KAHIBARO
Discord Login Register ☰
☰

Constraints

Table of Contents

Introduction

In SQL, constraints are rules that the database uses to protect your data. They help ensure that the data in your tables is valid, consistent, and safe.

Instead of trusting every application to always insert correct data, you tell the database the rules, and it enforces them every time.

Key idea: A constraint is a rule on a table or column that the database enforces automatically to keep data valid.

In this chapter you will see what the main constraint types are, how to define them, and many concrete examples.

Why Constraints Matter

Imagine a users table where:

This kind of data is very hard to work with, and can easily break your application.

Constraints help you avoid these problems by letting the database reject bad data before it is stored.

Typical data problems that constraints prevent:

ProblemConstraint that helps
Missing required valuesNOT NULL
Duplicate valuesUNIQUE, PRIMARY KEY
Wrong data typeColumn data type
Invalid references to other tablesFOREIGN KEY
Values out of allowed rangeCHECK
Completely duplicate rowsPRIMARY KEY, UNIQUE

Types of Constraints

The most common SQL constraints are:

You can define constraints:

Basic forms:

sql 
CREATE TABLE example (
    id          INT PRIMARY KEY,              -- column-level
    email       VARCHAR(255) NOT NULL UNIQUE, -- column-level
    age         INT,
    CONSTRAINT age_positive CHECK (age > 0),  -- table-level
    CONSTRAINT email_unique UNIQUE (email)    -- table-level
);

NOT NULL Constraint

What NOT NULL Does

NOT NULL means the column must always have a value. It cannot be NULL.

Without NOT NULL, a column can be left empty (NULL), even if your application always “tries” to send it.

Rule: A NOT NULL column must have a non-NULL value for every row.

Defining NOT NULL

You typically define NOT NULL when creating a table:

sql 
CREATE TABLE users (
    id        SERIAL PRIMARY KEY,
    email     VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    bio       TEXT          -- bio can be NULL
);

Here:

Trying to insert a NULL into a NOT NULL column fails:

sql 
INSERT INTO users (email, full_name)
VALUES (NULL, 'Alice');

Result: the database raises an error because email is NOT NULL.

Adding NOT NULL Later

If you already have a table, you can add NOT NULL to a column, but only if existing rows do not have NULL in that column.

sql 
ALTER TABLE users
ALTER COLUMN full_name SET NOT NULL;

If some rows already have NULL in full_name, this statement will fail. You must fix the data first:

sql 
UPDATE users
SET full_name = 'Unknown'
WHERE full_name IS NULL;
ALTER TABLE users
ALTER COLUMN full_name SET NOT NULL;

UNIQUE Constraint

What UNIQUE Does

UNIQUE ensures that no two rows in a table have the same value for that column or set of columns.

Typical uses:

Rule: A UNIQUE constraint prevents duplicate non-NULL values.

Important detail: many databases allow multiple NULL values in a UNIQUE column. Check your specific database, but PostgreSQL allows this.

Column-level UNIQUE

sql 
CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    email    VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50)  UNIQUE
);

Behavior:

sql 
INSERT INTO users (email, username) VALUES ('a@example.com', 'alice'); -- OK
INSERT INTO users (email, username) VALUES ('b@example.com', 'alice'); -- ERROR (duplicate username)
INSERT INTO users (email, username) VALUES ('a@example.com', 'bob');   -- ERROR (duplicate email)

Table-level UNIQUE (Composite Unique)

Sometimes you want a combination of columns to be unique, rather than each column alone.

Example: each product can appear only once per order:

sql 
CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    CONSTRAINT order_product_unique
        UNIQUE (order_id, product_id)
);

Now you cannot insert two rows with the same (order_id, product_id) pair.

Example inserts:

sql 
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 10, 2);      -- OK
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 10, 3);      -- ERROR, duplicate (order_id, product_id)
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 11, 1);      -- OK (different product)

Adding UNIQUE Later

You can add a unique constraint after creating the table:

sql 
ALTER TABLE users
ADD CONSTRAINT users_email_unique
UNIQUE (email);

This works only if there are no duplicates already, otherwise the command fails. You must clean duplicates first.

A common cleanup approach:

sql 
-- Example: keep the smallest id for each email, mark duplicates
DELETE FROM users u
USING users u2
WHERE u.email = u2.email
  AND u.id > u2.id;

Then you can add the unique constraint.

PRIMARY KEY Constraint

What PRIMARY KEY Does

A PRIMARY KEY uniquely identifies each row in a table. Every table should have one primary key.

Properties of a primary key:

Rule: A table can have only one PRIMARY KEY, but that key can use multiple columns.

Simple Primary Key

The most common design uses a single auto-incrementing integer:

sql 
CREATE TABLE users (
    id        SERIAL PRIMARY KEY,
    email     VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(255) NOT NULL
);

Here:

Example:

sql 
INSERT INTO users (email, full_name)
VALUES ('alice@example.com', 'Alice');
INSERT INTO users (email, full_name)
VALUES ('bob@example.com', 'Bob');
SELECT * FROM users;

