SQL constraints: protect data with rules

Who this is for: learners familiar with tables, columns, and basic SQL inserts. The examples build on the shared customers-and-orders dataset from SQL SELECT, joins, NULL handling, and aggregation.

What you will learn: how constraints protect records, how to declare primary and foreign keys, and how NOT NULL, UNIQUE, and CHECK rules reject invalid values.

The examples use common SQL constraint syntax and were checked with SQLite. They are not tested against every MySQL or PostgreSQL version. SQLite requires foreign-key enforcement to be enabled for each connection; other database configuration can also affect enforcement.

Define rules in the table schema

A constraint is a rule the database checks when data is inserted or changed. It protects the data even when multiple applications or people write to the same database.

This schema keeps the same three customers and three orders used in earlier lessons. It adds a unique email address to customers, a foreign key from each order to a customer, and a check that order amounts are not negative:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL,
    email VARCHAR(120) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount DECIMAL(8, 2) NOT NULL,
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    CONSTRAINT orders_amount_nonnegative
        CHECK (amount >= 0)
);

INSERT INTO customers (customer_id, customer_name, email) VALUES
    (1, 'Asha', 'asha@example.test'),
    (2, 'Ben', 'ben@example.test'),
    (3, 'Chloe', 'chloe@example.test');

INSERT INTO orders (order_id, customer_id, amount) VALUES
    (101, 1, 120.00),
    (102, 1, 80.00),
    (103, 3, 200.00);

For SQLite, enable foreign-key checks on the connection before creating or using these tables:

PRAGMA foreign_keys = ON;

Identify each row with a primary key

PRIMARY KEY identifies a row. Its values must be unique and cannot be NULL. Here, customer_id identifies each customer, and order_id identifies each order. Choose a key that identifies one record; customer names are not suitable because different people can share a name.

For example, this duplicate customer ID is rejected because customer 1 already exists:

INSERT INTO customers (customer_id, customer_name, email)
VALUES (1, 'Another Asha', 'another-asha@example.test');

Use NOT NULL and UNIQUE for required values

NOT NULL requires a value in the column. UNIQUE prevents the same value appearing in more than one row. The schema requires a customer name and email and makes each email unique.

-- Rejected: customer_name and email are required.
INSERT INTO customers (customer_id, customer_name, email)
VALUES (4, NULL, 'dara@example.test');

-- Rejected: that email address already belongs to customer 1.
INSERT INTO customers (customer_id, customer_name, email)
VALUES (4, 'Dara', 'asha@example.test');

A UNIQUE constraint is appropriate for a value that should identify one account, such as an email address under the application's rules. It is not a good reason to make a person's name unique.

Connect tables with a foreign key

The orders.customer_id foreign key must refer to an existing customers.customer_id. It prevents an order from pointing at a customer that does not exist:

-- Rejected: customer 99 does not exist.
INSERT INTO orders (order_id, customer_id, amount)
VALUES (104, 99, 50.00);

Ben can have no orders; that is different from an order containing an invalid customer ID. A foreign key does not require every customer to have an order. It ensures that each order's customer reference, when present, points to an existing customer.

Use CHECK to restrict allowed values

The CHECK (amount >= 0) constraint rejects a negative amount. A rule like this can catch mistakes before they become part of the stored data:

-- Rejected: the amount is negative.
INSERT INTO orders (order_id, customer_id, amount)
VALUES (104, 1, -5.00);

Constraints should reflect real business rules. For instance, whether a zero order amount is valid depends on the application; the current check intentionally allows zero.

Expected valid data

The valid inserts in the setup produce the same customer and order rows as the earlier lessons, with email addresses added to customers. The constraints do not change the result of a normal join:

SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Try it yourself

Which constraints would help ensure that an order always has an ID, refers to a real customer, and cannot have a negative amount? Then decide whether customer names should be unique.

Key idea

Constraints make important data rules part of the database: keys identify and connect rows, NOT NULL requires a value, UNIQUE prevents duplicate values, and CHECK limits allowed values. They complement application validation rather than replacing helpful user-facing error messages.