Ø 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)
);