Postgresql Foreign Key syntax

sql

foreign-key

database-design

data-integrity

byAlex Kataev·Nov 19, 2024

Below is the syntax to create a foreign key in PostgreSQL:

ALTER TABLE child ADD CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent(id);

This command creates a foreign key named fk_parent in the child table. The parent_id column of the child table can only contain values existing in the id column of the parent table.

Defining foreign keys: a closer look

Altering tables for foreign keys

Defining a foreign key using ALTER TABLE is a typical way to integrate a foreign key after creating a table. It solves problems related to cyclical dependencies and working with existing data.

Composite keys and inline definitions

For composite primary keys (a primary key made up of multiple columns), inline definition is not a possibility. With composite keys, ALTER TABLE has to be used to define the foreign key using reference columns.

Naming your constraints, because clarity matters

It's advisable to give clear and descriptive names to your foreign key constraints. This approach makes it easier during troubleshooting and future adjustments in database schema.

Serial types and their limitations

Even though SERIAL type is used for auto-increment functionality, it cannot define foreign key. Instead, for SERIAL column to have a foreign key relation, integer or bigint types are to be used explicitly.

Watch your data types

It's crucial to ensure that the data types of foreign key column and the referenced primary key match. Any discrepancy in data types could lead to constraint creation failures.

Referential integrity: ensuring database consistency

Enforcing proper relationships

Foreign keys ensure consistency by allowing inserts or updates only when there's a valid link to an existing record. This rule helps avoid orphan records, thus maintaining logical consistency.

Managing deletion and updates

In foreign key constraints, you can specify actions like CASCADE, SET NULL, or NO ACTION to tackle delete or update events. This automation maintains database relationships.

Naming convention matters

Stick to a consistent naming convention, such as prefixing with fk_, for your foreign key constraints. This practice makes it easier to understand and manage your database schema.