Foreign Key

What is a foreign key?

• A foreign key is a column (or combination of columns) whose values match the primary key of another table.
• It is the key used to link two tables together. It is sometimes called a referencing key.
• The table holding the foreign key is the dependent (child) table; the table holding the referenced primary key is the parent table.
• Example: in an ORDERS table, CUSTOMER_ID is a foreign key that matches ID, the primary key of the CUSTOMERS table.

Why use foreign keys?

• They stop orphan rows - you cannot insert an order for a customer that does not exist.
• They document the relationship between tables right in the database definition.
• Combined with delete rules (CASCADE, RESTRICT, SET NULL), they control what happens to child rows when a parent row is deleted.
• DB2 enforces the relationship automatically; application code does not have to check it.

Example

• Parent table:-
  
  CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), SALARY DECIMAL(18,2), PRIMARY KEY (ID));
• Dependent table with foreign key:-
  
  CREATE TABLE ORDERS (ID INT NOT NULL, ORDER_DATE DATE NOT NULL, CUSTOMER_ID INT NOT NULL REFERENCES CUSTOMERS(ID), AMOUNT DECIMAL(10,2), PRIMARY KEY (ID));
• Add a foreign key to an existing table:-
  
  ALTER TABLE ORDERS ADD FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (ID);