Constraints in oracle sql interview questions and answers

-

 Definition:

It is nothing but the conditions or the restrictions are assigned to each and every column of the database in order to maintain the data integrity. It is used to specify the rules for the data in a table. It is used to limit the type of the data that will go into the table. It ensures the accuracy and reliability of the data in the table. It can be applied at the column level or the table level. Column level constraints are applied at the column level. Table level constraints are applied at the whole table. It will not allow us to enter the information for the entire result.


How to create constraint in SQL?

It can be created when the table is created with the help of create table statement. It can also be created after the table is created with the help of alter statement.

Syntax:

Create table table_name(
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
...
...
...
column n datatype constraint);

Types of constraint:

1) NOT NULL: It ensures that the column cannot have the null value. By default, column can hold the null values. It enforces the column not to accept NULL as value. It enforces the field to contain some value. You cannot insert or update a record without inserting the value. Null is not equal to space or zero. Null means that no information or no value.

create table temp_persons(person_id int not null,first_name varchar2(15),last_name varchar2(15),age int);

Alter syntax using not null:







2) UNIQUE: It ensures all the values in the column are different. Both the unique and primary key constraints will provide assurance or guarantee for the uniqueness of the table's column or column sets. Multiple unique constraint is supported in the table. If there is any duplicate value in the particular column, it will not allow us to enter further information in the record.

Unique constraint on single table:

create table temp_persons(person_id int not null UNIQUE,first_name varchar2(15),last_name varchar2(15),age int);

Unique constraint is created on the column person_id of the temp_persons table.
desc temp_persons;


Multiple unique constraint:

create table temp_persons(person_id int not null,first_name varchar2(15),last_name varchar2(15) not null,age int,
constraint uc_persons UNIQUE (person_id,last_name));

Using Alter syntax, unique constraint is applied on the single column.

                              Using Alter syntax, unique constraint is applied on the multiple columns.


How to drop a unique constraint?

Alter table temp_persons DROP constraint UC_persons;





3) PRIMARY KEY: It is the combination of not null and unique values, and it uniquely identifies each row in the table. Primary key constraint automatically has unique constraint. Single primary key constraint is supported in the table. It can be applied on the single or multiple columns or fields. Columns which can be assigned by the primary key are called as the candidate key.

Alternate key=Candidate key-primary key

Primary key is applied on the single column person_id while creating the temp_persons table.

create table temp_persons(person_id int not null,first_name varchar2(15),last_name varchar2(15)not null,age int,Primary key(person_id));



create table temp_persons(person_id int not NULL PRIMARY KEY,
first_name varchar2(15),last_name varchar2(15) not null,age int);


Primary key is applied on the multiple columns person_id and last_name while creating the temp_persons table.

create table temp_persons(person_id int not null,first_name varchar2(15),last_name varchar2(15)not null,
age int,CONSTRAINT pk_persons Primary key(person_id,last_name));



Note: If you use ALTER TABLE to add primary key in the table, primary key column must be not null at the time of table creation.

How to drop primary key constraint?

ALTER TABLE temp_persons DROP CONSTRAINT pk_persons;





4) FOREIGN KEY: It prevent action that would destroy link from the table. It is the field or the collection of fields in one table which refers to the primary key in another table. The table with the foreign key is called as the child table and the table with the primary key is called as the parent table or referencing table. It prevents invalid data from being inserted into the foreign key column. It contains all the values of the column which in the parent table column.



INSERT ALL
INTO temp_persons(person_id ,first_name,last_name,age) VALUES (1,'Rahul','Kumar',36)
INTO temp_persons(person_id ,first_name,last_name,age) VALUES (2,'Rishav','Kumar',27)
INTO temp_persons(person_id ,first_name,last_name,age) VALUES (3,'Roshan','Kumar',25)
select * from dual;

We have inserted three records into the temp_persons.


select * from temp_persons;



Table temp_orders has been created with the foreign key as person_id.

create table temp_orders(order_id int not null primary key,order_number int not null,person_id int,FOREIGN KEY(person_id)
REFERENCES temp_persons(person_id));

Three records have been inserted into the temp_orders table.

INSERT ALL
INTO temp_orders(order_id ,order_number,person_id) VALUES (1,'1001',3)
INTO temp_orders(order_id ,order_number,person_id) VALUES (2,'1002',2)
INTO temp_orders(order_id ,order_number,person_id) VALUES (3,'1004',1)
select * from dual;




5) CHECK: It ensures that the values in the table would satisfy the specific condition.

6) DEFAULT: When no value is specified, it sets the default value for a column.

7) CREATE INDEX: It is used to create and retrieve the data from the database very quickly.










Comments

Post a Comment

Popular posts from this blog

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea,IBM and Mphasis etc" INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results.In other words,multiple select statements of similar column name in same order and having same data type to produce the unified result.In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. Column must be of the same datatype for these operations of one select statement with another select statements. Columns must be in the same order in this set operators in the multiple select statements. Same number of co...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more