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.
Leave a Reply to sqlandplsql Cancel reply