Oracle Constraints Examples

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.