Tables, Types and Integrity Constraints

Until now, you have queried existing tables. Modeling is the step before: deciding which tables to create, with which columns and which rules. A good model makes queries simple and keeps inconsistent data out; a bad model forces you to fix the data constantly.

From entity to table

You start from the business vocabulary. An entity is something you want to keep track of: a customer, a product, an order. It becomes a table. Its attributes (name, email, sign-up date) become columns, each with a type.

CREATE TABLE clients (
    id            INTEGER PRIMARY KEY,            -- unique identifier
    email         TEXT    NOT NULL UNIQUE,        -- required and without duplicates
    nom           TEXT    NOT NULL,
    code_postal   TEXT    CHECK (length(code_postal) = 5),
    date_inscription TEXT NOT NULL DEFAULT (date('now')),
    actif         INTEGER NOT NULL DEFAULT 1 CHECK (actif IN (0, 1))
);

Choosing types

Data	PostgreSQL	SQLite
Identifier	bigint GENERATED ALWAYS AS IDENTITY	INTEGER PRIMARY KEY
Text	text or varchar(n)	TEXT
Amount	numeric(10,2)	INTEGER in cents
Date / timestamp	date, timestamptz	TEXT in ISO format
Boolean	boolean	INTEGER 0/1

Two rules: a postal code or a phone number is text (a leading zero must not disappear), and an amount is never stored as a floating-point number (real, float), which produces rounding errors.

SQLite has flexible typing: an INTEGER column accepts text. Since version 3.37, the STRICT keyword at the end of CREATE TABLE enforces types. PostgreSQL always enforces types.

The primary key

The primary key identifies each row: it is unique and never null. Two options:

• a natural key, coming from the business (social security number, a country’s ISO code);
• a surrogate key, a meaningless identifier generated by the database.

The surrogate key is generally preferred: an email or a customer number can change, and changing them would break all the references. The business data stays protected by a UNIQUE constraint.

Constraints

• NOT NULL: the value is required.
• UNIQUE: no two rows with the same value (several NULLs are still allowed).
• CHECK (condition): the condition must be true (prix > 0, quantite BETWEEN 1 AND 100).
• DEFAULT value: value used if the insert does not provide one.

Constraints apply to all applications that write to the database: import script, back office, API. This is far safer than a check in a single form.

Check before constraining

On an already populated table, adding a constraint fails if some rows violate it. So you start by looking for them:

-- Duplicate emails, ignoring case
SELECT lower(email) AS email_normalise, COUNT(*) AS nb
FROM import_clients
GROUP BY lower(email)
HAVING COUNT(*) > 1;

You fix or merge the rows, then add the constraint. In PostgreSQL: ALTER TABLE clients ADD CONSTRAINT clients_email_key UNIQUE (email);. SQLite does not let you add a constraint to an existing table: you recreate the table and copy the data into it.