Previous post we have explained Oracle Constraints and now we will explain each of them with suitable examples.
Constraint can be defined at individual column and it is called inline specification and can be defined at table level and called Out of line specification. NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.
1. NOT Null Constraint
NOT NULL column must have a value.
Example :-
Create table person
(
pno integer,
pname varchar2(20) not null
);
pname column has a not null constraint.
SQL> desc person;
Name Null? Type
—————————————– ——– ————
PNO NUMBER(38)
PNAME NOT NULL VARCHAR2(20)
Below insert statement will validate the above not null constraint
SQL> insert into person values(1,null);
insert into person values(1,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“PERSON”.”PNAME”)
2. UNIQUE Constraint
UNIQUE Constraint column should have either unique values or null values across the table.
Examples :-
Method 1 (In line specification)
Create table person
(
pno integer,
pname varchar2(20) constraint uq_pname unique
);
Method 2 (Out of line Specification)
Create table person
(
pno integer,
pname varchar2(20),
constraint uq_pname unique (pname)
);
SQL> insert into person values(1,’Bill’);
1 row created.
SQL> insert into person values(2,’Bill’);
insert into person values(2,’Bill’)
*
ERROR at line 1:
ORA-00001: unique constraint (UQ_PNAME) violated
Next, UNIQUE constraint column can accept null values.
SQL> insert into person values(2,null);
1 row created.
SQL> insert into person values(3,null);
1 row created.
3. Primary Key Constraint
A primary key constraint combines a NOT NULL constraint and a UNIQUE constraint. A table can have only one PRIMARY KEY and it can be created for composite(Two or more columns) keys also.
Examples :-
Method 1 – In-line and without name
Create table person
(
pno integer primary key,
pname varchar2(20)
);
Method 2 – In-line with name
Create table person
(
pno integer constraint pk_pno PRIMARY KEY,
pname varchar2(20)
);
Method 3 – Out-line with name
Create table person
(
pno integer,
pname varchar2(20),
constraint pk_pno PRIMARY KEY (pno)
);
Method 4 – Composite, out-line with name
Create table person
(
pno integer,
cno integer,
pname varchar2(20),
constraint pk_pno PRIMARY KEY (pno,cno)
);
Method 5 – Adding a primary key – ALTER CONSTRAINT
Create table person
(
pno integer,
pname varchar2(20)
);
Alter table person add constraint pk_pno PRIMARY KEY (pno);
Method 5 – Dropping a primary key – ALTER CONSTRAINT
Alter table person drop constraint pk_pno;
4. Foreign Key Constraint
Foreign Key Constraint used to relate two or more table and values in one table to match values in another table.
Examples :-
create table dept
(
deptno integer primary key,
deptname varchar2(20) not null,
location varchar2(20)
);
Method 1 – In line and without name
create table emp
(
empno integer primary key,
empname varchar2(20) not null,
salary number,
deptno integer references dept(deptno)
);
Method 2 – In line with name
create table emp
(
empno integer primary key,
empname varchar2(20) not null,
salary number,
deptno integer constraint fk_deptno references dept(deptno)
);
Method 3 – Out line with name
create table emp
(
empno integer primary key,
empname varchar2(20) not null,
salary number,
deptno integer,
constraint fk_deptno foreign key(deptno) references dept(deptno)
);
Method 5 – Adding a foreign key – ALTER CONSTRAINT
Alter table emp add constraint fk_deptno foreign key(deptno) references dept(deptno);
Method 5 – Dropping a foreign key – ALTER CONSTRAINT
Alter table emp drop constraint fk_deptno;
5. CHECK Constraint
CHECK Constraints are enforcing certain types of values in a column.
Method 1 – In line and with name
Create table person
(
pno integer,
pname varchar2(20),
status varchar2(20) constraint ch_status check( status in (‘ACTIVE’,’INACTIVE’))
);
Method 2 – Out line with name
Create table person
(
pno integer,
pname varchar2(20),
status varchar2(20),
constraint ch_status check( status in (‘ACTIVE’,’INACTIVE’))
);
Method 3 – Adding a check constraint – ALTER CONSTRAINT
Alter table person add constraint ch_status check( status in (‘ACTIVE’,’INACTIVE’));
Method 4 – Dropping a check constraint – ALTER CONSTRAINT
Alter table person drop constraint ch_status;
Few facts about Constraints
1. It is nice to have a primary key constraint for all tables.
2. It is better to create unique index rather than creating UNIQUE constraint
3. Too many constraints also difficult to maintain in future.
4. Do not create too many CHECK constraints
Did you face any issues with constraints ? Share your comments.
Oracle PL/SQL Technology blog 
what is difference between constraint and trigger?
Can you please explain DEFERRABLE and NOT DEFERRABLE parameters of constraints.
=>what we can achieve using constraint,we can achieve using triggers.
=>but it is advisable to use constraints, for performance.
=>for business logics we write triggers,for general data constraints we create constraints.
=>when we have data in table (suppose duplicate also)and we want to achieve uniqueness
now-ownwards them we must use triggers ,that will check only upcoming data.
=>two types of parameters we have while creating a constraints .
1>deferrable initially immediate =>checks immediate as inserted/updated
2>deferrable initially deferred. =>checks while committing only,rollbacks if fails
You have mentioned in few facts about constrain that “It is better to create unique index rather than creating UNIQUE constraint” than what is reason behind it ? Will you please specify it ?
how to know constraint is availabel in table or column
All constaint details are stored in data dictionary table. Try user_constraints
hello sir this session is good but how can i check constraints on particular table like emp table .
select * from user_constraints where table_name=’EMP’; but this command is very big output so how can i check only column constraints on EMP table.