PostgreSQL Foreign Key (original) (raw)

Summary: 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.

Introduction to foreign keys #

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

For example, the following table products has a foreign key column called brand_id that references the brand_id column of the table brands:

The table with the foreign key column is called the child table, while the table with the primary key column that the child table references is known as the referenced or parent table.

In this example, the table brands is the referenced or parent table, while the products table is the child table.

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 table, that brand id should match a brand id in the brands table.

A foreign key helps maintain referential integrity. For instance, if a product references a brand, that brand must exist in the brands table.

When you delete a row in the parent table, you should handle the child table properly to avoid orphaned records.

Note that orphaned records are records in a table referencing a non-existent primary key value in another table.

For example, if a row in the brands is deleted, all the related rows in the products table should be deleted automatically.

Foreign keys in one-to-many relationship #

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

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

The products table has an additional column called brand_id that references the brand_id of the brands table.

Similarly, the relationship between the categories and products tables is a one-to-many relationship.

The products table has a foreign key column called category_id that references the category_id column of the categories table:

A table may have multiple foreign key columns, like the products table.

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 column of row one is equal to the value in the brand_id column of the brands table.

By convention, the foreign key column has the format table_id. For example, the foreign key columns are category_id and brand_id.

Multiple rows can have the same foreign key column values. For example, both rows with the product name iPhone 14 Pro and iPhone 15 Pro have the same brand_id 1.

Foreign keys in a many-to-many relationship #

In PostgreSQL, you use two one-to-many relationships to model a many-to-many relationship.

For example, the relationship between products and tags tables is many-to-many.

To model this relationship, we create a new table called product_tags, often called a link table:

The product_tags will have two foreign keys; one references the product_id column in the products table, and another references the tag_id column in the tags table.

Foreign key constraints #

In PostgreSQL, you use the foreign key constraint to set up a foreign key.

Here’s the basic syntax for defining a foreign key constraint:

CONSTRAINT constraint_name FOREIGN KEY (fk_column) REFERENCES table(pk_column) ON DELETE delete_action ON UPDATE update_action;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the name of the foreign key constraint in the CONSTRAINT clause. If you don’t, PostgreSQL will automatically generate a constraint name.
• Second, place the foreign key column (fk_column) inside the parentheses () after the FOREIGN KEY keywords.
• Third, provide the table and primary key column that the foreign key column references after the REFERENCES keyword.
• Finally, set the delete and update actions to specify the behaviors when a row in the parent table primary key in the table is deleted and updated.

The FOREIGN KEY and REFERENCES clauses are mandatory, while the CONSTRAINT, ON DELETE, and ON UPDATE clauses are optional.

Foreign key constraint example #

First, create a new table called brands:

CREATE TABLE brands ( brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(255) NOT NULL );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new table called products with the foreign key column brand_id that references the brand_id column of the brands table:

CREATE TABLE products ( product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL, brand_id INT NOT NULL, FOREIGN KEY (brand_id) REFERENCES brands (brand_id) );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, insert three rows into the brands table:

INSERT INTO brands(name) VALUES ('Apple'), ('Samsung'), ('Google') RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

brand_id | name ----------+--------- 1 | Apple 2 | Samsung 3 | GoogleCode language: plaintext (plaintext)

Inserting data into a foreign key table #

If you insert a new row into the products table, the value of the brand_id must exist in the brands table. For example, the following insert a new row into the products table with the brand_id 1:

INSERT INTO products(name, price, brand_id) VALUES('iPhone 14 Pro', 999.99, 1) RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

product_id | name | price | brand_id ------------+---------------+--------+---------- 1 | iPhone 14 Pro | 999.99 | 1Code language: plaintext (plaintext)

You will encounter an error if you attempt to insert a row with a brand id that does not exist in the brands table.

The following statement attempts to insert a new row into the products table with an invalid brand id:

INSERT INTO products(name, price, brand_id) VALUES('iPhone 15 Pro', 1299.99, 11) RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

PostgreSQL issues the following error:

insert or update on table "products" violates foreign key constraint "products_brand_id_fkey"Code language: plaintext (plaintext)

Dropping tables with foreign key constraints #

When you drop a table referenced by other tables via foreign key constraints, PostgreSQL will issue an error.

For example, the products table references the brands table via a foreign key constraint. If you drop the brands table, you will get an error:

DROP TABLE brands;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Error:

cannot drop table brands because other objects depend on itCode language: plaintext (plaintext)

To drop the brands table, you follow these steps:

• First, drop foreign key constraint that references the brand_id column in the table brands.
• Second, drop the table brands.

You can do both steps using the DROP TABLE ... CASCADE statement:

DROP TABLE brands CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

This statement dropped the constraint products_brand_id_fkey on the table products.

Let’s also drop the products table:

DROP TABLE products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Deletion actions #

The deletion action allows you to specify an action to the rows in the child tables when rows in the parent table are deleted:

ON DELETE delete_action;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The delete_action can be one of the following:

