FOREIGN KEY Constraint

• A FOREIGN KEY is a key used to establishes link between two tables (as parent child relationship).The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
• A FOREIGN KEY in one table that refers to the PRIMARY KEY in another table.
• Composite FOREIGN KEY should be declare in Table level.
• The FOREIGN KEY and the REFERENCED KEY can be same  table or view.

Restrictions :

• The FOREIGN KEY columns cannot be applied on LOB,LONG,VARRAY,NASTED TABLE,OBJECT AND TIMESTAMP WITH TIME ZONE.
• Composite FOREIGN KEY  cannot contain more than 32 columns.
• Child and parent tables must be in same data base.
• The referenced key(unique or primary) constraint on the parent table or view must already be defined. 
• REFERENCES Clause should be used when the FOREIGN KEY constraint is INLINE.

ON DELETE Clause :

If you want to delete the record from the parent and that parent having child then oracle will not allow you to delete the parent record. To delete the parent record we need to yes ON DELETE clause , by using ON DELETE clause oracle manage the referential integrity if you remove a referenced key .

• CASCADE:  If you want to delete dependent parent with child then use CASCADE
• SET NULL:    If you want to delete the only parent and child table dependent  FOREIGN KEY values set as NULL .

Syntax :

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH) CONSTRAINT Constraint_Name REFERENCES <reference_table_name>[reference_table PK column_name],
 COLUMN_NAMEn <DATA_TYPE>(WIDTH));

See The Example:

FOREIGN KEY CONSTRAINT EXAMPLE