A sound relational schema makes identity, relationships, and data rules explicit. In PostgreSQL 18, use primary keys to identify rows, UNIQUE constraints to protect alternate identifiers and combinations, foreign keys to enforce valid references, and CHECK and NOT NULL constraints for row-level requirements. This FAQ explains what each does, how to choose among them, and where PostgreSQL-specific behavior matters.
What is a primary key?
A primary key designates the column or group of columns used to identify each row. PostgreSQL requires its values to be unique and non-null, and permits at most one primary-key constraint per table. A primary key may contain multiple columns.
As an Amazon Associate I earn from qualifying purchases.
For example, a table of accounts might use a generated identifier as its primary key:
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsCREATE TABLE accounts (
account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
external_code text NOT NULL UNIQUE
);Here, account_id identifies rows, while external_code is a separate business identifier that must not repeat. PostgreSQL’s constraint documentation describes primary-key and unique constraints.
#1 Best Overall
When should I use a composite key?
Use a composite key when the data rule says that a combination of values identifies a row. For example, an enrollment table may permit a student to enroll in a course only once:
CREATE TABLE enrollments (
student_id bigint NOT NULL,
course_id bigint NOT NULL,
enrolled_at date NOT NULL,
PRIMARY KEY (student_id, course_id)
);The constraint prevents duplicate student-course pairs; it does not make either column unique by itself. PostgreSQL supports multi-column primary keys and UNIQUE constraints. If applications also need a compact identifier independent of that pair, a separate primary key can coexist with a composite UNIQUE constraint:
CREATE TABLE enrollments (
enrollment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_id bigint NOT NULL,
course_id bigint NOT NULL,
enrolled_at date NOT NULL,
UNIQUE (student_id, course_id)
);Choose based on the identity rule and how the identifier will be used. A composite key directly expresses a natural tuple identity; a separate key gives the row a standalone identifier while the UNIQUE rule still protects the real-world combination.
Free tools Windows power users keep installed
One-click scans. No signup required.
What does a foreign key do?
A foreign key requires values in one table to correspond to an eligible key in another table. It prevents a row from referring to a parent that does not exist, preserving referential integrity. In PostgreSQL, the referenced columns must be a primary key, a UNIQUE constraint, or columns covered by a non-partial unique index.
CREATE TABLE cities (
city_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE weather_readings (
reading_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
city_id bigint NOT NULL REFERENCES cities (city_id),
observed_on date NOT NULL
);With city_id NOT NULL, each reading must identify a city, and PostgreSQL rejects an insert or update that names no existing city. The official foreign-key tutorial demonstrates this behavior. The constraints reference documents eligible referenced keys and foreign-key rules.
How do I model relationships?
One-to-many
Put the foreign key on the many-side table. In the weather example, many readings can reference one city. If every reading must have a city, use NOT NULL as shown; if a reading may exist without a city, allow the foreign-key column to be null.
One-to-one
Put a foreign key on the dependent table and make that column UNIQUE so that no two dependent rows can point to the same parent. Add NOT NULL if every dependent row must have a parent. Whether the parent must also have a dependent row is a separate rule and is not established merely by the child-side foreign key.
Many-to-many
Represent the association with a junction table containing foreign keys to each participating table. A composite primary key across those foreign keys prevents duplicate pairs:
CREATE TABLE student_courses (
student_id bigint NOT NULL REFERENCES students (student_id),
course_id bigint NOT NULL REFERENCES courses (course_id),
PRIMARY KEY (student_id, course_id)
);Use additional columns on the junction table when the relationship itself has attributes, such as an enrollment date or role.
Should I use ON DELETE CASCADE?
Choose a referential action to match the meaning and retention needs of the relationship. PostgreSQL supports actions including CASCADE, SET NULL, SET DEFAULT, RESTRICT, and NO ACTION. For example:
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
item_number integer NOT NULL,
product_id bigint NOT NULL REFERENCES products (product_id) ON DELETE RESTRICT,
PRIMARY KEY (order_id, item_number)
);Deleting an order removes its dependent items in this example; deleting a product referenced by an item is blocked. CASCADE is appropriate only when child rows should share the parent’s lifecycle. SET NULL is suitable only if the relationship is optional and the foreign-key column permits nulls. SET DEFAULT assigns the column’s default, which must still satisfy the foreign key. PostgreSQL distinguishes RESTRICT, which prevents the operation immediately, from NO ACTION, which checks the constraint at the end of the statement or transaction when deferred. See the PostgreSQL referential-action documentation.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Which other constraints should I use?
- NOT NULL makes a value mandatory.
- UNIQUE prevents duplicate values or duplicate combinations of columns.
- CHECK enforces a predicate about the row being written, such as a positive quantity.
- FOREIGN KEY ensures a reference matches an eligible key in another table.
CREATE TABLE invoice_lines (
invoice_id bigint NOT NULL REFERENCES invoices (invoice_id),
line_number integer NOT NULL CHECK (line_number > 0),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (invoice_id, line_number)
);These constraints make invalid writes fail at the database boundary, regardless of which application issued them. PostgreSQL cautions that CHECK constraints should not be used to enforce conditions involving other rows or tables: changes to those other rows can invalidate the condition without rechecking the original row. Use an appropriate UNIQUE, EXCLUDE, or FOREIGN KEY constraint for supported cross-row rules. See PostgreSQL’s CHECK constraint guidance.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Do foreign keys create indexes?
In PostgreSQL, primary keys and UNIQUE constraints create unique B-tree indexes. PostgreSQL does not automatically create an index on the referencing foreign-key columns. The referenced side is protected by an eligible key; the referencing side may need its own index depending on how the application reads and maintains the data.
Consider an index on a foreign-key column when queries frequently join or filter by it, or when parent rows are often updated or deleted and PostgreSQL must find referencing rows. For example:
CREATE INDEX weather_readings_city_id_idx
ON weather_readings (city_id);Do not add one mechanically to every foreign key. Table size, query patterns, maintenance costs, and observed query plans should guide the choice. PostgreSQL explains the referencing-side lookup consideration in its constraint documentation and index behavior in CREATE TABLE.
How should I review a schema?
- Identify what uniquely and stably identifies each row; make that the primary key.
- Add UNIQUE constraints for business identifiers and combinations that must not repeat.
- For each relationship, decide whether it is one-to-one, one-to-many, or many-to-many, and whether participation is mandatory or optional.
- Use foreign keys to enforce valid references and choose delete/update actions based on lifecycle and retention requirements.
- Use NOT NULL and CHECK for required values and row-level rules; do not rely on CHECK for cross-row conditions.
- Evaluate indexes against actual joins, filters, parent updates/deletes, table size, and query plans.
These examples use PostgreSQL documentation, including PostgreSQL 18 material; behavior such as null handling, accepted referenced keys, and index creation can vary by database engine and version. Verify the rules for the engine the schema will run on.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
