Db2 Foreign Key

Summary: in this tutorial, you will learn how to use the Db2 foreign key constraint to enforce the referential integrity between the data across tables.

Introduction to the Db2 foreign key

Let’s take a look at the contacts and phones tables:

CREATE TABLE contacts(
	contact_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
	first_name VARCHAR(100) NOT NULL,
	last_name VARCHAR(100) NOT NULL,
	PRIMARY KEY(contact_id)
);

CREATE TABLE phones(
	phone_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
	phone_no VARCHAR(20) NOT NULL,
	phone_type VARCHAR(10) NOT NULL,
	contact_id INT NOT NULL,
	PRIMARY KEY(phone_id)
);
Code language: SQL (Structured Query Language) (sql)

In this diagram, each contact may have zero or many phones such as home phone, work phone, and emergency phone. However, each phone must belong to one and only one contact. The relationship between the contacts and phones is one-to-many.

For each row in the phones table, you can always find a corresponding row in the contacts table. But the current setup does not ensure this relationship. It means you can insert a new row into the phones table with the contact identification (contact_id) that does not exist in the contacts table.

Furthermore, if you delete a contact, all the phones of the deleted contact will remain in the phones table. The rows in the phones table that does not have corresponding rows in the contacts table are called orphaned rows.

In order to enforce the relationship between contacts and phones tables, you need to use a foreign key constraint.

What is a foreign key?

A foreign key is a column or group of columns in a table that uniquely identifies a row in another table. The foreign key constraints define foreign keys.

Back to our example, the contact_id in the phones table should be the foreign key of the phones table. Because for each phone in the phones table, you can find a corresponding contact in the contacts table.

To add a foreign key constraint to the phones table, you use the following ALTER TABLE statement:

ALTER TABLE phones
FOREIGN KEY (contact_id)
	REFERENCES contacts(contact_id)
		ON UPDATE NO ACTION
		ON DELETE CASCADE;
Code language: SQL (Structured Query Language) (sql)

The contacts table is called the parent table to which the foreign key references. The phones table is called the child table (or dependent table) to which the foreign key constraint is applied.

The contact_id column in the contacts table is called the parent key and the contact_id column in the phones table is called the foreign key or foreign key column.

In the database world, referential integrity is a mechanism to ensure that the relationship of data between tables remains consistent. And to enforce the referential integrity, you use foreign key constraints. Therefore, foreign key constraints are also known as referential integrity constraints or referential constraints.

Db2 FOREIGN KEY constraint syntax

The following illustrates the syntax of defining a foreign key constraint:

[CONSTRAINT constraint_name]
FOREIGN KEY (fk1, fk2,...)
REFERENCES parent_table(c1,2,..) 
	ON UPDATE [ NO ACTION | RESTRICT]
	ON DELETE [ NO ACTION | RESTRICT | CASCADE | SET NULL];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify a constraint name in the CONSTRAINT clause. The CONSTRAINT clause is optional. If you omit it, Db2 will generate a name for the foreign key constraint.

Second, specify a list of comma-separated foreign key columns enclosed by parentheses in the FOREIGN KEY clause.

Third, specify the name of the parent table and a list of comma-separated columns to which the foreign key columns reference.

 ON UPDATE rules

Db2 triggers the ON UPDATE rule when you update a row in either parent or child table. The update rule has two options NO ACTION and RESTRICT.

When you update the row in the parent key column of the parent table, Db2 rejects the update if there is the corresponding row exists in the child table for both RESTRICT and NO ACTION option.

When you update the row in the foreign key column of the child table, Db2 rejects the update for RESTRICT option and allows the update for the NO ACTION, with the condition that the new value of the foreign key column exists in the parent table.

 ON DELETE rules

Db2 triggers the ON DELETE rule when you delete a row in the parent table. Db2 determines whether or not to delete the rows in the child table based on the following options:

• NO ACTION or RESTRICT does not delete any row in both tables and issues an error.
• CASCADE deletes the row in the parent table and all related rows in the child table.
• SET NULL deletes the row in the parent table and updates values in the foreign key columns in the child table to NULL only if these columns are not nullable columns.

You can use the foreign key constraint to define foreign keys in the CREATE TABLE or ALTER TABLE statement.

Db2 FOREIGN KEY constraint examples

Let’s take some example of using the foreign key constraint to understand it better.

1) Creating a table which has a single foreign key example

First, insert a new contact into the contacts table:

INSERT INTO contacts(first_name, last_name) 
VALUES('John','Doe');
Code language: SQL (Structured Query Language) (sql)

The contact John Doe has the contact id 1:

SELECT * FROM contacts;
Code language: SQL (Structured Query Language) (sql)

Next, add two phones for the contact John Doe:

INSERT INTO 
	phones(phone_no, phone_type, contact_id)
VALUES
	('(408)-987-1234','HOME',1),
	('(408)-672-3424','WORK',1);
Code language: SQL (Structured Query Language) (sql)

Then, delete the contact id 1 from the contacts table. Because we declare the ON DELETE rule with the CASCADE action, Db2 will delete all phones of John Doe from the phones table:

DELETE FROM contacts
WHERE contact_id = 1;
Code language: SQL (Structured Query Language) (sql)

After, verify the deletion by querying data from the contacts table:

SELECT * FROM contacts;
Code language: SQL (Structured Query Language) (sql)

It returned no row.

Finally, view data in the contacts table by using the following SELECT statement:

SELECT * FROM phones;
Code language: SQL (Structured Query Language) (sql)

It also returns an empty set.

2) Creating a table with multiple foreign keys example

The following statement creates a new table called members:

CREATE TABLE members (
	member_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
	first_name VARCHAR(100) NOT NULL,
	last_name VARCHAR(100) NOT NULL,
	joined_date DATE NOT NULL,
	PRIMARY KEY(member_id)
);
Code language: SQL (Structured Query Language) (sql)

Suppose each member can have one or many favorite books and each book may belong to favorite lists of many users. The relationship between members and books are many-to-many.

The following favorite_books table stores the favorite books of members:

CREATE TABLE favorite_books(
	member_id INT NOT NULL,
	book_id INT NOT NULL,
	FOREIGN KEY (book_id)
		REFERENCES books(book_id) 
		ON UPDATE RESTRICT 
		ON DELETE CASCADE,
	FOREIGN KEY (member_id)
		REFERENCES members(member_id) 
		ON UPDATE RESTRICT 
		ON DELETE CASCADE
);
Code language: SQL (Structured Query Language) (sql)

The favorite_books table has two foreign keys. The first one refers to the book_id column of the books table and the second one references to the member_id of the members table.

The favorite_books table is known as an associative table, pivot table, or mapping table. We often use these kinds of tables to manage the many-to-many relationship.

In this tutorial, you have learned about Db2 foreign key and how to use the foreign key constraint to enforce referential integrity.