Constraints

Constraints : Constraints are the rules to maintain consistent data in database.

Types of Constraints :

• NOT NULL
• UNIQUE
• PRIMARY KEY
• COMPOSITE KEY
• CHECK
• FOREIGN KEY
• CANDIDATE KEY
• SUPER KEY

Not Null : It does not allows null values to not null defined columns.

SQL> create table emp(id number not null,name varchar2(20),salary number);

Table created.

SQL> describe emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                                     VARCHAR2(20)
 SALARY                                                  NUMBER

SQL> insert into emp values(1,'kamlesh',1200);

1 row created.

Note : If we try to insert null values to a column then we will have following error

SQL> insert into emp values(null,'rakesh',1200);
insert into emp values(null,'rakesh',1200)
                       *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("KKM"."EMP"."ID")

Unique : It does not allows duplicate values


SQL> create table emp(id number unique,name varchar2(20),salary number);

Table created.

SQL> insert into emp values(1,'kamlesh',1200);

1 row created.

SQL> insert into emp values(2,'rakesh',1200);

1 row created.

Note : If we try to insert duplicate values to unique key defined column then we will have following error

SQL> insert into emp values(1,'mohan',1200);
insert into emp values(1,'mohan',1200)
*
ERROR at line 1:
ORA-00001: unique constraint (KKM.SYS_C007115) violated

Primary key : It is the combination of not null and unique key.

SQL> create table emp(id number primary key,name varchar2(20),salary number);

Table created.

SQL> insert into emp values(1,'mohan',1200);

1 row created.

SQL> insert into emp values(2,'kamlesh',1200);

1 row created.

Note : If we try to insert null value or duplicate values we will have following error

SQL> insert into emp values(1,'sohan',1200);
insert into emp values(1,'sohan',1200)
*
ERROR at line 1:
ORA-00001: unique constraint (KKM.SYS_C007116) violated

Composite primary key : When two or more columns have properties of primary key then composite primary key is used . There is no special keyword for composite primary key.

SQL> create table emp(id number,name varchar2(20),salary number,primary key(id,name));

Table created.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                NOT NULL VARCHAR2(20)
 SALARY                                                 NUMBER

SQL> insert into emp values(1,'mohan',1200);

1 row created.

SQL> insert into emp values(2,'rakesh',1200);

1 row created.

SQL> insert into emp values(1,'rakesh',1200);

1 row created.

SQL> insert into emp values(3,'rakesh',1200);

1 row created.

Note : If we try to insert null value or duplicate values we will have following error

SQL> insert into emp values(3,'rakesh',1200);
insert into emp values(3,'rakesh',1200)
*
ERROR at line 1:
ORA-00001: unique constraint (KKM.SYS_C007117) violated

SQL> create table emp(id number,name varchar2(20),salary number check(salary>500),primary key(id,name));

Table created.

SQL> insert into emp values(1,'rakesh',1200);

1 row created.

SQL> insert into emp values(2,'mohan',1300);

1 row created.

Note : If we try to insert null value or duplicate values we will have following error

SQL> insert into emp values(3,'mohan',200);
insert into emp values(3,'mohan',200)
*
ERROR at line 1:
ORA-02290: check constraint (KKM.SYS_C007118) violated


Foreign key : Parent table containg primary key whose value is checked during insert on child class column

SQL> create table department(id number primary key,name varchar2(20));

Table created.

SQL> desc department;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                                     VARCHAR2(20)


SQL> create table emp(id number,name varchar2(20),salary number,dno number, foreign key(dno) references department(id));

Table created.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                      NUMBER
 NAME                                               VARCHAR2(20)
 SALARY                                            NUMBER
 DNO                                                  NUMBER


SQL> insert into department values(10,'hr');

1 row created.

SQL> insert into department values(20,'bpo');

1 row created.

SQL> insert into department values(30,'sales');

1 row created.

SQL> select * from department;

        ID NAME                                                                
---------- --------------------                                                
        10 hr                                                                  
        20 bpo                                                                 
        30 sales                                                               

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                      NUMBER
 NAME                                               VARCHAR2(20)
 SALARY                                            NUMBER
 DNO                                                  NUMBER

SQL> insert into emp values(1,'kamlesh',1200,10);

1 row created.

SQL> insert into emp values(2,'ramesh',1200,20);

1 row created.

SQL> insert into emp values(3,'rajesh',1100,10);

1 row created.

Note : If we try to insert values which is not present in parent then we will have following error

SQL> insert into emp values(4,'rajesh',1100,40);
insert into emp values(4,'rajesh',1100,40)
*
ERROR at line 1:
ORA-02291: integrity constraint (KKM.SYS_C007121) violated - parent key not
found

IN CASE TABLE IS ALREADY CREATED WE USE ALTER COMMAND TO MAKE CHANGES.




HOW TO ADD NOT NULL , UNIQUE KEY, PRIMARY KEY, COMPOSITE KEY, FOREIGN KEY USING ALTER

• SQL> ALTER TABLE EMP ADD CONSTRAINT [CONSTRAINT NAME] NOT NULL(COLUMN NAME);
• SQL> ALTER TABLE EMP ADD CONSTRAINT [CONSTRAINT NAME] UNIQUE(COLUMN NAME);
• SQL> ALTER TABLE EMP ADD CONSTRAINT [CONSTRAINT NAME] PRIMARY KEY(COLUMN NAME);
• SQL>ALTER TABLE EMP ADD CONSTRAINT [CONSTRAINT NAME] PRIMARY KEY(COL 1, COL2);
• SQL>ALTER TABLE EMP ADD CONSTRAINT [CONSTRAINT NAME] FOREIGN KEY REFERENCES [TABLE NAME(COLUMN NAME)];

 HOW TO DELETE A CONSTRAINT FROM A TABLE USING DROP

•  SQL> DROP CONSTRAINT [CONSTRAINT NAME];
• IN CASE OF FOREIGN KEY  , DROP FIRST PARENT TABLE THEN YOU CAN DROP CHILD TABLE CONSTRAINS. 

SUPER KEY

• A Super key is a set of one or more attributes that allows us to to identify uniquely an entity in the entity set. 
• For example, the Roll_no attribute of the entity set 'Student' distinguishes one student from another.

CANDIDATE KEY

• A Super key may contain extraneous attributes and we are often interested in the smallest super key . A Super key for which no subset is a super key is called a candidate key.
• For example, Student_name and Student_street are sufficient to uniquely identify one particular student. Hence , [Roll_no,Student_street] are candidate key.