Designing Tables

Defining structure: DDL and DML

SQL statements fall into a few families. The ones that read and change rows — SELECT, INSERT, UPDATE, DELETE — are covered on Querying with SELECT and Changing Data. This page is about the family that builds the containers those rows live in.

Data definition language (DDL) is the part of SQL that creates, changes and removes database objects: tables, indexes, views. Its three main statements are CREATE, ALTER and DROP. Data manipulation language (DML) works on the rows inside those objects. A third group, data control language (DCL: GRANT and REVOKE), handles permissions and is covered on Database Security.

The difference matters in practice. A DDL change affects every program that uses the table, is often harder to undo, and in some systems (MySQL, Oracle) ends the current transaction automatically. In production, DDL is usually applied through reviewed schema migrations rather than typed by hand (see Working with Databases from Code).

CREATE TABLE, step by step

Suppose the web shop wants customers to review the products they bought. Before writing SQL, list what a review must hold: which product, which customer, a rating from 1 to 5, an optional comment, and a moderation status that starts as "pending". Each customer may review a product only once. That list translates almost line by line into a table definition:

CREATE TABLE product_reviews (
  review_id    INTEGER PRIMARY KEY,
  product_id   INTEGER NOT NULL REFERENCES products (product_id),
  customer_id  INTEGER NOT NULL REFERENCES customers (customer_id),
  rating       INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment      TEXT,
  status       TEXT    NOT NULL DEFAULT 'pending',
  UNIQUE (product_id, customer_id)
);

INSERT INTO product_reviews (product_id, customer_id, rating, comment)
VALUES (3, 1, 5, 'Quiet clicks, long battery life'),
       (5, 2, 4, NULL);

SELECT * FROM product_reviews ORDER BY review_id;

review_id  product_id  customer_id  rating  comment                          status
---------  ----------  -----------  ------  -------------------------------  -------
1          3           1            5       Quiet clicks, long battery life  pending
2          5           2            4       NULL                             pending

Read the definition one line at a time:

• CREATE TABLE product_reviews ( … ); names the table; the parentheses hold a comma-separated list of column definitions and table-level constraints.
• Each column definition is name, type, constraints: rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5) reads "a whole number, always present, between 1 and 5".
• review_id INTEGER PRIMARY KEY identifies each row. The INSERT did not supply it, so SQLite numbered the rows 1 and 2.
• REFERENCES products (product_id) makes product_id a foreign key: a review can only point to a product that exists.
• comment TEXT has no NOT NULL, so it is optional — the second review has none.
• DEFAULT 'pending' fills status when the INSERT leaves it out.
• UNIQUE (product_id, customer_id) is a table-level constraint over two columns: the combination must be unique, which is exactly "one review per customer per product".

Data types across systems

A column's data type says what kind of value it holds, how it is stored, how it sorts and which operations make sense. Choosing the right type is the first line of defense: a DATE column cannot hold "next Tuesday", and comparisons on it are date comparisons, not text comparisons. The names differ between products; the ideas are the same.

A few guidelines that hold everywhere. Use the smallest type that surely fits, but do not be stingy with identifiers: a table that may pass two billion rows needs a 64-bit bigint key. For moments in time (when an order was placed), prefer a type that knows the time zone, or store UTC consistently; a bare timestamp without zone is ambiguous once servers or users sit in different zones. varchar(n) limits length, which is useful as a sanity check (a country code is never 200 characters), not as a performance trick. In SQL Server, nvarchar is the usual choice for text in any language.

Money: exact decimals, never floats

Floating-point types (REAL, FLOAT, DOUBLE) store numbers in binary and cannot represent most decimal fractions exactly. That is fine for a temperature and wrong for an invoice:

SELECT 0.1 + 0.2 AS total,
       0.1 + 0.2 = 0.3 AS is_equal,
       10 + 20 = 30 AS cents_equal;

total                is_equal  cents_equal
-------------------  --------  -----------
0.30000000000000004  0         1

SQLite prints a false comparison as 0 and a true one as 1. The floating-point sum is not exactly 0.3, while whole cents compare exactly. In PostgreSQL, MySQL, SQL Server and Oracle, DECIMAL / NUMERIC with a fixed number of decimals is exact. SQLite has no exact decimal type (a DECIMAL column there stores fractions as floating-point numbers), so store whole cents as an INTEGER. The shop database keeps prices in whole euros only to make the examples easy to read.