Result might look like:

idemailfull_name
1alice@example.comAlice
2bob@example.comBob

Composite Primary Key

A primary key can also be made from several columns. This is called a composite key.

Example: in a join table between orders and products:

sql 
CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Here, (order_id, product_id) together form the primary key.

Insert examples:

sql 
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 10, 2);  -- OK
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 10, 5);  -- ERROR, duplicate primary key
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 11, 3);  -- OK

Primary Key vs UNIQUE

PRIMARY KEY and UNIQUE both force uniqueness, but they are not identical:

FeaturePRIMARY KEYUNIQUE
Number per tableOnly oneMany allowed
Allows NULLNoUsually yes, often multiple
Main row identifierYesNot necessarily
Often used by foreign keysVery oftenCan be, but primary is default

A common pattern:

FOREIGN KEY Constraint

What FOREIGN KEY Does

A FOREIGN KEY is a constraint that creates a relationship between tables. It ensures that a value in one table exists in another table.

Example: every order must refer to a real user.

Rule: A FOREIGN KEY value must exist in the referenced table, or be NULL if allowed.

Without foreign keys, you can have "orphan" rows, such as orders that reference user IDs that do not exist.

Basic Foreign Key Example

Two tables, users and orders:

sql 
CREATE TABLE users (
    id        SERIAL PRIMARY KEY,
    email     VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE orders (
    id        SERIAL PRIMARY KEY,
    user_id   INT NOT NULL,
    total     NUMERIC(10,2) NOT NULL,
    CONSTRAINT orders_user_fk
        FOREIGN KEY (user_id)
        REFERENCES users (id)
);

Behavior:

sql 
INSERT INTO users (email)
VALUES ('alice@example.com');        -- OK, id = 1
INSERT INTO orders (user_id, total)
VALUES (1, 100.00);                  -- OK, user with id 1 exists
INSERT INTO orders (user_id, total)
VALUES (999, 50.00);                 -- ERROR, no user with id 999

The foreign key stops invalid references.

ON DELETE and ON UPDATE Actions

What happens if a referenced user is deleted? The database needs a rule.

Common actions:

ActionDescription
RESTRICT / NonePrevent delete or update if rows still reference it
CASCADEAutomatically delete or update child rows
SET NULLSet the foreign key column to NULL
SET DEFAULTSet the foreign key column to its default value

Example with ON DELETE CASCADE:

sql 
CREATE TABLE orders (
    id        SERIAL PRIMARY KEY,
    user_id   INT NOT NULL,
    total     NUMERIC(10,2) NOT NULL,
    CONSTRAINT orders_user_fk
        FOREIGN KEY (user_id)
        REFERENCES users (id)
        ON DELETE CASCADE
);

Now:

sql 
DELETE FROM users WHERE id = 1;

This automatically deletes all orders with user_id = 1.

Example with SET NULL:

sql 
CREATE TABLE orders (
    id        SERIAL PRIMARY KEY,
    user_id   INT,
    total     NUMERIC(10,2) NOT NULL,
    CONSTRAINT orders_user_fk
        FOREIGN KEY (user_id)
        REFERENCES users (id)
        ON DELETE SET NULL
);

Now deleting a user with id = 1 will set user_id to NULL for those orders, instead of deleting the orders.

Composite Foreign Keys

You can reference multiple columns if the referenced table has a primary or unique key on those columns.

Example:

sql 
CREATE TABLE products (
    category_id INT,
    code        VARCHAR(20),
    name        VARCHAR(100),
    PRIMARY KEY (category_id, code)
);
CREATE TABLE inventory (
    warehouse_id INT,
    category_id  INT,
    product_code VARCHAR(20),
    quantity     INT NOT NULL,
    PRIMARY KEY (warehouse_id, category_id, product_code),
    FOREIGN KEY (category_id, product_code)
        REFERENCES products (category_id, code)
);

Now each (category_id, product_code) in inventory must exist in products.

CHECK Constraint

What CHECK Does

A CHECK constraint allows you to define a logical condition that values must satisfy.

Examples:

Rule: A CHECK constraint must be TRUE or UNKNOWN (NULL) for every row, otherwise the insert or update is rejected.

If the expression is FALSE, the database rejects the change.

Simple CHECK Examples

Age must be non negative

sql 
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    age   INT,
    CONSTRAINT age_non_negative CHECK (age >= 0)
);

Behavior:

sql 
INSERT INTO users (age) VALUES (25);   -- OK
INSERT INTO users (age) VALUES (0);    -- OK
INSERT INTO users (age) VALUES (-5);   -- ERROR
INSERT INTO users (age) VALUES (NULL); -- OK, condition is UNKNOWN

Note that by default, NULL does not violate the check. If you want to forbid NULL, you must also use NOT NULL.

Allowed status values

