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
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) );
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
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
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
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;
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.
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.
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];
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
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
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:
RESTRICTdoes not delete any row in both tables and issues an error.
CASCADEdeletes the row in the parent table and all related rows in the child table.
SET NULLdeletes 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.
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
INSERT INTO contacts(first_name, last_name) VALUES('John','Doe');
John Doe has the contact id 1:
SELECT * FROM contacts;
Next, add two
phones for the contact
INSERT INTO phones(phone_no, phone_type, contact_id) VALUES ('(408)-987-1234','HOME',1), ('(408)-672-3424','WORK',1);
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
DELETE FROM contacts WHERE contact_id = 1;
After, verify the deletion by querying data from the
SELECT * FROM contacts;
It returned no row.
Finally, view data in the
contacts table by using the following
SELECT * FROM phones;
It also returns an empty set.
2) Creating a table with multiple foreign keys example
The following statement creates a new table called
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) );
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.
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 );
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
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.