SQLite: type affinity and STRICT tables

Most database systems reject a value that does not match the column type. SQLite, by default, is flexible: the declared type gives a column a type affinity, a preferred type that SQLite tries to convert values to. If the conversion is not lossless, the value is stored as it is. The function typeof() shows what was really stored:

CREATE TABLE readings (sensor TEXT, value INTEGER);
INSERT INTO readings VALUES ('A', 42), ('B', '42'), ('C', 'high'), ('D', 3.5);
SELECT sensor, value, typeof(value) AS stored_as FROM readings ORDER BY sensor;

sensor  value  stored_as
------  -----  ---------
A       42     integer
B       42     integer
C       high   text
D       3.5    real

The text '42' became an integer, but 'high' and 3.5 were accepted unchanged in an INTEGER column. Since version 3.37 (2021), SQLite offers STRICT tables that enforce the declared type, as other systems do. A strict table allows only the types INT, INTEGER, REAL, TEXT, BLOB and ANY:

CREATE TABLE readings (sensor TEXT, value INTEGER) STRICT;
INSERT INTO readings VALUES ('B', '42');
SELECT sensor, value, typeof(value) AS stored_as FROM readings;
INSERT INTO readings VALUES ('C', 'high');

sensor  value  stored_as
------  -----  ---------
B       42     integer

Error: cannot store TEXT value in INTEGER column readings.value

Text that converts losslessly ('42') is still converted; anything else is refused. When a CHECK constraint is not enough and you use SQLite for real data, strict tables are worth considering.

Constraints: rules the database enforces

A constraint is a rule declared on a column or table that the database checks on every INSERT and UPDATE; a change that would break the rule fails with an error and nothing is written. Validation in the application is still useful for friendly messages, but only constraints protect the data from every program, script and person that touches the table.

NOT NULL

A NOT NULL constraint means the column must always have a value. Columns without it accept NULL ("unknown or not applicable"), which is a common source of surprises in queries (see Filtering, NULL and Functions). Make columns NOT NULL unless there is a real reason a value can be missing. In the shop, every product needs a category:

INSERT INTO products (name, category, price) VALUES ('Cable ties', NULL, 4);

Error: NOT NULL constraint failed: products.category

UNIQUE

A UNIQUE constraint forbids two rows with the same value in a column (or the same combination in a group of columns, as in product_reviews). The shop's customers.email is unique, so a second account with Sanne's address is refused:

INSERT INTO customers (first_name, last_name, email, country, signup_date)
VALUES ('Sam', 'Jones', 'sanne.devries@example.com', 'NL', '2026-10-01');

Error: UNIQUE constraint failed: customers.email

Unlike a primary key, a UNIQUE column may contain NULL. In SQLite, PostgreSQL and MySQL any number of rows may have NULL there (unknown values are not considered equal); SQL Server allows only one. The database enforces uniqueness with an index behind the scenes (see Indexes and Query Performance).

CHECK

A CHECK constraint holds a condition that must be true (or NULL) for every row. The shop uses it for prices, stock and order status. An update that sets a price to zero fails:

UPDATE products SET price = 0 WHERE product_id = 1;

Error: CHECK constraint failed: price > 0

A CHECK sees only the row being written. It can compare columns of that row (valid_until >= valid_from), but it cannot look at other rows or tables; that is the job of UNIQUE and FOREIGN KEY constraints, or of application logic.

DEFAULT

DEFAULT is not a rule but a value to use when an INSERT leaves the column out. The shop's stock defaults to 0:

INSERT INTO products (name, category, price) VALUES ('Mouse pad', 'Accessories', 8);
SELECT product_id, name, price, stock FROM products WHERE product_id = 11;

product_id  name       price  stock
----------  ---------  -----  -----
11          Mouse pad  8      0

A default can also be an expression such as DEFAULT CURRENT_TIMESTAMP, which fills in the moment the row was inserted. A default only applies when the column is left out; an explicit NULL is stored as NULL (or refused, if the column is NOT NULL).

PRIMARY KEY and automatic numbering

A primary key (see The Relational Model) combines UNIQUE and NOT NULL and marks the column or columns that identify a row. Most tables use a surrogate key — a meaningless number the database hands out — because natural values such as email addresses change. How the numbering is declared differs per system; the statements below are not run here:

