CREATE TABLE and constraints

A table definition

CREATE TABLE customers (
    customer_id  INT PRIMARY KEY,
    email        VARCHAR(255) NOT NULL UNIQUE,
    name         VARCHAR(100) NOT NULL,
    city         VARCHAR(100),
    credit_limit DECIMAL(10,2) DEFAULT 0,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The constraints

• PRIMARY KEY — unique and not null, one per table. Usually also creates an index.
• NOT NULL — the column must have a value.
• UNIQUE — no duplicates, but nulls are allowed.
• DEFAULT — value used when none is supplied.
• CHECK — an arbitrary condition, e.g. CHECK (credit_limit >= 0).
• FOREIGN KEY — the value must exist in another table.

Foreign keys

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    total       DECIMAL(10,2) NOT NULL,
    order_date  DATE NOT NULL
);

This makes an orphaned order impossible — the database rejects a customer_id that does not exist.

Why constraints matter

Application code can enforce these rules too, but only for data arriving through that application. A constraint in the database holds regardless of what wrote the row — a script, a migration, a colleague with a SQL client.

The cost of a missing constraint is not an error; it is data that quietly stops making sense.