Constraints in SQL

Constraints are rules enforced at the database level that govern what data can be stored in a table. Rather than relying on application code to validate every piece of incoming data, constraints allow the database engine itself to reject any data that violates defined rules, making your data inherently more reliable. SQL supports several types of constraints, each addressing a different aspect of data integrity: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and DEFAULT. Understanding each one in depth — and knowing how to apply them correctly when creating tables — is fundamental to good relational database design.

Before diving into individual constraint types, it helps to understand the two placement styles available when defining constraints. An inline (column-level) constraint is written directly after the column's data type declaration. A table-level constraint is written after all column definitions, separated by commas. Some constraints, such as composite primary keys, can only be expressed at the table level. Both styles can optionally use the CONSTRAINT keyword to assign the constraint a name, which is strongly recommended for maintainability.

The PRIMARY KEY constraint is the cornerstone of relational table design. Every table should have a primary key — a column or combination of columns whose value uniquely identifies each row. The primary key automatically enforces two guarantees simultaneously: the column must be UNIQUE (no two rows can share the same value) and NOT NULL (every row must supply a value). You do not need to write UNIQUE or NOT NULL separately for a primary key column; those rules are implied.

In the simplest case, a primary key is a single column, often an integer or a universally unique identifier (UUID):

CREATE TABLE students (
    student_id   INT          CONSTRAINT pk_students PRIMARY KEY,
    full_name    VARCHAR(100) NOT NULL,
    email        VARCHAR(150)
);

Here student_id is declared inline with a named constraint pk_students. When you need a composite primary key — a primary key made up of two or more columns — the constraint must be written at the table level, because it spans more than one column:

CREATE TABLE course_enrollments (
    student_id  INT  NOT NULL,
    course_id   INT  NOT NULL,
    enrolled_on DATE NOT NULL,
    CONSTRAINT pk_enrollments PRIMARY KEY (student_id, course_id)
);

In this design, neither student_id alone nor course_id alone needs to be unique; only the combination of both columns must be unique across all rows. This is the correct model for a many-to-many relationship junction table. Primary keys are also the anchors for relational links: other tables reference a primary key through a foreign key, making the primary key central to the entire relational model.

The FOREIGN KEY constraint creates a formal link between two tables, enforcing referential integrity. The table containing the foreign key is called the child table; the table being referenced is the parent table. The foreign key column in the child table must only ever contain values that already exist in the referenced primary key column of the parent table — or NULL, if the column permits it.

Consider adding enrollment records that reference the students table:

CREATE TABLE courses (
    course_id   INT          CONSTRAINT pk_courses PRIMARY KEY,
    course_name VARCHAR(200) NOT NULL
);

CREATE TABLE course_enrollments (
    enrollment_id INT  CONSTRAINT pk_enroll PRIMARY KEY,
    student_id    INT  NOT NULL,
    course_id     INT  NOT NULL,
    enrolled_on   DATE NOT NULL,
    CONSTRAINT fk_enroll_student FOREIGN KEY (student_id)
        REFERENCES students (student_id),
    CONSTRAINT fk_enroll_course  FOREIGN KEY (course_id)
        REFERENCES courses (course_id)
);

If you attempt to insert a row into course_enrollments with a student_id value of 999 but no student with that ID exists in the students table, the database will immediately raise a referential integrity error and reject the insert. This protection works in the other direction too: you cannot simply delete a parent row that is still being referenced by a child row, unless you have configured a deletion action.

Foreign keys support ON DELETE and ON UPDATE actions that tell the database what to do to child rows when a parent row is deleted or its key value is updated:

An example using CASCADE on delete:

