SQL Table Constraints

Most SQL you write reads data and hopes it is sound. Aconstraint goes the other way: it states what the data must look like, and the database rejects anything that disagrees. The rule then holds no matter who writes to the table or which tool they use.

create or replace table order_imports (
  order_id integer primary key,
  customer_email varchar not null,
  status varchar default 'pending',
  quantity integer check (quantity > 0),
  coupon_code varchar unique
)

What each one rejects

Every one of those is a Constraint Error, raised on theINSERT or UPDATE that broke the rule. The whole statement fails; nothing partial lands.

NOT NULL and DEFAULT

NOT NULL says the column must always have a value.DEFAULT supplies one when the insert does not, and the two work well together: the column can never be empty, and callers do not have to care.

create or replace table order_imports (
  order_id integer,
  status varchar not null default 'pending',
  loaded_at timestamp not null default current_timestamp
)

A default only applies when the column is left out of the insert. Passing an explicit null is not leaving it out — that hits theNOT NULL and fails, which is usually what you want.

CHECK

CHECK takes any expression that evaluates to true or false. This is where the rules specific to your business live.

create or replace table order_imports (
  quantity integer check (quantity > 0),
  discount_pct decimal(5, 2) check (discount_pct between 0 and 100),
  status varchar check (status in ('pending', 'shipped', 'cancelled'))
)

A CHECK is documentation that cannot go stale. Six months later the table itself still says a discount is a percentage between 0 and 100.

UNIQUE and PRIMARY KEY

Both refuse duplicates. The difference is that a primary key also forbids nulls and marks the column as the identifier for a row — a table gets exactly one, and any number of unique constraints.

create or replace table order_lines (
  order_id integer,
  line_no integer,
  product_id integer,
  primary key (order_id, line_no)
)

A composite key is unique across the combination, so(1, 1) and (1, 2) both fit while a second(1, 1) does not.

FOREIGN KEY

A foreign key says a value here must exist over there, which is what keeps orders from pointing at customers who were never created.

create or replace table customers_ref (
  customer_id integer primary key,
  name varchar
);

create or replace table orders_ref (
  order_id integer primary key,
  customer_id integer references customers_ref(customer_id)
);

The reference is enforced in both directions. Inserting an order for a customer who does not exist fails, and so does deleting a customer who still has orders — DuckDB reports that the keyis still referenced by a foreign key in a different table.

Reading the rules off a table

select constraint_type, constraint_text
from duckdb_constraints()
where table_name = 'order_imports'
order by constraint_type, constraint_text

Worth running before you write to an unfamiliar table. It is faster than finding out from an error, and much faster than finding out from a report.

Constraints on a table that already exists

You can add most rules later withALTER TABLE, but only if the rows already comply — adding NOT NULL to a column containing nulls fails, and that is the constraint doing its job. Clean the data first, then add the rule.

How much is worth enforcing

Constraints cost you on write and pay you on read. For a table people load into repeatedly, that trade is good — the rule catches the bad row on the day it arrives, next to the code that produced it. For a throwaway analysis table, skip them; you are the only writer and you will drop it this afternoon.

• Give every table people write to a primary key.
• NOT NULL the columns your queries would silently mis-count without — every join key, every amount you sum.
• Turn the rules you already check in a WHERE clause into aCHECK, so nobody has to remember them.
• Add foreign keys where a broken reference would be a real incident, and accept that you will delete children first.