{"id":170,"date":"2024-11-20T16:32:49","date_gmt":"2024-11-20T09:32:49","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=170"},"modified":"2025-01-02T08:51:13","modified_gmt":"2025-01-02T01:51:13","slug":"postgresql-foreign-key","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-foreign-key\/","title":{"rendered":"PostgreSQL Foreign Key"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about foreign keys and how to use PostgreSQL foreign key constraints to create foreign key columns for a table.<\/p>\n\n\n\n

Introduction to foreign keys #<\/a><\/h2>\n\n\n\n

In PostgreSQL, a foreign key is a column or set of columns in one table that references the primary key<\/a> of another table<\/strong>. It serves as a link between the two tables.<\/p>\n\n\n\n

For example, the following table products<\/code> has a foreign key column called brand_id<\/code> that references the brand_id<\/code> column of the table brands<\/code>:<\/p>\n\n\n\n

\"postgresql<\/figure>\n\n\n\n

The table with the foreign key column is called the child table<\/em>, while the table with the primary key column that the child table references is known as the referenced or parent table<\/em>.<\/p>\n\n\n\n

In this example, the table brands<\/code> is the referenced <\/em>or parent table<\/em>, while the products table is the child table<\/em>.<\/p>\n\n\n\n

A foreign key ensures data in the child table matches the related data in the parent table. For example, if you have a brand id in the products<\/code> table, that brand id should match a brand id in the brands<\/code> table.<\/p>\n\n\n\n

A foreign key helps maintain referential integrity<\/em>. For instance, if a product references a brand, that brand must exist in the brands<\/code> table.<\/p>\n\n\n\n

When you delete a row in the parent table, you should handle the child table properly to avoid orphaned records.<\/p>\n\n\n\n

Note that orphaned records are records in a table referencing a non-existent primary key value in another table.<\/p>\n\n\n\n

For example, if a row in the brands<\/code> is deleted, all the related rows in the products<\/code> table should be deleted automatically.<\/p>\n\n\n\n

Foreign keys in one-to-many relationship #<\/a><\/h2>\n\n\n\n

When you have two tables with a one-to-many relationship, the table on the “many” side will have the foreign key column.<\/p>\n\n\n\n

For example, the relationship between the brands<\/code> and products<\/code> tables is one-to-many. The products<\/code> table is the “many” side; it should get the foreign key column:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The products<\/code> table has an additional column called brand_id<\/code> that references the brand_id<\/code> of the brands<\/code> table.<\/p>\n\n\n\n

Similarly, the relationship between the categories<\/code> and products<\/code> tables is a one-to-many relationship.<\/p>\n\n\n\n

The products<\/code> table has a foreign key column called category_id<\/code> that references the category_id<\/code> column of the categories<\/code> table:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

A table may have multiple foreign key columns, like the products<\/code> table.<\/p>\n\n\n\n

The values in the foreign key columns are equal to those of the primary key of the reference rows. For example, the value in the brand_id<\/code> column of row one is equal to the value in the brand_id<\/code> column of the brands<\/code> table.<\/p>\n\n\n\n

By convention, the foreign key column has the format table_id<\/code>. For example, the foreign key columns are category_id<\/code> and brand_id<\/code>.<\/p>\n\n\n\n

Multiple rows can have the same foreign key column values. For example, both rows with the product name iPhone 14 Pro<\/code> and iPhone 15 Pro<\/code> have the same brand_id<\/code> 1.<\/p>\n\n\n\n

Foreign keys in a many-to-many relationship #<\/a><\/h2>\n\n\n\n

In PostgreSQL, you use two one-to-many relationships to model a many-to-many relationship.<\/p>\n\n\n\n

For example, the relationship between products<\/code> and tags<\/code> tables is many-to-many.<\/p>\n\n\n\n

To model this relationship, we create a new table called product_tags<\/code>, often called a link table<\/em>:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The product_tags<\/code> will have two foreign keys; one references the product_id<\/code> column in the products table, and another references the tag_id<\/code> column in the tags<\/code> table.<\/p>\n\n\n\n

Foreign key constraints #<\/a><\/h2>\n\n\n\n

In PostgreSQL, you use the foreign key constraint to set up a foreign key.<\/p>\n\n\n\n

Here’s the basic syntax for defining a foreign key constraint:<\/p>\n\n\n

CONSTRAINT<\/span> constraint_name<\/span>\nFOREIGN KEY<\/span> (fk_column)\nREFERENCES<\/span> table<\/span>(pk_column)\nON<\/span> DELETE<\/span> delete_action\nON<\/span> UPDATE<\/span> update_action;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n