A foreign key is a field in a database table that is used to establish a link to another table. In PostgreSQL, foreign keys are used to ensure data integrity and to enforce relationships between tables.
👋 Check out our easy to use desktop GUI for SQL
Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!
Available for MacOS, Linux, and Windows.
Creating a foreign key
To create a foreign key in PostgreSQL, you first need to create two tables: a parent table and a child table. The parent table is the table that the foreign key will reference, and the child table is the table that will contain the foreign key.
Here is an example of how to create a foreign key in PostgreSQL using SQL:
CREATE TABLE customers ( customer_id serial PRIMARY KEY, name varchar(255) ); CREATE TABLE orders ( order_id serial PRIMARY KEY, customer_id integer NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );
In this example, the
orders table is the child table and the
customers table is the parent table. The
customer_id field in the
orders table is the foreign key, and it is linked to the primary key (
customer_id) in the
Taking action on a parent record change
You can also specify the actions to be taken when a foreign key constraint is violated. For example, you can specify that the constraint should be checked on insert or update to the parent record, or that the constraint should be enforced using a trigger.
Here is an example of how to specify a foreign key constraint with on update and on delete actions:
CREATE TABLE orders ( order_id serial PRIMARY KEY, customer_id integer NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT );
In this example
ON UPDATE CASCADEspecifies that if the
customerstable is updated, the
orderstable will also be updated.
ON DELETE RESTRICTclause specifies that if a row in the
customerstable is deleted, the foreign key constraint will be checked, and if there are any matching rows in the
orderstable, the delete operation will be rejected.
You can see a full list of foreign key constraints and triggers in the official PostgreSQL docs
Disabling constraints / triggers
It is also possible to disable all foreign key constraints in PostgreSQL using the
DISABLE TRIGGER command. This is useful if you need to perform a bulk update or delete operation that would otherwise violate a foreign key constraint.
Here is an example of how to disable a foreign key constraint in PostgreSQL:
ALTER TABLE orders DISABLE TRIGGER ALL;
To re-enable the foreign key constraint, you can use the
ENABLE TRIGGER command:
ALTER TABLE orders ENABLE TRIGGER ALL;
PostgreSQL Foreign Key Wrap Up.
In summary, foreign keys are a powerful tool for enforcing relationships between tables in a database. They help to ensure data integrity and can be used to enforce complex business rules. By using foreign keys in PostgreSQL, you can ensure that your data is consistent and accurate, even as your database grows and changes over time.
You can read more about foreign keys in the official PostgreSQL tutorial