How to use foreign keys in PostgreSQL

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.

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 customers table.

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 CASCADE specifies that if the customer_id in the customers table is updated, the customer_id in the orders table will also be updated.
• ON DELETE RESTRICT clause specifies that if a row in the customers table is deleted, the foreign key constraint will be checked, and if there are any matching rows in the orders table, the delete operation will be rejected.

You can see a full list of foreign key constraints and triggers in the official PostgreSQL docs

Managing foreign key relationships through SQL gives you precise control, but Beekeeper Studio makes this process visual and intuitive. When creating or editing foreign keys in Beekeeper’s table editor, you’ll get a dropdown of available tables and columns to link to, eliminating syntax errors and making relationship management straightforward.

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