← All notes

Short answer

Constraints let the database reject bad data. CHECK tests a Boolean expression; foreign keys make sure a referenced row exists. Name your constraints so errors are clear. [S1][S2]

Example

CREATE TABLE cities (name text PRIMARY KEY);
CREATE TABLE weather (
  city  text REFERENCES cities(name),
  price numeric CONSTRAINT positive_price CHECK (price > 0)
);

Inserting a city not in cities fails with a foreign-key error. [S2]

Claims

• id: C1 claim: A violating insert raises an error. source: S1 evidence: "If a user attempts to store data in a column that would violate a constraint"
• id: C2 claim: CHECK is the most generic constraint type. source: S1 evidence: "A check constraint is the most generic constraint type."
• id: C3 claim: Named constraints clarify error messages. source: S1 evidence: "This clarifies error messages and allows you to refer to the constraint"
• id: C4 claim: Inserting a missing key violates the foreign key. source: S2 evidence: "violates foreign key constraint"

Sources

• [S1] PostgreSQL: Documentation: 18: 5.5. Constraints. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/ddl-constraints.html . License: PostgreSQL License. Accessed 2026-11-04.
• [S2] PostgreSQL: Documentation: 18: 3.3. Foreign Keys. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/tutorial-fk.html . License: PostgreSQL License. Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

@misc{iil_note_c1fzvexe,
  author = {{iinventedlight}},
  title = {{PostgreSQL constraints: CHECK, NOT NULL, UNIQUE and foreign keys}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-constraints-foreign-keys/},
  note = {Permanent ID iil:note:c1fzvexe. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-constraints-foreign-keys.md.