sql 
CREATE TABLE orders (
    id     SERIAL PRIMARY KEY,
    status VARCHAR(20) NOT NULL,
    CONSTRAINT order_status_check
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

Now:

sql 
INSERT INTO orders (status) VALUES ('pending');   -- OK
INSERT INTO orders (status) VALUES ('refunded');  -- ERROR

Numeric range

sql 
CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    price NUMERIC(10,2) NOT NULL,
    CONSTRAINT price_positive CHECK (price > 0)
);

Any attempt to insert price <= 0 is rejected.

Table-level CHECK across multiple columns

You can use CHECK with several columns:

sql 
CREATE TABLE bookings (
    id          SERIAL PRIMARY KEY,
    start_date  DATE NOT NULL,
    end_date    DATE NOT NULL,
    CONSTRAINT dates_valid CHECK (end_date > start_date)
);

Behavior:

sql 
INSERT INTO bookings (start_date, end_date)
VALUES ('2024-01-01', '2024-01-10');  -- OK
INSERT INTO bookings (start_date, end_date)
VALUES ('2024-01-10', '2024-01-01');  -- ERROR

DEFAULT Constraint

What DEFAULT Does

DEFAULT is often grouped with constraints. It specifies a value that the database will use if you do not provide one when inserting a row.

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    is_active   BOOLEAN NOT NULL DEFAULT TRUE
);

Now:

sql 
INSERT INTO users DEFAULT VALUES;

You get a row with:

Another example:

sql 
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    stock       INT NOT NULL DEFAULT 0,
    description TEXT
);

Inserts:

sql 
INSERT INTO products (description) VALUES ('Item A'); -- stock = 0 by default
INSERT INTO products (stock, description) VALUES (10, 'Item B'); -- explicit stock

DEFAULT does not stop you from inserting other values. It only fills in missing ones.

Naming and Managing Constraints

Naming Constraints

You can let the database generate constraint names, or you can name them yourself with CONSTRAINT.

Good practice is to give meaningful names:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    age         INT,
    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_age_non_negative CHECK (age >= 0)
);

This makes error messages clearer, and it is easier to drop or modify specific constraints later.

Viewing Constraints

Each database has its own way to view constraints. In PostgreSQL, two common ways:

List table definition:

sql 
\d users   -- in psql

Or query system catalogs:

sql 
SELECT conname, contype
FROM   pg_constraint
WHERE  conrelid = 'users'::regclass;

In other systems, you might use INFORMATION_SCHEMA.TABLE_CONSTRAINTS:

sql 
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'users';

Dropping Constraints

You may sometimes need to remove a constraint. Use ALTER TABLE ... DROP CONSTRAINT.

Example:

sql 
ALTER TABLE users
DROP CONSTRAINT users_email_unique;

If the constraint was unnamed or auto generated, you must first find the system generated name.

For simple NOT NULL:

sql 
ALTER TABLE users
ALTER COLUMN full_name DROP NOT NULL;

Practical Examples in Backend Development

Example: User Table with Strong Constraints

A typical users table:

sql 
CREATE TABLE users (
    id             SERIAL PRIMARY KEY,
    email          VARCHAR(255) NOT NULL,
    password_hash  VARCHAR(255) NOT NULL,
    full_name      VARCHAR(255) NOT NULL,
    is_active      BOOLEAN NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_full_name_not_empty CHECK (full_name <> '')
);

What this protects you from:

Example: Orders and Order Items

A small e-commerce set of tables:

sql 
CREATE TABLE customers (
    id        SERIAL PRIMARY KEY,
    email     VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(255) NOT NULL
);
CREATE TABLE orders (
    id           SERIAL PRIMARY KEY,
    customer_id  INT NOT NULL,
    status       VARCHAR(20) NOT NULL,
    total        NUMERIC(10,2) NOT NULL DEFAULT 0,
    created_at   TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id)
        REFERENCES customers (id)
        ON DELETE RESTRICT,
    CONSTRAINT orders_status_check
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);
CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    price      NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT order_items_order_fk
        FOREIGN KEY (order_id)
        REFERENCES orders (id)
        ON DELETE CASCADE,
    CONSTRAINT order_items_quantity_check
        CHECK (quantity > 0),
    CONSTRAINT order_items_price_positive
        CHECK (price > 0)
);

Highlights:

Design Guidelines and Common Mistakes

Good Practices

Common Mistakes

  1. No foreign keys
    Relying only on application code, which can still insert invalid references.
  2. Too many nullable columns
    Columns that are logically required but are not NOT NULL.
  3. No uniqueness constraints
    Letting duplicate emails, usernames, or identifiers slip into the database.
  4. Overcomplicated CHECKs
    Very complex conditions that are hard to understand or maintain. Keep them clear and simple.
  5. Forgetting about cascading rules
    Accidentally deleting a parent row and being surprised by cascading deletes, or the opposite, not being able to delete because of referencing rows.

Summary

Constraints are a central tool to keep your database correct and safe:

As a backend developer, you should think about constraints every time you design or change a table. They are your first line of defense for data integrity, before your application code even runs.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3104 / 24h
Impressum / Imprint
Datenschutz / Data Policy