Constraints in SQL

A column's type stops the wrong kind of value. It can't stop a rating of 9, a visit with no supplier, or two visits with the same id. Those are rules about values, and a constraint writes them into the table:

create or replace table supplier_visits (
  visit_id    integer primary key,
  supplier_id integer not null,
  visited_on  date not null,
  rating      integer check (rating between 1 and 5),
  follow_up   boolean not null default false
);

insert into supplier_visits (visit_id, supplier_id, visited_on, rating)
values (1, 4, '2024-04-18', 4);

select * from supplier_visits;

What each rule rejects

• primary key: a second row with the same visit_id, or a row with none. DuckDB reports Duplicate key "visit_id: 1" violates primary key constraint.
• not null: a missing value, including an explicit null: NOT NULL constraint failed: supplier_visits.supplier_id.
• check (...): a value that makes the condition false: CHECK constraint failed on table supplier_visits.

Each one is a Constraint Error, and it fails the whole statement. Insert two rows where only the second breaks a rule, and neither is added. The same goes for an UPDATE: if one changed row would break a CHECK, no row changes.

CHECK lets NULL through

A CHECK rejects a row only when its condition is false. For a missing rating, NULL between 1 and 5 is unknown, not false, so the row is accepted:

create or replace table visit_scores (
  rating integer check (rating between 1 and 5)
);

insert into visit_scores values (3), (null);

select count(*) as visits, count(rating) as rated
from visit_scores;

Both rows go in, and only one has a rating. It's the reverse of WHERE, which keeps a row only when its condition is true. Lesson 2.8 · True, false, and unknown covers the three answers. Either way, an unknown slips past a rule written only with true and false in mind, so a value that must exist and must be in range needs both rules: not null check (rating between 1 and 5). Whenever you write a rule, ask what it does with a NULL.

A foreign key points at another table

references plant_tags (tag_id) means every tag_id here must already exist in plant_tags. The column it points at must be a primary key or unique:

create or replace table plant_tags (
  tag_id   integer primary key,
  tag_name varchar not null
);

create or replace table product_tags (
  product_id integer not null,
  tag_id     integer not null references plant_tags (tag_id)
);

insert into plant_tags values (1, 'shade'), (2, 'pet safe');
insert into product_tags values (4, 1), (4, 2);

select * from product_tags;

Tagging a product with tag 3 fails, because no tag 3 exists. So does deleting tag 1 while product 4 still uses it. The Garden Shop tables here are loaded from CSV files without keys, so a foreign key to products fails: there is no primary key or unique constraint for referenced table "products". DuckDB also refuses to drop or replace plant_tags while product_tags points at it, so running that example a second time fails until you drop product_tags first.