Add Oracle Constraints – Primary, Foreign, Unique, Check, Null to Existing Table

Most of the time the syntax for the constraint addition is confusing one unless you are frequent to the table creation. Here I am giving some examples and syntaxes for the Oracle constraint creations.
  1. Primary Key
a.   With Create Table Command
SQL> create table dept
(
deptno number(2),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
);
Table created.

b.   Constraint addition Using Alter Table

SQL> alter table dept add constraint pk_dept primary key (deptno);

Table altered.

  1. Foreign Key: Foreign key must be referencing to a primary key of another table.
a.   With Create Table Command
SQL> create table emp
(
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7, 2),
comm number(7, 2),
deptno number(2),
constraint FK_EMP foreign key (deptno) references  dept (deptno)
); 

Table created.
b.   Constraint addition Using Alter Table

SQL> alter table emp add constraint FK_EMP foreign key (deptno) references  dept (deptno) ;

Table altered.

  1. Check Key

a.   With Create Table Command

SQL> create table emp
(
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7, 2),
comm number(7, 2),
deptno number(2),
constraint CHK_emp_SAL check (sal>1000)
);

Table created.
b.   Constraint addition Using Alter Table
SQL> alter table emp add constraint CHK_emp_SAL check (sal>1000);

Table altered.
  1. Unique Key

a.   With Create Table Command
SQL> create table emp
(
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7, 2),
comm number(7, 2),
deptno number(2),
constraint UK_EMP unique (empno)
);

Table created.

b.   Constraint addition Using Alter Table
SQL> alter table emp add constraint UK_EMP unique (empno);

Table altered.
  1. NOT NULL

a.   With Create Table Command
SQL> create table emp
(
empno number(4) not null,
ename varchar2(10) constraint NN_EMP_ENAME NOT NULL,
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7, 2),
comm number(7, 2),
deptno number(2)
); 

Table created.

b.   Using alter table command
SQL> alter table emp modify ename varchar2(10) constraint NN_EMP_ENAME NOT NULL;

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google