• NO ACTION – issues a constraint violation error. The NO ACTION is the default.
• SET NULL – sets the values of foreign key column to NULL.
• CASCADE – deletes all related rows in the child table.
• SET DEFAULT – sets the default values for the foreign key columns in the child table.
• RESTRICT works like the NO ACTION.

ON DELETE SET NULL #

First, recreate the brands table:

CREATE TABLE brands ( brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(255) NOT NULL );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create the products table that has the brand_id as the foreign key column referenced the brand_id column of the brands table:

CREATE TABLE products ( product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL, brand_id INT, FOREIGN KEY (brand_id) REFERENCES brands (brand_id) ON DELETE SET NULL );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, insert rows into the brands and products tables:

`INSERT INTO brands(name) VALUES ('Apple'), ('Samsung') RETURNING *;

INSERT INTO products(name, price, brand_id) VALUES ('iPhone 14 Pro', 999.99, 1), ('iPhone 15 Pro', 1299.99, 1), ('Galaxy S23 Ultra', 1299.99, 2) RETURNING *;`Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Fourth, delete a row from the brands table with the id 1:

DELETE FROM brands WHERE brand_id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Finally, retrieve data from the products table:

SELECT * FROM products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

product_id | name | price | brand_id ------------+------------------+---------+---------- 3 | Galaxy S23 Ultra | 1299.99 | 2 1 | iPhone 14 Pro | 999.99 | NULL 2 | iPhone 15 Pro | 1299.99 | NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output shows that the ON DELETE SET NULL action sets the values of the brand_id column of the related products to NULL.

ON DELETE CASCADE #

First, drop the brands and products table:

DROP TABLE brands, products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create the brands table and products table with the ON DELETE CASCADE action:

CREATE TABLE brands ( brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(255) NOT NULL ); CREATE TABLE products ( product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL, brand_id INT, FOREIGN KEY (brand_id) REFERENCES brands (brand_id) ON DELETE CASCADE );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, insert rows into the products tables:

`INSERT INTO brands(name) VALUES('Apple'), ('Samsung') RETURNING *;

INSERT INTO products(name, price, brand_id) VALUES ('iPhone 14 Pro', 999.99, 1), ('iPhone 15 Pro', 1299.99, 1), ('Galaxy S23 Ultra', 1299.99, 2) RETURNING *;`Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Fourth, delete a row from the brands table with the id 1:

DELETE FROM brands WHERE brand_id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Finally, retrieve data from the products table:

SELECT * FROM products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

product_id | name | price | brand_id ------------+------------------+---------+---------- 3 | Galaxy S23 Ultra | 1299.99 | 2Code language: plaintext (plaintext)

The output shows that the ON DELETE CASCADE action delete the rows in the products table with the brand_id column 1.

ON DELETE SET DEFAULT #

First, drop the brands and products tables:

DROP TABLE brands, products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create the brands table:

CREATE TABLE brands ( brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(255) NOT NULL );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, create the products table:

CREATE TABLE products ( product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL, brand_id INT DEFAULT 1, FOREIGN KEY (brand_id) REFERENCES brands (brand_id) ON DELETE SET DEFAULT );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this statement, we set the default value of the brand_id column to 1.

Fourth, insert rows into the brands table:

INSERT INTO brands(name) VALUES('Unknown'), ('Apple'), ('Samsung') RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The row with id 1 will be “Unknown“.

Fifth, insert rows into products table:

INSERT INTO products(name, price, brand_id) VALUES ('iPhone 14 Pro', 999.99, 2), ('iPhone 15 Pro', 1299.99, 2), ('Galaxy S23 Ultra', 1299.99, 3) RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Sixth, delete a row from the brands table with the id 2:

DELETE FROM brands WHERE brand_id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Due to the ON DELETE SET DEFAULT action, the statement sets the value of the brand_id column of the rows with brand_id 2 to 1.

Seventh, retrieve data from the products table:

SELECT * FROM products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

product_id | name | price | brand_id ------------+------------------+---------+---------- 3 | Galaxy S23 Ultra | 1299.99 | 3 1 | iPhone 14 Pro | 999.99 | 1 2 | iPhone 15 Pro | 1299.99 | 1Code language: plaintext (plaintext)

The output shows that the ON DELETE SET DEFAULT action sets the values of the brand_id column of the related products to a default value of the brand_id column, which is 1.

Summary #

• A foreign key is a column or set of columns that references the primary key of another table.
• A table can have one or more foreign key columns.
• Use the PostgreSQL foreign key constraint to create a foreign key for a table.
• Use the ON DELETE SET NULL to set the value of the foreign key columns to NULL when the corresponding rows from the parent table are deleted.
• Use the ON DELETE CASCADE to delete the rows in the child table when the corresponding rows from the parent table are deleted.
• Use the ON DELETE SET DEFAULT to set the values of the foreign key columns to their default values when the corresponding rows from the parent table are deleted.
• Use the ON DELETE NO ACTION to prevent the rows from the parent table to be deleted when they have referenced rows in the child tables.

Quiz #

Was this tutorial helpful ?