SQL Constraints

Learn how SQL constraints protect data integrity with NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT rules.

SQL constraints are rules enforced by a database on table columns or combinations of columns. They restrict invalid INSERT and UPDATE operations, helping keep data accurate, complete, unique, and correctly related across tables.

Constraints support several forms of data integrity:

Data quality: values follow the rules expected by the application.
Entity integrity: every row has a reliable, unique identifier.
Uniqueness: duplicate values are prevented where they are not allowed.
Referential integrity: relationships between related tables remain valid.

Constraint types at a glance

Constraint	What it enforces	Typical use	Can use multiple columns
`NOT NULL`	A column must contain a non-`NULL` value.	Required attributes such as a name or creation date.	Indirectly; apply it separately to columns.
`UNIQUE`	Values, or value combinations, cannot be duplicated.	Email addresses or repeated business-key combinations.	Yes.
`PRIMARY KEY`	Each row has one unique, non-`NULL` identifier.	Identifying a customer, product, or enrollment.	Yes; this is a composite primary key.
`FOREIGN KEY`	Values must refer to a key in another table.	Connecting orders to customers.	Yes.
`CHECK`	A value or row must satisfy a Boolean condition.	Nonnegative quantities or allowed statuses.	Yes.
`DEFAULT`	A value is supplied when an inserted column is omitted.	Initial status or creation timestamp.	Apply it to individual columns.

Defining constraints in CREATE TABLE

CREATE TABLE defines a table's columns, data types, and constraints. A general structure is:

CREATE TABLE table_name (
    column_name data_type column_constraint,
    another_column data_type,
    table_constraint
);

A column-level constraint appears beside one column definition:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    display_name VARCHAR(100) NOT NULL
);

A table-level constraint is written separately inside the table definition. It is especially useful for composite keys and composite uniqueness rules:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    CONSTRAINT pk_enrollments PRIMARY KEY (student_id, course_id),
    CONSTRAINT uq_student_course UNIQUE (student_id, course_id)
);

A named constraint uses the pattern CONSTRAINT constraint_name constraint_definition. Names such as pk_enrollments and uq_users_email make error messages, schema documentation, and later changes easier to understand. Exact identifier rules and automatic naming behavior vary by database system.

NOT NULL

NOT NULL requires a column to receive a non-NULL value. NULL represents an unknown, missing, or inapplicable value; it is not the same as an empty string such as '', and it is not the same as zero. Whether empty strings receive special treatment can vary by database system, but NOT NULL specifically concerns NULL.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    display_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL
);

An insert that omits display_name, or explicitly supplies NULL, fails unless another applicable rule supplies a value.

UNIQUE

UNIQUE prevents duplicate values in a column or duplicate combinations of values. A single-column rule is useful for an email address:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT uq_users_email UNIQUE (email)
);

A multi-column, or composite, UNIQUE rule checks the combination rather than each column independently:

CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    CONSTRAINT uq_enrollment_pair UNIQUE (student_id, course_id)
);

This allows a student to enroll in many courses and a course to have many students, but prevents the same student-and-course pair from appearing twice. The treatment of NULL values in a UNIQUE constraint differs among SQL products, so check your database documentation when nullable unique columns matter.

PRIMARY KEY

A primary key is the column or set of columns that uniquely identifies each row. Primary-key values must be unique and cannot be NULL. A table has one primary-key definition, although that definition may contain multiple columns.

A single-column primary key is commonly used for a surrogate identifier:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL
);

A composite primary key identifies a row by the combination of two or more columns:

CREATE TABLE course_sections (
    course_id INTEGER NOT NULL,
    section_code VARCHAR(20) NOT NULL,
    CONSTRAINT pk_course_sections PRIMARY KEY (course_id, section_code)
);

PRIMARY KEY versus UNIQUE

Property	`PRIMARY KEY`	`UNIQUE`
Uniqueness	Required.	Required.
`NULL` handling	Not allowed.	Database-specific behavior; nullable columns may permit one or more `NULL` values.
Number allowed per table	One primary-key definition.	Multiple unique constraints are allowed.
Typical purpose	Main identity of each row.	An alternate key or business rule.
Multi-column usage	Yes, as a composite key.	Yes, as a composite unique rule.

FOREIGN KEY and referential integrity

A foreign key is a column or set of columns whose values refer to a primary key or unique key in another table. The referenced table is the parent table; the table containing the foreign key is the child table.

Referential integrity requires relationships between these tables to remain valid. In the example below, every order must refer to an existing customer.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(200) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
);

An order with customer_id = 42 cannot be inserted unless the parent table contains customer 42. The data types and referenced columns must be compatible, and the referenced columns generally need to be a primary key or a supported unique key.

Foreign-key referential actions

When a referenced parent row is updated or deleted, a foreign key can specify a referential action. Product support and exact behavior vary, so verify the syntax for your database.

Action	Effect when the referenced row changes or is deleted	Suitable use case
`RESTRICT` or `NO ACTION`	Rejects the operation if dependent child rows exist. Timing can differ by product.	Protecting important historical records.
`CASCADE`	Propagates the update or deletion to matching child rows.	Dependent data that should never outlive its parent.
`SET NULL`	Sets the child foreign-key columns to `NULL`.	Optional relationships where the child row should remain.
`SET DEFAULT`	Sets the child columns to their defined defaults, where supported.	Relationships with a meaningful fallback parent.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

