{"id":593,"date":"2019-06-04T17:57:26","date_gmt":"2019-06-05T01:57:26","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=593"},"modified":"2020-04-11T06:58:52","modified_gmt":"2020-04-11T14:58:52","slug":"db2-foreign-key","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-foreign-key\/","title":{"rendered":"Db2 Foreign Key"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 foreign key constraint to enforce the referential integrity between the data across tables.<\/p>\n\n\n\n

Introduction to the Db2 foreign key<\/h2>\n\n\n\n

Let’s take a look at the contacts<\/code> and phones<\/code> tables:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> contacts(\n\tcontact_id INT<\/span> NOT<\/span> NULL<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span>,\n\tfirst_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n\tlast_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n\tPRIMARY KEY<\/span>(contact_id)\n);\n\nCREATE<\/span> TABLE<\/span> phones(\n\tphone_id INT<\/span> NOT<\/span> NULL<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span>,\n\tphone_no VARCHAR<\/span>(20<\/span>) NOT<\/span> NULL<\/span>,\n\tphone_type VARCHAR<\/span>(10<\/span>) NOT<\/span> NULL<\/span>,\n\tcontact_id INT<\/span> NOT<\/span> NULL<\/span>,\n\tPRIMARY KEY<\/span>(phone_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this diagram, each contact may have zero or many phones<\/code> 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.<\/p>\n\n\n\n
For each row in the phones<\/code> table, you can always find a corresponding row in the contacts<\/code> table. But the current setup does not ensure this relationship. It means you can insert a new row into the phones<\/code> table with the contact identification (contact_id<\/code>) that does not exist in the contacts<\/code> table.<\/p>\n\n\n\n
Furthermore, if you delete a contact, all the phones<\/code> of the deleted contact will remain in the phones<\/code> table. The rows in the phones<\/code> table that does not have corresponding rows in the contacts<\/code> table are called orphaned rows.<\/p>\n\n\n\n
In order to enforce the relationship between contacts<\/code> and phones<\/code> tables, you need to use a foreign key constraint.<\/p>\n\n\n\n
What is a foreign key?<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
Back to our example, the contact_id<\/code> in the phones<\/code> table should be the foreign key of the phones<\/code> table. Because for each phone in the phones<\/code> table, you can find a corresponding contact in the contacts<\/code> table.<\/p>\n\n\n\n
To add a foreign key constraint to the phones<\/code> table, you use the following ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> phones\nFOREIGN<\/span> KEY<\/span> (contact_id)\n\tREFERENCES<\/span> contacts(contact_id)\n\t\tON<\/span> UPDATE<\/span> NO<\/span> ACTION<\/span>\n\t\tON<\/span> DELETE<\/span> CASCADE<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The contacts<\/code> table is called the parent table to which the foreign key references. The phones<\/code> table is called the child table (or dependent table) to which the foreign key constraint is applied.<\/p>\n\n\n\n
The contact_id<\/code> column in the contacts<\/code> table is called the parent key and the contact_id<\/code> column in the phones<\/code> table is called the foreign key or foreign key column.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Db2 FOREIGN KEY constraint syntax<\/h2>\n\n\n\n
The following illustrates the syntax of defining a foreign key constraint:<\/p>\n\n\n
[CONSTRAINT constraint_name]\nFOREIGN KEY (fk1, fk2,...)\nREFERENCES parent_table(c1,2,..) \n\tON UPDATE<\/span> [ NO<\/span> ACTION<\/span> | RESTRICT]\n\tON<\/span> DELETE<\/span> [ NO<\/span> ACTION<\/span> | RESTRICT | CASCADE<\/span> | SET<\/span> NULL<\/span>];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify a constraint name in the CONSTRAINT<\/code> clause. The CONSTRAINT<\/code> clause is optional. If you omit it, Db2 will generate a name for the foreign key constraint.<\/p>\n\n\n\n
Second, specify a list of comma-separated foreign key columns enclosed by parentheses in the FOREIGN KEY<\/code> clause.<\/p>\n\n\n\n
Third, specify the name of the parent table and a list of comma-separated columns to which the foreign key columns reference.<\/p>\n\n\n\n
 ON UPDATE rules<\/h3>\n\n\n\n
Db2 triggers the ON UPDATE<\/code> rule when you update a row in either parent or child table. The update rule has two options NO ACTION<\/code> and RESTRICT<\/code>.<\/p>\n\n\n\n
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<\/code> and NO ACTION<\/code> option.<\/p>\n\n\n\n
When you update the row in the foreign key column of the child table, Db2 rejects the update for RESTRICT<\/code> option and allows the update for the NO ACTION<\/code>, with the condition that the new value of the foreign key column exists in the parent table.<\/p>\n\n\n\n
 ON DELETE rules<\/h3>\n\n\n\n
Db2 triggers the ON DELETE<\/code> 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:<\/p>\n\n\n\n