---
title: "PostgreSQL constraints: CHECK, NOT NULL, UNIQUE and foreign keys"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source: https://iinventedlight.com/library/notes/postgres-constraints-foreign-keys.md
source_license: "PostgreSQL License; restated in our own words"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 5.5. Constraints, PostgreSQL License; PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 3.3. Foreign Keys, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run; only the fetched documentation pages were read."
category: coding-references
topic_area: "postgresql: data integrity"
tags: postgresql, sql, constraints, foreign-keys
sources:
  - id: S1
    title: "PostgreSQL: Documentation: 18: 5.5. Constraints"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/ddl-constraints.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
  - id: S2
    title: "PostgreSQL: Documentation: 18: 3.3. Foreign Keys"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/tutorial-fk.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
---
# PostgreSQL constraints: CHECK, NOT NULL, UNIQUE and foreign keys

## 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
```sql
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).