Constraints: rules for the data

In short: Constraints such as NOT NULL, DEFAULT, UNIQUE, CHECK and PRIMARY KEY are rules in CREATE TABLE that make the table keep its data correct on its own.

Problem

A table that takes anything

Here is a table for the suppliers with no rules at all. Run the query:

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  vat TEXT,
  rating INTEGER
);
INSERT INTO suppliers (name, vat, rating) VALUES
  ('Zora Bakery', '201000001', 5),
  (NULL, '201000002', 4),
  ('Zora Bakery', '201000001', 7);
SELECT * FROM suppliers;

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT, vat TEXT, rating INTEGER ); INSERT INTO suppliers (name, vat, rating) VALUES ('Zora Bakery', '201000001', 5), (NULL, '201000002', 4), ('Zora Bakery', '201000001', 7); SELECT * FROM suppliers;

▶ RunRestore the queryFresh database

A supplier with no name got in. The same VAT number got in twice. A rating of 7 on a scale from 1 to 5 got in. Nobody checked.

In the module on changing data, the shop rejected such rows with errors. Those errors come from rules written in CREATE TABLE. Now you will write them.

The idea

NOT NULL and DEFAULT

The rules are written after the column's type. They are called constraints.

• NOT NULL: the column cannot be empty.
• DEFAULT value: what to store if the INSERT gives no value.

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL DEFAULT 'Sofia'
);
INSERT INTO suppliers (name) VALUES ('Balkan Dairy');
INSERT INTO suppliers (name, city) VALUES ('Sunny Farm', 'Plovdiv');
SELECT * FROM suppliers;

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL DEFAULT 'Sofia' ); INSERT INTO suppliers (name) VALUES ('Balkan Dairy'); INSERT INTO suppliers (name, city) VALUES ('Sunny Farm', 'Plovdiv'); SELECT * FROM suppliers;

▶ RunRestore the queryFresh database

The dairy got “Sofia”, because its city was not given. A supplier with no name does not get in:

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL DEFAULT 'Sofia'
);
INSERT INTO suppliers (city) VALUES ('Varna');

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL DEFAULT 'Sofia' ); INSERT INTO suppliers (city) VALUES ('Varna');

▶ RunRestore the queryFresh database

DEFAULT CURRENT_DATE stores today's date. It is handy for “when it was added”:

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  added_at TEXT NOT NULL DEFAULT CURRENT_DATE
);
INSERT INTO suppliers (name) VALUES ('Balkan Dairy');
SELECT name, added_at = CURRENT_DATE AS added_today FROM suppliers;

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, added_at TEXT NOT NULL DEFAULT CURRENT_DATE ); INSERT INTO suppliers (name) VALUES ('Balkan Dairy'); SELECT name, added_at = CURRENT_DATE AS added_today FROM suppliers;

▶ RunRestore the queryFresh database

1 means “yes, it is today's date”. Replace added_at = CURRENT_DATE with added_at alone and you will see the date itself.

More precisely, if you are curious

CURRENT_DATE in SQLite is the date in UTC, not in local time. In Bulgaria, between midnight and 2 or 3 in the morning, the local date is already the new one while UTC is still the old one. CURRENT_TIMESTAMP gives the date and time, also in UTC.

Try it

The required fields

The table deliveries has id, supplier_id, amount and status. Make the supplier and the amount required, and the status required with the default 'expected'.

SQLempty database

CREATE TABLE deliveries (
  id INTEGER PRIMARY KEY,
  supplier_id INTEGER,
  amount REAL,
  status TEXT
);

CREATE TABLE deliveries ( id INTEGER PRIMARY KEY, supplier_id INTEGER, amount REAL, status TEXT );

Check▶ Run

The idea

UNIQUE

UNIQUE forbids two equal values in the column. A company has one VAT number:

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  vat TEXT UNIQUE
);
INSERT INTO suppliers (name, vat) VALUES ('Zora Bakery', '201000001');
INSERT INTO suppliers (name, vat) VALUES ('Zora Ltd', '201000001');

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, vat TEXT UNIQUE ); INSERT INTO suppliers (name, vat) VALUES ('Zora Bakery', '201000001'); INSERT INTO suppliers (name, vat) VALUES ('Zora Ltd', '201000001');

▶ RunRestore the queryFresh database

Sometimes it is the pair that is unique. Each supplier gives one price for a product. Two suppliers may offer the same product, and one supplier offers many products. The rule is written separately, after the columns, with both columns in brackets:

SQLempty database

CREATE TABLE supplier_prices (
  supplier_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  price REAL NOT NULL,
  UNIQUE (supplier_id, product_id)
);
INSERT INTO supplier_prices VALUES (1, 1, 0.95), (2, 1, 0.9), (1, 2, 1.4);
INSERT INTO supplier_prices VALUES (1, 1, 0.85);

CREATE TABLE supplier_prices ( supplier_id INTEGER NOT NULL, product_id INTEGER NOT NULL, price REAL NOT NULL, UNIQUE (supplier_id, product_id) ); INSERT INTO supplier_prices VALUES (1, 1, 0.95), (2, 1, 0.9), (1, 2, 1.4); INSERT INTO supplier_prices VALUES (1, 1, 0.85);

▶ RunRestore the queryFresh database

The first three rows go in. The fourth is a second price from supplier 1 for product 1.

More precisely, if you are curious

UNIQUE allows many NULLs: two suppliers without a VAT number do not count as equal, because NULL is not equal to NULL. If the VAT number is required, write vat TEXT NOT NULL UNIQUE. PostgreSQL works the same way. SQL Server by default lets only one NULL into a UNIQUE column.

The idea

CHECK

