How to Add and Drop Foreign Key and other Constraint in Oracle 10g XE

Foreign key is a non-key attribute which depends on a Primary key column of another table.

Here in below given example in table EMPL, DEPTID is foreign key column and there is another table named DEPTL which is Primary key table and DEPTID

is Primary Key Column.

If Foreign Key is present in same Primary Key table then it called Self Referential Integrity.

1: Adding Constraint After table created

ON DELETE SET NULL:sets child value null
ON DELETE CASCADE: Child row will delete

eg.

 ALTER TABLE EMPL ADD CONSTRAINT FOREIGN KEY EMPL_DEPTID_FK FOREIGN KEY(DEPTID) REFRENCES DEPTL(DEPTID) ON DELETE SET NULL 

 ALTER TABLE EMPL ADD CONSTRAINT FOREIGN KEY EMPL_DEPTID_FK FOREIGN KEY(DEPTID) REFRENCES DEPTL(DEPTID) ON DELETE CASCADE  

2: Dropping Constraint

ALTER TABLE TABLENAME CONSTRAINT CONSTRAINT-NAME

eg.

 ALTER TABLE EMPL DROP CONSTRAINT EMPL_DEPTID_FK