SIGN UP MEMBER LOGIN:
   
ARTICLE

Foreign Key Constraint in DB2

Posted by Deepak Kumar Articles | DB2 May 06, 2011
A Foreign Key is a referential constraint which is specified using CREATE or ALTER Statement. A Foreign Key (in one table) refers to a Primary or Unique Key (in another table) which is also known as Parent Key for Foreign Key.
Reader Level:

A Foreign Key is a referential constraint which is specified using CREATE or ALTER Statement. A Foreign Key (in one table) refers to a Primary or Unique Key (in another table) which is also known as Parent Key for Foreign Key. Foreign Key is optional and a table can have more than one Foreign Key.

A column, on which a Foreign Key is specified, can have only values or records which are already exist in it's parent column.

SYNTAX -

>> To specify a Foreign Key

CREATE TABLE table_name(  Column Defination,
CONSTRAINT constraint_name
FOREIGN KEY (column_name)
REFERENCES another_table_name(column_name)
ON DELETE CASCADE / RESTRICT / SET NULL / NO ACTION

OR

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name)
REFERENCES other_table_name(column_name)
ON DELETE CASCADE / RESTRICT / SET NULL / NO ACTION

* In our queries, CASCADE or RESTRICT or SET NULL or NO ACTION can be specified in "ON DELETE" Phrase as per our requirement. Behavior of each is as follows :

CASCADE : Whenever rows in the master (referenced) table are deleted, the respective rows of the child (referencing) table with a matching foreign key column will get deleted as well. This is called a cascade delete.

RESTRICT : A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table. Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

NO ACTION : NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

SET NULL : The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.

>> To drop a Foreign Key

ALTER TABLE table_name DROP CONSTRAINT constraint_name

EXAMPLE

We will discuss the use and behavior of Foreign Key step by step with the help of following example :
Suppose, A sports company wants to store it's customers information and orders raised by each customer as well as. So we need two tables 1. Customers, 2. Orders.
Creating Table Customers :

 FOREIGNKEY1.gif

Creating Table Orders :

 FOREIGNKEY2.gif

The CUS_ID Column is common in both tables and also makes a relationship between these tables. In Table Customers CUS_ID is defined as Primary Key, and defined as Foreign Key in Table Orders with reference of Column CUS_ID in Table Customers. It means CUS_ID in table Orders can contain those values which are already exist in the CUS_ID in table Customers.

Inserting rows into table Customers :

 FOREIGNKEY5.gif

Selecting all rows from Customers :

FOREIGNKEY6.gif

Inserting rows into table Orders :

 FOREIGNKEY7.gif

Selecting all rows from Orders :

FOREIGNKEY8.gif

Inserting a row into table Orders with a CUS_ID that is not available in Primary Key Table :

 FOREIGNKEY9.gif

Error encountered : SQL0530N The insert or update value of the FOREIGN KEY "DKVERMA.ORDERS.FK_CUS_ID" is not equal to any value of the parent key of the parent table. SQLSTATE=23503

Drop a Foreign Key Constraint :

 FOREIGNKEY3.gif

Specify a Foreign Key Constraint using ALTER TABLE Statement :

FOREIGNKEY4.gif
 

Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING
TRENDING UP