CHECK (condition) lets the row in only if the condition is true. The condition is like the one in WHERE:

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5)
);
INSERT INTO suppliers (name, rating) VALUES ('Sunny Farm', 7);

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, rating INTEGER CHECK (rating BETWEEN 1 AND 5) ); INSERT INTO suppliers (name, rating) VALUES ('Sunny Farm', 7);

▶ RunRestore the queryFresh database

When the condition is about several columns, CHECK goes after the columns. A promotion cannot end before it starts:

SQLempty database

CREATE TABLE promotions (
  product_id INTEGER NOT NULL,
  starts_on TEXT NOT NULL,
  ends_on TEXT NOT NULL,
  CHECK (ends_on >= starts_on)
);
INSERT INTO promotions VALUES (17, '2026-11-01', '2026-10-01');

CREATE TABLE promotions ( product_id INTEGER NOT NULL, starts_on TEXT NOT NULL, ends_on TEXT NOT NULL, CHECK (ends_on >= starts_on) ); INSERT INTO promotions VALUES (17, '2026-11-01', '2026-10-01');

▶ RunRestore the queryFresh database

SQLite's message says which condition failed. So keep the conditions short and clear.

See it

CHECK lets NULL through

The rating is from 1 to 5. And if it is not given?

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5)
);
INSERT INTO suppliers (name, rating) VALUES ('Sunny Farm', NULL);
SELECT * FROM suppliers;

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, rating INTEGER CHECK (rating BETWEEN 1 AND 5) ); INSERT INTO suppliers (name, rating) VALUES ('Sunny Farm', NULL); SELECT * FROM suppliers;

▶ RunRestore the queryFresh database

It got in. CHECK rejects the row only when the condition is false. NULL BETWEEN 1 AND 5 is “unknown”, like every comparison with NULL. This is the opposite of WHERE, which keeps only what is true.

Here that is right: a new supplier has no rating yet. But if the value is required, CHECK is not enough. You also need NOT NULL.

Try it

The suppliers' prices

The table supplier_prices has supplier_id, product_id and price. The price is required and greater than 0, and each supplier has only one price for a given product. Add the rules.

SQLempty database

CREATE TABLE supplier_prices (
  supplier_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  price REAL
);

CREATE TABLE supplier_prices ( supplier_id INTEGER NOT NULL, product_id INTEGER NOT NULL, price REAL );

Check▶ Run

The idea

PRIMARY KEY

You already know the primary key: the column by which a row is known. In CREATE TABLE it is a constraint like the others. It does not let in two rows with the same key:

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
INSERT INTO suppliers (id, name) VALUES (1, 'Zora Bakery');
INSERT INTO suppliers (id, name) VALUES (1, 'Balkan Dairy');

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); INSERT INTO suppliers (id, name) VALUES (1, 'Zora Bakery'); INSERT INTO suppliers (id, name) VALUES (1, 'Balkan Dairy');

▶ RunRestore the queryFresh database

When the key has two columns, it is written separately, like UNIQUE. That is how it is in order_items: a product is once in an order, and the amount is in quantity.

order_items

• PKorder_idinteger→ orders
• PKproduct_idinteger→ products
• quantityinteger *
• unit_pricereal *

More precisely, if you are curious

SQLite has an old quirk: a primary key that is not INTEGER PRIMARY KEY accepts NULL. Run it:

SQLempty database

CREATE TABLE coupons (
  code TEXT PRIMARY KEY,
  discount REAL
);
INSERT INTO coupons VALUES (NULL, 5), (NULL, 10);
SELECT * FROM coupons;

CREATE TABLE coupons ( code TEXT PRIMARY KEY, discount REAL ); INSERT INTO coupons VALUES (NULL, 5), (NULL, 10); SELECT * FROM coupons;

▶ RunRestore the queryFresh database

Two coupons with no code. It is a bug from the early versions that was kept so that old programs do not break. In a STRICT table the primary key does not accept NULL. Everywhere else, write code TEXT NOT NULL PRIMARY KEY. In PostgreSQL and MySQL the primary key is always NOT NULL.

Exercise

The suppliers, done right

Make the final table suppliers: id (primary key), name (required), vat (VAT number, required and with no repeats), city (required, with the default 'Sofia') and rating (a whole number from 1 to 5, may be missing). The check will also try to add bad suppliers.

SQLempty database

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  vat TEXT,
  city TEXT,
  rating INTEGER
);

CREATE TABLE suppliers ( id INTEGER PRIMARY KEY, name TEXT, vat TEXT, city TEXT, rating INTEGER );

Check▶ Run

Test

Check yourself

1. The column is city TEXT NOT NULL DEFAULT 'Sofia'. What happens with an INSERT that gives no city?

The city stays NULLThe city becomes “Sofia”A NOT NULL error

2. How do you say “a supplier has one price per product” if two suppliers may offer the same product?

UNIQUE on supplier_id and UNIQUE on product_idUNIQUE (supplier_id, product_id)NOT NULL on both columns

3. The column is rating INTEGER CHECK (rating BETWEEN 1 AND 5). What gets in?

Anything, CHECK is only a warning1 to 5, and NULLOnly 1 to 5

4. What does PRIMARY KEY (order_id, product_id) mean in order_items?

The pair of order and product cannot repeatThe table has two primary keysEach order has only one product

Check

Start the lesson over

Related lessons

• Builds on: Date functions · Primary key and foreign key · INSERT: new rows · CREATE TABLE: a new table
• Continues in: Foreign keys: links between tables · ALTER TABLE: changing a table · Relationship types: 1:1, 1:N, M:N · ER diagram: from a description to a schema
• Tasks on this topic: Tables and rules