In PostgreSQL, the review table's key would read:

CREATE TABLE product_reviews (
  review_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  rating     integer NOT NULL CHECK (rating BETWEEN 1 AND 5)
  -- other columns as before
);

In SQLite, a column declared exactly INTEGER PRIMARY KEY becomes an alias for the row's internal rowid, and a new row normally gets the highest current value plus one. Adding AUTOINCREMENT guarantees that numbers of deleted rows are never reused, at a small cost; the SQLite documentation recommends it only when that guarantee is needed. In every system, generated keys can have gaps (after rollbacks or deletes): treat them as identifiers, not as a count of rows.

Foreign keys and referential integrity

A foreign key declares that a column's values must match a key in another table. The database then guarantees referential integrity: every reference points to a row that actually exists. Without it, an order could belong to customer 99, who was never created, and every report that joins orders to customers would silently lose that order.

The rule works in both directions. You cannot create a child row that points nowhere, and you cannot remove a parent row that is still referenced. In the shop, Ana (customer 8) has no orders and can be deleted; Sanne (customer 1) has orders, so deleting her fails:

DELETE FROM customers WHERE customer_id = 8;
SELECT count(*) AS customers FROM customers;
DELETE FROM customers WHERE customer_id = 1;

customers
---------
7

Error: FOREIGN KEY constraint failed

ON DELETE: what happens to the children

