What are constraints and what types are available?

Constraints are rules the database enforces on column values, so bad data is rejected when it is written. The main types are:

• NOT NULL stops a column from holding NULL.
• UNIQUE stops two rows from sharing a value.
• PRIMARY KEY combines NOT NULL and UNIQUE to identify each row.
• FOREIGN KEY keeps referential integrity by requiring each value to match a row in another table.
• CHECK rejects values that fail a condition, such as a total of zero or less.
• DEFAULT supplies a value when an insert gives none.

A CHECK also passes when its condition is NULL, so pair it with NOT NULL when the column must have a value.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT (CURRENT_DATE),
    total_amount DECIMAL(10,2) CHECK (total_amount > 0),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);