DROPPING CONSTRAINTS :–
Syntax :–
| Alter table<tabname>
Drop constraint<name> |
- Alter table emp88 drop constraint ck_sal_emp88 -> Check constraint will be dropped.
- The primary key cannot be dropped,if it is referenced by any foreign key.
DELETE RULES :– “if specifies how child record is effected if the parent record is deleted”.
- on delete no action
- on delete cascade
- on delete set null
- on delete set default.
(i) on delete no action :-
- If foreign key is declared with out any delete rule then parent record cannot be deleted,If matching child records exist.
(ii) on delete cascade :-
- If foreign key is declared with on delete cascade, if we delete the parent record, The parent is deleted along with child records.
Create table dept33 (dno into primery key, Dnam varchar(20)).
Create table emp33 (eno int primery key, Ename varchar(20), Dno int references dept33(dno) On delete cascade)
(iii) on delete set null :-
- If foreign key is declared with or delete set null then if you delete the parent record, parent record is deleted with out deleting the child record but the child record foreign key values is set to null.
Example :-
create table dept33 (dno into primery key, Dname varchar (20)) (Parent)
Create table emp33 (eno int primery key, Ename varchar (20), Dno int references dept33 (dno) On delete set null) (Child)
(iv) on delete set default :-
- If foreign key is declared with on delete set default, if parent record is deleted then child record foreign key value set to default value.
Example :-
Create table dept33 (dno int primery key, Dname varchar(20))
Create table emp33 (eno int primery key, Ename varchar (20), Dno int references dept33(dno)