SET NULL requires the foreign-key column to allow NULL. Cascading actions should be chosen carefully because one deletion can affect many rows.

CHECK

CHECK requires a value or row to satisfy a Boolean condition. Common conditions use comparisons, ranges, and allowed values.

CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL,
    rating INTEGER,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    CONSTRAINT ck_inventory_quantity CHECK (quantity >= 0),
    CONSTRAINT ck_inventory_rating CHECK (rating BETWEEN 1 AND 5),
    CONSTRAINT ck_inventory_status CHECK (status IN ('active', 'discontinued'))
);

A table-level condition can involve more than one column:

CREATE TABLE bookings (
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CONSTRAINT ck_booking_dates CHECK (end_date >= start_date)
);

SQL uses three-valued logic: a condition can be true, false, or unknown. Because NULL can make an expression unknown, the result of a CHECK involving nullable columns can be database-specific or surprising. Add NOT NULL when a value is required, or include an explicit IS NULL or IS NOT NULL condition when the rule needs to control nullability.

DEFAULT

DEFAULT supplies a value when an INSERT omits the column.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_on DATE DEFAULT CURRENT_DATE
);

Literal defaults include numbers, text, and dates. Many databases also support expression-based defaults such as the current date or timestamp, but syntax and supported expressions differ.

Omitting a column invokes its default:

INSERT INTO orders (order_id)
VALUES (1001);

Explicitly inserting NULL is different. It normally requests NULL rather than the default, and it fails if the column also has NOT NULL. Some database systems provide a database-specific DEFAULT expression for explicitly requesting the default.

Constraint violations during INSERT and UPDATE

Constraints are enforced for both new rows and changes to existing rows. A statement that violates a constraint normally fails, leaving the invalid change unapplied. Exact error text varies by database.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    display_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT uq_users_email UNIQUE (email)
);

INSERT INTO users (user_id, display_name, email)
VALUES (1, 'Mina', 'mina@example.com');

-- Fails: display_name is omitted and is NOT NULL.
INSERT INTO users (user_id, email)
VALUES (2, 'new@example.com');

-- Fails: email duplicates the existing unique value.
INSERT INTO users (user_id, display_name, email)
VALUES (2, 'Another name', 'mina@example.com');

-- Fails: the updated email would duplicate another row.
UPDATE users
SET email = 'mina@example.com'
WHERE user_id = 3;

Other common failures include a negative quantity rejected by CHECK, an order referencing a nonexistent customer rejected by FOREIGN KEY, and a duplicate identifier rejected by PRIMARY KEY. These failures are useful protections: they stop inconsistent data from entering the table.

Constraint troubleshooting

Required column has no value: A NOT NULL rule was violated. Supply a non-NULL value, make the attribute optional only if that is correct, or define an appropriate default.
Value already exists: A UNIQUE or PRIMARY KEY rule was violated. Choose a new value, correct duplicate source data, or review whether the uniqueness rule should cover a different column set.
Order cannot reference a customer: The foreign-key value has no matching parent key. Insert the parent first, use an existing key, and check the data types and referenced columns.
Negative quantity or unsupported status is rejected: The value does not satisfy a CHECK expression. Correct the value or revise the condition if the business rule is wrong.
Default did not appear: The insert probably supplied NULL or another value instead of omitting the column. Omit the column or use supported DEFAULT syntax.

Removing tables and constraint effects

DROP TABLE removes a table definition and all data stored in that table:

DROP TABLE orders;

Warning: DROP TABLE is destructive. It removes data as well as columns, constraints, and other table definitions. Use it only when the table is no longer needed or after confirming that a backup exists.

A table referenced by a foreign key may not be droppable while dependent tables or foreign-key constraints exist. The usual approach is to remove or alter dependent objects first, then drop the parent table. Some database systems support cascading drop behavior, but use it with care because it can remove dependent objects and data.

Practical complete example

The following schema combines required values, identifiers, uniqueness, relationships, checks, defaults, and named constraints:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(200) NOT NULL,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT uq_customers_email UNIQUE (email)
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id),
    CONSTRAINT ck_orders_quantity CHECK (quantity > 0),
    CONSTRAINT ck_orders_status
        CHECK (status IN ('pending', 'paid', 'cancelled'))
);

First insert a customer, then insert an order using that customer's identifier:

INSERT INTO customers (customer_id, customer_name, email)
VALUES (10, 'Ravi Shah', 'ravi@example.com');

INSERT INTO orders (order_id, customer_id, quantity)
VALUES (5001, 10, 2);

The order receives the default pending status. It is accepted because quantity is positive and customer 10 exists. An order with quantity 0, an unsupported status, or an unknown customer identifier is rejected.

Key points

Constraints are database-enforced rules, not merely application suggestions.
NOT NULL controls missing values; it does not reject empty strings or zero by itself.
UNIQUE prevents duplicate values or combinations, while a PRIMARY KEY is the table's unique, non-NULL row identifier.
FOREIGN KEY maintains valid parent-child relationships.
CHECK expresses allowed conditions, and DEFAULT supplies values for omitted insert columns.
Use table-level syntax for composite keys and multi-column uniqueness rules.
Database products differ in areas such as nullable UNIQUE values, check evaluation, default expressions, and foreign-key actions.