SQL-CONSTRAINTS(COMPOSITE KEYS,DEFERRABLE)

USING ON DELTE CASCADE

By using this clause you can remove the parent record even it childs exists.

Because when ever you remove parent record oracle automatically removes all its dependent records from child table, if this clause is present while creating foreign key constraint.

Ex:

      TABLE LEVEL

     SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             primary key(empno), foreign key(deptno) references dept(deptno) on delete

             cascade);

      SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             constraint pk primary key(empno), constraint fk foreign key(deptno) references

             dept(deptno) on delete cascade);

     

      ALTER LEVEL

      SQL> alter table emp add foreign key(deptno) references dept(deptno) on delete

             cascade;

                SQL> alter table emp add constraint fk foreign key(deptno) references dept(deptno) on

                     delete cascade;

COMPOSITE KEYS

A composite key can be defined on a combination of columns.

We can define composite keys on entity integrity and referential integrity constraints.

Composite key can be defined in table and alter levels only.

Ex:

      UNIQUE (TABLE LEVEL)

     

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             unique(no,name));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint un unique(no,name));

           

      UNIQUE (ALTER LEVEL)

      SQL> alter table student add unique(no,name);

      SQL> alter table student add constraint un unique(no,name);

     PRIMARY KEY (TABLE LEVEL)

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             primary key(no,name));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint pk primary key(no,name));

      PRIMARY KEY (ALTER LEVEL)

      SQL> alter table student add primary key(no,anme);

      SQL> alter table student add constraint pk primary key(no,name);

      FOREIGN KEY (TABLE LEVEL)

     SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             dname varchar(10), primary key(empno), foreign key(deptno,dname) references

             dept(deptno,dname));

      SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

             dname varchar(10), constraint pk primary key(empno), constraint fk foreign

             key(deptno,dname) references dept(deptno,dname));

      FOREIGN KEY (ALTER LEVEL)

    

      SQL> alter table emp add foreign key(deptno,dname) references dept(deptno,dname);

                SQL> alter table emp add constraint fk foreign key(deptno,dname) references

                     dept(deptno,dname);

DEFERRABLE CONSTRAINTS

Each constraint has two additional attributes to support deferred checking of constraints.

Ø  Deferred initially immediate

Ø  Deferred initially deferred

Deferred initially immediate checks for constraint violation at the time of insert.

Deferred initially deferred checks for constraint violation at the time of commit.

Ex:

     SQL> create table student(no number(2), name varchar(10), marks number(3),

             constraint un unique(no) deferred initially immediate);

     SQL> create table student(no number(2), name varchar(10), marks number(3),

             constraint un unique(no) deferred initially deferred);

     SQL> alter table student add constraint un unique(no) deferrable initially deferred;

   

      SQL> set constraints all immediate;

     This will enable all the constraints violations at the time of inserting.

      SQL> set constraints all deferred;

     This will enable all the constraints violations at the time of commit.

OPERATIONS WITH CONSTRAINTS

Possible operations with constraints as follows.

Ø  Enable

Ø  Disable

Ø  Enforce

Ø  Drop

ENABLE

This will enable the constraint. Before enable, the constraint will check the existing data.

Ex:

     SQL> alter table student enable constraint un;

DISABLE

This will disable the constraint.

Ex:

     SQL> alter table student enable constraint un;

ENFORCE

This will enforce the constraint rather than enable for future inserts or updates.

This will not check for existing data while enforcing data.

Ex:

     SQL> alter table student enforce constraint un;

DROP

This will remove the constraint.

Ex:

     SQL> alter table student drop constraint un;

     Once the table is dropped, constraints automatically will drop.