CREATE TABLE and constraints

Why let the database check the rules?

Your app should check input, but apps have bugs, and many programs (a website, a mobile app, a night-time import script) may write to the same table. A constraint is a rule stored in the table itself. The database checks it on every insert and update, from every program, every time. Bad data never gets in.

The shape of CREATE TABLE

CREATE TABLE accounts (
  id       INTEGER PRIMARY KEY,
  holder   TEXT NOT NULL,
  email    TEXT UNIQUE,
  branch   TEXT NOT NULL DEFAULT 'Pune',
  balance  INTEGER NOT NULL DEFAULT 0 CHECK (balance >= 0)
);

Using DEFAULT

INSERT INTO accounts (id, holder, email, branch, balance)
VALUES (1, 'Asha Verma', 'asha@example.com', 'Mumbai', 15000);
INSERT INTO accounts (id, holder, email) VALUES (2, 'Ravi Kumar', 'ravi@example.com');
SELECT * FROM accounts;
-- 1 | Asha Verma | asha@example.com | Mumbai | 15000
-- 2 | Ravi Kumar | ravi@example.com | Pune   | 0

Notice the column list after the table name: (id, holder, email). Naming the columns in an INSERT is a good habit. The statement keeps working even if someone adds a column to the table later.

Table-level constraints

A constraint that involves several columns is written after the last column, as its own line. That is how order_items gets its composite primary key and its foreign keys.

CREATE TABLE order_items (
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty        INTEGER NOT NULL CHECK (qty > 0),
  price      INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

INTEGER PRIMARY KEY and AUTOINCREMENT in SQLite

In SQLite, a column declared exactly INTEGER PRIMARY KEY fills itself in: leave it out of the INSERT and SQLite uses the largest id so far plus one. If you delete the row with the largest id, that number can be used again.

Adding the word AUTOINCREMENT (INTEGER PRIMARY KEY AUTOINCREMENT) promises that an id is never reused, even after deletes. It costs a little extra work, and the SQLite docs advise using it only when you truly need that promise, for example when old ids are printed on receipts.

-- Insert 'p' and 'q' (ids 1 and 2), delete id 2, insert 'r':
-- INTEGER PRIMARY KEY               ->  r gets id 2 (reused)
-- INTEGER PRIMARY KEY AUTOINCREMENT ->  r gets id 3 (never reused)

Changing and removing tables

ALTER TABLE customers ADD COLUMN loyalty_points INTEGER NOT NULL DEFAULT 0;
DROP TABLE IF EXISTS old_imports;

SQLite's ALTER TABLE can add, rename and drop columns and rename a table, with limits: an added column cannot be UNIQUE or a PRIMARY KEY, and a NOT NULL column needs a DEFAULT, or you get "Cannot add a NOT NULL column with default value NULL".

Common mistakes

• A comma after the last column, or a missing comma between columns. Both are syntax errors; read the error's "near ..." part to find the spot.
• Expecting CHECK to reject NULL. CHECK (marks > 0) passes when marks is NULL, because the result is unknown, not false. Add NOT NULL as well if the value is required.
• Leaving out the column list in INSERT, then breaking when a column is added. Name the columns.
• Running CREATE TABLE twice and getting "table already exists". Use CREATE TABLE IF NOT EXISTS in setup scripts.