Constraints

Draft

Constraints move basic data rules into PostgreSQL. They make invalid states harder to store, no matter which application sends the SQL.

Insert valid data

The row passes NOT NULL, UNIQUE, PRIMARY KEY, and CHECK constraints.

Loaded Database:constraints.database-init

INSERT INTO products (sku, name, price_cents)
VALUES ('TEE-001', 'PgQuest T-shirt', 3200)
RETURNING *;

id	sku	name	price_cents
3	TEE-001	PgQuest T-shirt	3200

Insert on products  (cost=0.00..0.01 rows=1 width=72)
  ->  Result  (cost=0.00..0.01 rows=1 width=72)

Inspect constraints

PostgreSQL stores constraints in system catalogs that you can query.

Loaded Database:constraints.database-init

SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'products'::regclass
ORDER BY conname;

conname	contype
products_id_not_null	n
products_name_not_null	n
products_pkey	p
products_price_cents_check	c
products_price_cents_not_null	n
products_sku_key	u
products_sku_not_null	n

Sort  (cost=7.44..7.44 rows=1 width=65)
  Sort Key: conname
  ->  Seq Scan on pg_constraint  (cost=0.00..7.43 rows=1 width=65)
        Filter: (conrelid = '16385'::oid)

What We Learned

PRIMARY KEYidentifies each row and prevents duplicate ids.
UNIQUEprevents duplicate values in selected columns.
CHECKrequires each row to satisfy a boolean expression.
pg_constraintis a PostgreSQL catalog containing table constraints.