CONSTRAINT fk_enroll_student FOREIGN KEY (student_id)
    REFERENCES students (student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

With ON DELETE CASCADE, deleting a student automatically removes all of that student's enrollment rows. This is convenient but must be chosen carefully — cascading deletes can remove large amounts of data silently. SET NULL is often a safer choice when you want to keep the child rows but simply clear the reference.

The NOT NULL constraint is conceptually the simplest but one of the most frequently applied. By default, any column in SQL accepts NULL — a special marker meaning "no value present." NOT NULL overrides this default and requires that every inserted or updated row must supply an actual value for that column.

NULL is not a value; it is the absence of a value. This distinction matters because comparing NULL with anything (even another NULL) using = does not return TRUE — it returns NULL (unknown). Allowing NULL in columns that must always have data leads to complicated query logic and unreliable aggregations. The NOT NULL constraint prevents this at the source.

CREATE TABLE employees (
    employee_id   INT          CONSTRAINT pk_emp PRIMARY KEY,
    first_name    VARCHAR(80)  NOT NULL,
    last_name     VARCHAR(80)  NOT NULL,
    hire_date     DATE         NOT NULL,
    middle_name   VARCHAR(80)           -- NULL allowed: middle name is optional
);

Here first_name, last_name, and hire_date are essential for business operations — no employee record makes sense without them — so NOT NULL is applied. middle_name is optional, so it is left nullable. This approach is common: NOT NULL is applied wherever a missing value would make a record meaningless or break application logic. Columns such as order totals, transaction dates, product codes, and user names are typical candidates.

The UNIQUE constraint guarantees that no two rows in a table share the same value in the constrained column (or combination of columns). It differs from PRIMARY KEY in two important ways: a table may have multiple UNIQUE constraints on different columns, and a UNIQUE column generally permits NULL values (though the treatment of multiple NULLs varies by database system — some allow many NULLs while others allow only one).

CREATE TABLE users (
    user_id      INT          CONSTRAINT pk_users  PRIMARY KEY,
    username     VARCHAR(50)  NOT NULL CONSTRAINT uq_username UNIQUE,
    email        VARCHAR(150) NOT NULL CONSTRAINT uq_email    UNIQUE,
    phone_number VARCHAR(20)  CONSTRAINT uq_phone UNIQUE  -- nullable but unique if provided
);

In this table, username and email must both be distinct across all users — two users cannot share the same email address even though email is not the primary key. phone_number is unique when provided but can be NULL for users who did not supply one. UNIQUE constraints are the right tool for natural keys or business identifiers that are not the primary key: email addresses, national ID numbers, social security numbers, product barcodes, and similar values.

A composite UNIQUE constraint works the same way as a composite primary key — the combination of values must be unique, not the individual column values:

CREATE TABLE project_assignments (
    employee_id INT NOT NULL,
    project_id  INT NOT NULL,
    role        VARCHAR(50),
    CONSTRAINT uq_emp_project UNIQUE (employee_id, project_id)
);

This allows the same employee to appear on multiple projects and the same project to have multiple employees, but prevents the same employee from being assigned to the same project more than once.

The DEFAULT constraint assigns a predetermined value to a column whenever an INSERT statement omits that column entirely. It does not prevent NULL from being inserted explicitly — if a statement supplies NULL for that column, the database stores NULL, not the default. The default only activates when the column is completely absent from the INSERT column list.

CREATE TABLE orders (
    order_id     INT          CONSTRAINT pk_orders PRIMARY KEY,
    customer_id  INT          NOT NULL,
    order_date   DATE         NOT NULL DEFAULT CURRENT_DATE,
    status       VARCHAR(20)  NOT NULL DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

With this definition, an insert that omits order_date, status, and total_amount will automatically store today's date, the string 'pending', and 0.00 respectively:

-- Only customer_id and order_id are supplied; defaults fill the rest
INSERT INTO orders (order_id, customer_id)
VALUES (1001, 42);

Default values can be:

• Literal numbers — e.g., DEFAULT 0, DEFAULT 100
• Literal strings — e.g., DEFAULT 'active', DEFAULT 'N/A'
• Date/time functions — e.g., DEFAULT CURRENT_DATE, DEFAULT CURRENT_TIMESTAMP
• Boolean literals — e.g., DEFAULT TRUE or DEFAULT FALSE in systems that support a boolean type

DEFAULT constraints reduce the burden on application developers by moving standard, predictable values into the schema itself. A newly created order is almost always in a pending state; an audit timestamp should almost always record now. Encoding these facts in the database schema means they hold true regardless of which application or tool performs the insert.

When it comes to applying constraints during table creation, the placement and naming of constraints deserve deliberate attention. The two placement options — inline and table-level — each have appropriate use cases:

• Inline constraints are written immediately after the column data type. They are concise and readable for single-column constraints such as NOT NULL, UNIQUE, or a single-column PRIMARY KEY.
• Table-level constraints are written after all column definitions, separated by commas. They are required for composite primary keys and composite foreign keys. They are also a clean way to gather all integrity rules in one place for readability.

Naming constraints explicitly with the CONSTRAINT keyword is a professional practice. When a constraint is violated, the database error message includes the constraint name — a name like fk_enroll_student is far more informative than a system-generated name like SYS_C0012345. Named constraints are also easy to target when modifying the schema:

-- Dropping a named constraint is straightforward
ALTER TABLE course_enrollments
DROP CONSTRAINT fk_enroll_student;

-- Adding a new constraint by name
ALTER TABLE course_enrollments
ADD CONSTRAINT fk_enroll_student FOREIGN KEY (student_id)
    REFERENCES students (student_id)
    ON DELETE SET NULL;

Combining multiple constraints on a single column is both common and encouraged. For instance, a column intended to store a unique, mandatory business identifier should carry both NOT NULL and UNIQUE:

CREATE TABLE products (
    product_id  INT           CONSTRAINT pk_products PRIMARY KEY,
    sku         VARCHAR(30)   NOT NULL CONSTRAINT uq_sku UNIQUE,
    product_name VARCHAR(200) NOT NULL,
    price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Here sku is both NOT NULL (every product must have a SKU) and UNIQUE (no two products can share a SKU). The combination delivers a much stronger integrity guarantee than either constraint alone. The following table summarises the key properties of each constraint type for quick comparison:

Together, these five constraint types form the foundation of declarative data integrity in relational databases. Rather than writing validation logic repeatedly in every application that touches the database, constraints allow you to define the rules once, inside the schema, where they apply universally and automatically. A well-constrained schema catches bad data at the earliest possible moment — the instant an INSERT or UPDATE is attempted — and provides clear, named feedback about what rule was violated and why.