FOREIGN KEY

            Using Primary/Unique key and foreign key we can create parent and child relationship between two table. Primary/Unique key table is parent and Foreign key table is child table.

Note:

• First parent table should create then only we can create child table.
• First parent table should populate then only we can populate child table.
• If we need to drop parent constraint, first we should drop child constraint then only we can drop parent constraint.
• If we need to delete data from parent table, first we need to delete related data from child table.
• If we need to drop parent table first we should drop child table or child table foreign key constraint.

 

Applying Foreign Key constraint on a new table:

Syntax:-

CREATE TABLE <Child Table name>

(<Column 1> <Data type>,

<Column 2> <Data type>,

.

.

.

<Column N> <Data type>,

CONSTRAINT <Constraint name> <Constraint type> (Child table column name) REFERENCES <Parent table name> (parent primary/Unique key Column name)

)

Example:

Parent table:

CREATE TABLE DEPT_PK

(

DID INT

,Name VARCHAR(20)

,Dept INT

,CONSTRAINT DEPT_PK1 PRIMARY KEY (Dept)

)

Child table:

CREATE TABLE EMP_FK

(

ID INT

,FirstName varchar (30)

,LastName Varchar(30)

,Gender Char(1)

,Dept INT

,Sal DECIMAL(18,4)

,CONSTRAINT DEPT_FK1 FOREIGN KEY (Dept) REFERENCES DEPT_PK(Dept)

)

  

Result: SP_HELP EMP_FK 

 

Applying Foreign key constraint on existing table:

Note: If we want to create foreign key constraint on a column, that column related data should be present in parent table or child table should be empty, other wise we cannot apply foreign key constraint.

Syntax:

ALTER TABLE <Table name> ADD CONSTRAINT <Constraint name> FOREIGN KEY (<Child table Column name>) REFERENCES <Parent Table name> (<Parent table column name>)

Example:

Child table creation:

CREATE TABLE EMP_FK2

(

ID INT

,FirstName varchar (30)

,LastName Varchar(30)

,Gender Char(1)

,Dept INT

,Sal DECIMAL(18,4)

)

Adding Foreign key constraint:

ALTER TABLE EMP_FK2 ADD CONSTRAINT DEPT_FK2 FOREIGN KEY (Dept) REFERENCES DEPT_PK(Dept)

Result:

Drop constraint:

Syntax:

ALTER TABLE <Table name> DROP CONSTRAINT <Constraint name>

Example:

ALTER TABLE EMP_FK2 DROP CONSTRAINT DEPT_FK2