A foreign key can say what to do with child rows when their parent is deleted (and, with ON UPDATE, when the parent's key changes):

SQL Server has no RESTRICT (NO ACTION is its default), and in MySQL NO ACTION and RESTRICT behave identically.

A cascading delete removes dependent rows automatically. Here a supplier has contact people (cascade) and deliveries (set to NULL, so the delivery history survives):

CREATE TABLE suppliers (
  supplier_id  INTEGER PRIMARY KEY,
  name         TEXT NOT NULL UNIQUE
);

CREATE TABLE supplier_contacts (
  contact_id   INTEGER PRIMARY KEY,
  supplier_id  INTEGER NOT NULL
               REFERENCES suppliers (supplier_id) ON DELETE CASCADE,
  name         TEXT NOT NULL
);

CREATE TABLE deliveries (
  delivery_id  INTEGER PRIMARY KEY,
  supplier_id  INTEGER
               REFERENCES suppliers (supplier_id) ON DELETE SET NULL,
  delivered_on TEXT NOT NULL
);

INSERT INTO suppliers VALUES (1, 'CableWorks'), (2, 'Northwind Peripherals');
INSERT INTO supplier_contacts VALUES (1, 1, 'Iris Vos'), (2, 2, 'Bram Smit'), (3, 2, 'Noor Ali');
INSERT INTO deliveries VALUES (1, 1, '2026-09-01'), (2, 2, '2026-09-03');

DELETE FROM suppliers WHERE supplier_id = 2;

SELECT * FROM supplier_contacts ORDER BY contact_id;
SELECT * FROM deliveries ORDER BY delivery_id;

contact_id  supplier_id  name
----------  -----------  --------
1           1            Iris Vos

delivery_id  supplier_id  delivered_on
-----------  -----------  ------------
1            1            2026-09-01
2            NULL         2026-09-03

One DELETE removed one supplier, two contact people and one delivery reference. That is convenient and dangerous at the same time: a cascade can reach several tables deep. Use CASCADE only where the children truly have no meaning on their own. For customers and orders, the default (refuse) is the safer choice — orders are financial records.

Naming conventions

Names are read far more often than they are written. Whatever convention a team picks, consistency matters more than the choice itself. The shop follows a common one:

• Lowercase snake_case: order_items, signup_date. Unquoted names are case-insensitive in most systems (MySQL table names on Linux are an exception), and PostgreSQL folds them to lowercase; mixed case then forces you to quote every name.
• Plural table names, singular column names (customers.email). Some teams prefer singular table names; pick one.
• Keys named after the table: customer_id in customers and the same name in orders, so a join reads naturally.
• No reserved words or spaces: a column called order or user needs quoting in some systems.
• Named constraints with a prefix, such as chk_discount_period or fk_orders_customer. The name appears in error messages and makes the constraint easy to drop or change later.
• Units in the name when a number has one: weight_grams, price_cents.

Changing a table later: ALTER and DROP

Requirements change, and so do tables. ALTER TABLE changes an existing table without losing its rows. Adding a column is the most common change; existing rows get NULL (or the column's default):

ALTER TABLE products ADD COLUMN weight_grams INTEGER;
UPDATE products SET weight_grams = 95 WHERE product_id = 3;
SELECT product_id, name, weight_grams FROM products
WHERE product_id IN (3, 4) ORDER BY product_id;

product_id  name                 weight_grams
----------  -------------------  ------------
3           Wireless mouse       95
4           Mechanical keyboard  NULL

A new NOT NULL column needs a default in SQLite (and in PostgreSQL when the table already has rows), because the existing rows must get some value:

ALTER TABLE products ADD COLUMN sku TEXT NOT NULL;

Error: Cannot add a NOT NULL column with default value NULL

SQLite's limits, and other systems

SQLite's ALTER TABLE supports renaming a table, renaming a column, adding a column, (since 3.35) dropping a column and (since 3.53, 2026) setting or dropping NOT NULL on a column. It cannot change a column's type or add other constraints to an existing table — in the SQLite used for these examples (3.40), ALTER TABLE products ADD CONSTRAINT … is a syntax error. The documented workaround is to create a new table with the desired definition, copy the rows across, drop the old table and rename the new one, all inside one transaction. Other systems can do far more in place; for example, in PostgreSQL:

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
ALTER TABLE products ADD CONSTRAINT chk_products_price CHECK (price < 10000);

SQL Server writes the first as ALTER TABLE products ALTER COLUMN price decimal(10,2); and MySQL as ALTER TABLE products MODIFY price DECIMAL(10,2);. Both forms replace the whole column definition: restate NOT NULL (and in MySQL the DEFAULT), or it is dropped. On a large, busy table, some changes rewrite every row or lock the table for a long time; plan them as a migration (see Database Administration in Practice).

DROP TABLE

DROP TABLE removes a table: its definition, every row, its indexes and its constraints. There is no "are you sure?". With foreign keys switched on, SQLite first deletes the rows, so a table that is still referenced cannot be dropped:

DROP TABLE customers;

Error: FOREIGN KEY constraint failed

Designing from requirements

Good tables start as sentences, not as SQL. A worked example: the shop wants discount codes. The requirements, as the marketing team describes them:

1. Each code is a short word such as WELCOME10, unique.
2. A code gives between 5% and 50% off.
3. A code is valid from one date until another.
4. A code can be used once, unless marketing says otherwise.

Each sentence maps to a column and a constraint: (1) a text primary key; (2) an integer with a CHECK; (3) two dates and a CHECK that compares them; (4) a positive count with a DEFAULT of 1. Testing the design with a bad row is part of the work:

CREATE TABLE discount_codes (
  code         TEXT    NOT NULL PRIMARY KEY,
  percent_off  INTEGER NOT NULL
               CONSTRAINT chk_discount_percent CHECK (percent_off BETWEEN 5 AND 50),
  valid_from   TEXT    NOT NULL,
  valid_until  TEXT    NOT NULL,
  max_uses     INTEGER NOT NULL DEFAULT 1 CHECK (max_uses > 0),
  CONSTRAINT chk_discount_period CHECK (valid_until >= valid_from)
);

INSERT INTO discount_codes (code, percent_off, valid_from, valid_until)
VALUES ('WELCOME10', 10, '2026-10-01', '2026-12-31');

SELECT * FROM discount_codes;

INSERT INTO discount_codes (code, percent_off, valid_from, valid_until)
VALUES ('AUTUMN15', 15, '2026-11-30', '2026-11-01');

code       percent_off  valid_from  valid_until  max_uses
---------  -----------  ----------  -----------  --------
WELCOME10  10           2026-10-01  2026-12-31   1

Error: CHECK constraint failed: chk_discount_period

The named constraint makes the error self-explanatory: the period ends before it starts. A short checklist for any new table:

• What is one row? ("one discount code", "one line of an order")
• What identifies a row, and will that value ever change?
• Which columns are required (NOT NULL)?
• Which values must be unique, alone or in combination?
• Which ranges, lists or comparisons must always hold (CHECK)?
• Which other tables does it reference, and what should happen on delete?
• Is each fact stored only once? (See Normalization.)