The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Create a tableCREATE TABLE accounts (id bigint PRIMARY KEY, email text NOT NULL);View examples
Generate identifiersid bigint GENERATED ALWAYS AS IDENTITYView examples
Require a valuename text NOT NULLView examples
Supply a defaultcreated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMPView examples
Validate a conditionCONSTRAINT products_price_nonnegative CHECK (price >= 0)View examples
Declare a primary keyCONSTRAINT accounts_pkey PRIMARY KEY (id)View examples
Use a composite keyPRIMARY KEY (order_id, product_id)View examples
Require uniquenessCONSTRAINT accounts_email_key UNIQUE (email)View examples
Reference another tableFOREIGN KEY (customer_id) REFERENCES customers (id)View examples
Delete dependent rowsFOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADEView examples
Detach on deletionFOREIGN KEY (manager_id) REFERENCES employees (id) ON DELETE SET NULLView examples
Name a ruleCONSTRAINT quantity_positive CHECK (quantity > 0)View examples
Add a constraintALTER TABLE products ADD CONSTRAINT products_sku_key UNIQUE (sku);View examples
Defer existing-row validationALTER TABLE orders ADD CONSTRAINT orders_total_check CHECK (total >= 0) NOT VALID;View examples
Validate existing rowsALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;View examples
Remove a constraintALTER TABLE products DROP CONSTRAINT products_sku_key;View examples

Constraints move essential data rules into PostgreSQL so every writer follows them. Choose types first, make nullability explicit, give important rules stable names, and select foreign-key actions from the lifecycle of the related records rather than convenience.

Step by step

Detailed examples

01

Start with explicit types and generated identity

A table definition is easier to evolve when each column has the narrowest useful type and its identity strategy is explicit. GENERATED ALWAYS AS IDENTITY normally rejects user-supplied identifiers, which protects the sequence; GENERATED BY DEFAULT permits intentional overrides. Identity generation does not itself guarantee uniqueness, so pair it with a primary key or unique constraint.

A typed account table
CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY,
  email text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT accounts_pkey PRIMARY KEY (id)
);
Output
CREATE TABLE
Back to quick reference ↑
02

Express nullability, defaults, and checks

NOT NULL answers whether missing data is valid; DEFAULT supplies a value only when the insert omits the column. A CHECK passes when its expression is true or null, so columns participating in mandatory checks usually need NOT NULL too. Keep checks dependent on the current row and use immutable logic so later changes cannot invalidate stored data silently.

Products with bounded data
CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  price numeric(12, 2) NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT products_price_nonnegative CHECK (price >= 0)
);
Output
CREATE TABLE
Back to quick reference ↑
03

Choose keys that match row identity

A primary key is both unique and not null, and a table has at most one. A UNIQUE constraint protects alternate identifiers and can cover several columns. PostgreSQL permits multiple nulls in a unique constraint by default; use NULLS NOT DISTINCT when null should compare as an equal value. Composite keys are appropriate when the combination—not either column alone—identifies a row.

Order lines identified by a pair
CREATE TABLE order_items (
  order_id bigint NOT NULL,
  product_id bigint NOT NULL,
  quantity integer NOT NULL,
  CONSTRAINT order_items_pkey PRIMARY KEY (order_id, product_id),
  CONSTRAINT order_items_quantity_positive CHECK (quantity > 0)
);
Output
CREATE TABLE
A named alternate key
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  CONSTRAINT users_email_key UNIQUE (email)
);
Output
CREATE TABLE
Back to quick reference ↑
04

Model relationships and deletion policy

A foreign key protects referential integrity but does not automatically index the referencing columns, so add an index when joins or parent deletes need it. The default action rejects removal of a referenced row. CASCADE fits components that have no meaning without their owner; SET NULL fits optional relationships and requires the affected columns to be nullable.

Owned order items and optional managers
CREATE TABLE employees (
  id bigint PRIMARY KEY,
  manager_id bigint,
  CONSTRAINT employees_manager_fkey
    FOREIGN KEY (manager_id) REFERENCES employees (id) ON DELETE SET NULL
);

CREATE TABLE orders (id bigint PRIMARY KEY);
CREATE TABLE order_notes (
  id bigint PRIMARY KEY,
  order_id bigint NOT NULL,
  note text NOT NULL,
  CONSTRAINT order_notes_order_fkey
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
);
Output
CREATE TABLE
CREATE TABLE
CREATE TABLE
Back to quick reference ↑
05

Name durable business rules

PostgreSQL can generate constraint names, but explicit names make violations and migrations easier to understand. Use a consistent table-column-rule pattern and name table-level constraints where they are declared. The name must be unique within the table, not across the whole database.

Readable validation errors
CREATE TABLE inventory (
  sku text PRIMARY KEY,
  quantity integer NOT NULL,
  CONSTRAINT inventory_quantity_nonnegative CHECK (quantity >= 0)
);
Output
CREATE TABLE
Back to quick reference ↑
06

Change constraints with existing data in mind

Adding most constraints checks every existing row and fails if old data violates the rule. PostgreSQL supports NOT VALID for foreign keys and CHECK constraints, allowing new writes to be protected before a later VALIDATE CONSTRAINT scan. Clean and inspect the data first, use stable constraint names, and understand the locks and index work required before running a production migration.

Stage and validate a check
ALTER TABLE orders
  ADD CONSTRAINT orders_total_check CHECK (total >= 0) NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_total_check;
Output
ALTER TABLE
ALTER TABLE
Add and later remove uniqueness
ALTER TABLE products
  ADD CONSTRAINT products_sku_key UNIQUE (sku);

ALTER TABLE products
  DROP CONSTRAINT products_sku_key;
Output
ALTER TABLE
ALTER TABLE
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: CREATE TABLEpostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL: Constraintspostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: ALTER TABLEpostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: Default Valuespostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback