![]() ![]() And to enforce the referential integrity, you use foreign key constraints. In the database world, referential integrity is a mechanism to ensure that the relationship of data between tables remains consistent. ![]() ![]() 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. The phones table is called the child table (or dependent table) to which the foreign key constraint is applied. The contacts table is called the parent table to which the foreign key references. To add a foreign key constraint to the phones table, you use the following ALTER TABLE statement: ALTER TABLE phones Because for each phone in the phones table, you can find a corresponding contact in the contacts table. The foreign key constraints define foreign keys.īack to our example, the contact_id in the phones table should be the foreign key of the phones table. What is a foreign key?Ī foreign key is a column or group of columns in a table that uniquely identifies a row in another table. In order to enforce the relationship between contacts and phones tables, you need to use a foreign key constraint. The rows in the phones table that does not have corresponding rows in the contacts table are called orphaned rows. 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.įurthermore, if you delete a contact, all the phones of the deleted contact will remain in the phones table. But the current setup does not ensure this relationship. The relationship between the contacts and phones is one-to-many.įor each row in the phones table, you can always find a corresponding row in the contacts table. However, each phone must belong to one and only one contact. In this diagram, each contact may have zero or many phones such as home phone, work phone, and emergency phone. Phone_id INT NOT NULL GENERATED ALWAYS AS IDENTITY,Ĭode language: SQL (Structured Query Language) ( sql ) Let’s take a look at the contacts and phones tables: CREATE TABLE contacts(Ĭontact_id INT NOT NULL GENERATED ALWAYS AS IDENTITY, 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |