Thursday, October 4, 2012

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  

No comments:

Popular Posts