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

advertisements

_____________________________________________________________________________________________________________________

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.

2. 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.

3. 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.

4. 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.

5. 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;

Table altered.

Datapump Tuning Features

Datapump vs EXP/IMP

Datapump Advantages

Datapump Exclude Table Partition

Datapump Exclude and Include Parameter

Datapump NetworkLink Parameter

Datapump SQLFILE Parameter

Datapump Reuse_dumpfiles Parameter

Datapump Compression Parameter

Datapump Jobname Parameter

Datapump Remapdata Parameter

Datapump Sample Parameter

Datapump Query Parameter

Datapump Transportable Tablespace Option

_____________________________________________________________________________________________________________________

Website Stats