Oracle Constraints Types

---

Constraints :

             Constraint is a mechanism which is used to restrict the invalid data which is entered by the end user by implementing business rules.
We can apply the constraints(User defined) in two situations.

• During the creation of the table
• After the creation of the table

NOTE : System defined constraint names are always in the format of SYS_CN. Here N represents any number.

• We can apply the constraints in two levels.

1)Column Level : Applying constraints after defining the column immediately then those constraints can be called as column level constraints.
2)Table Level : Applying constraints after defining all the columns then those constraints can be called as Table level constraints.

1)UNIQUE :
=========
                This constraint doesn’t allows us to enter duplicate values in a particular column in a table. We can apply the unique constraint on more than one column in the table.The main disadvantage of unique constraint is it allows NULL values.

Syntax: create table <table_name>(col1 datatype(size) constraint type,Col2 datatype(size),….,Coln datatype(size));
Example: create table javanib(eid number(5) unique,ename varchar2(20));

NOTE : Once we are violating the unique constraint properties then it automatically displays the following error message.

            Ora_0001 : unique constraint (JAVANIB.sys_c5001) violated. 

2)NOT NULL :
===========
                 This constraint doesn’t allows us to enter null values in a particular column in a table. We can apply the unique constraint on more than one column in the table.The main disadvantage of not null constraint is it allows duplicate values.

Syntax: create table <table_name>(col1 datatype(size) constraint type,Col2 datatype(size)……. Coln datatype(size));
Example: create table Employee(eid number(5),ename varchar2(20) not null);

NOTE : Once we are violating the not null constraint properties then it automatically displays the following error message.

          Ora_01400 : can not insert null into("JAVANIB"."EMPLOYEE"."ENAME")

3)CHECK :
=========
This constraint allows us to enter the values which are satisfying the given condition. We can apply on more than one column.Check constraint allows us to enter null values.

Syntax: create table <table_name>(col1 datatype(size) constraint type Col2 datatype(size)……. Coln datatype(size));
Example: create table Employee(eid number(5),ename varchar2(20),sal number(5) check(sal>5000));

NOTE : Once we are violating the check constraint properties then it automatically displays the following error message.

      Ora_02290 : check constraint(JAVANIB.SYS_C005419) violated.

• Applying check constraint on character column.

Example: create table Employee(eid number(5) unique,
                                                ename varchar2(20) not null,
                                                sal number(5) check(sal>5000),
              dname varchar2(15) check(dname in (‘sales’,’marketing’,’HR’)));

4)PRIMARY KEY :
===============

• It is a combination of unique and not null constraints. So, it doesn’t allows us to enter both null values and duplicate values.
• We can apply the primary key constraint on key attribute column in the table.
• A table can have only one primary key constraint it means that the keyword primary key should not repeat more than one time in a single table.

Example: create table Employee(eid number(5) primary key,ename varchar2(20));

Note: If we try to provide more than one primary key then we get 

           Ora_02260 : table can not have more than one primary key

DEFAULT :
=========
                It gives default value to perticular column in the table.
Example: create table Employee(eid number(5) default 007,ename varchar2(20) ); 

 FOREIGN KEY :
=============
               Using this constraint, we can maintain the relationship between the tables with in the database.A foreign key constraint requires values in one table to match values in another table.This constraint can also be called as referential integrity. Using this constraint we can stop the deletion operation if there are any dependencies on the parent values.

Example: create table child(eid number(5) primary key,
                                        ename varchar2(20) not null,
                                        sal number(5) check(sal>5000),
                                        deptno number(5), 
        constraint balu foreign key (deptno) references parent(deptno));

* On delete cascade:
   ==============
          Using this command we can delete the parent value forcibly even though there are some dependencies.

Example: create table child(eid number(5) primary key,
                                           ename varchar2(20) not null,
                                           sal number(5) check(sal>5000),
                                           deptno number(5), 
                                       constraint balu foreign key (deptno) references parent(deptno) on delete cascade);

Syntax to apply Constraint after creation of table :
--------------------------------------------------------
Syntax: alter table <table_name> modify/add (col1 datatype(size) Constraint_Name,…,coln datatype(size) Constraint_Name);

List Of Constraints :
===============
Syntax: select * from user_constraints;
-->for particular table 
      Select constraint_name, constraint_type, status from user_constraints where table_name=’CHILD’;

• To create user defined constraint 

Example: create table child(eid number(5) constraint raja primary key,
                                           ename varchar2(20) constraint ravi not null,
                                           sal number(5) constraint ram check(sal>5000),
                                           deptno number(5) constraint Rahim not null);

• Syntax to enable/disable the constraint 

Example: alter table <table_name> enable/disable constraint <constraint_name>;

COMPOSITE Primary Key :
======================
                      The primary key constraint properties are shared by more than one column with in the table at the same time.
Example: create table Product(pid number(5) 
                                                Pname varchar2(20),
                                                Cost number(6,2), 
                                                Size number(5), 
                                                Constraint ram primary key(pid,pname,cost));

---