FOREIGN KEY Constraint

Ø  It designates a column as foreign key and establishes a relation between the foreign key and a specified primary key or UNIQUE key.
Ø  A composite foreign key designates a combination of columns as the foreign key.
Ø  The TABLE or VIEW containing the foreign key is called the child object.
Ø  The TABLE or VIEW containing the referenced key is called the parent object.
Ø  The corresponding column or columns of the foreign key and the referenced key must match in order and data type.
Ø  A foreign key constraint can be defined on a single key column either inline or out of line.
Ø  A composite foreign key on attributes should be declared at table level or out of line style.
Ø  We can designate the same column or combination of columns as both a foreign key and a primary or unique key.
Ø  A composite foreign key constraint must refer to a composite unique key or composite primary key in the parent table or view.
Restrictions:
Ø  The foreign key columns cannot be applied on….
Ø  LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, BFILE, REF, TIMESTAMP WITH TIME ZONE.
Ø  A composite foreign key cannot have more than 32 columns.
Ø  The child and parent tables must be on the same database.
References clause:
Ø  The references clause should be used when the foreign key constraint is INLINE.
Ø  When the constraint is out of line, we must specify the foreign key word.
ON DELETE Clause:
Ø  The ON DELETE Clause determines how oracle automatically maintains referential integrity if the referenced primary or unique key value is removed from master table.
Ø  CASCADE Option can be specified if we want oracle to remove dependent foreign key values.
Ø  Specify SET NULL if we want Oracle to convert dependent foreign key values to null.
Ø  On DELETE Clause cannot be specified for a view constraint.
Ø  Declared on foreign key column only.
Steps Followed for Creating Foreign key Constraint are as Follows:
Step 1: Create Primary Master’s / Pure Master’s
·         Create Table Dept
                        (
                                    Deptno Number(2)
                                    Constraint deptno_PK primary key,
                                    Dname varchar2(20)
                                    Constraint Dname_NN NOT NULL,
                                    Location varchar2(20)
                                    Constraint Loc_NN NOT NULL
                        );
Step 2: Create Detailed / Child Table
Ø  These are tables which can contain primary key or their own as well as foreign key’s referring to other primary master’s or to themselves.
Ø  These Tables are also called as dependent tables or referential tables.
·         Create Table Employee
                        (
                                    EmployeeID Number(6)
                                    Constraint Emp_ID_PK primary key,
                                    Ename varchar2(30)
                                    Constraint Ename_NN NOT NULL,
                                    Designation varchar2(30)
                                    Constraint Desig_NN NOT NULL,
                                    ManagerID Number(6)
                                    Constraint Mgr_ID_FK_SELF Refrences
                                    Employee(EmployeeID) ON DELETE SET NULL,
                                    Hiredate Date
                                    Constraint hiredate_NN NOT NULL,
                                    Commission Number (7, 2),
                                    DeptID Number(2)
                                    Constraint DeptID_FK
                                    References Dept(Deptno) ON DELETE CASCADE
                        );
Working With Composite Keys:
Step 1: Create Pure Masters
·         Create Table SampleMaster1
                        (
                                    SampleID1 Number(4)
                                    Constraint SampID1PK primary key,
                                    SampName1 varchar2(20)
                                    Constraint SampName1NN NOT NULL,
                                    SampleDate1 Date
                                    Constraint SampleDate1NN NOT NULL
                        );

·         Create Table SampleMaster2
                        (
                                    SampleID2 Number(4)
                                    Constraint SampleID2PK primary key,
                                    SampleName2 varchar2(20)
                                    Constraint SampName2NN NOT NULL,
                                    SampleDate2 Date
                                    Constraint SampleDate2NN NOT NULL
                        );
Step 2: Create The Pure Details
·         Create Table SampRef
                        (
                                    SampleIDRef1 Number(4)
                                    Constraint SampleIDRef1_FK
                                    References SampMaster1(SampID1),
                                    SampIDRef2 Number(4)
                                    Constraint SampIDRef2_FK
                                    References SampMaster2(SampID2),
                                    SampNameRef Varchar2(20),
                                    SampDateRef Date,
                                    Constraint SampRef_Comp_PK
                                    Primary key (SampIDRef1,SampIDRef2)

                        );