SQL Database Objects

SQL Constraints

Enforce data integrity — rules the database guarantees, no matter what your application does.

NOT NULL — A column must always have a value
UNIQUE — All values in a column must be different
PRIMARY KEY — Uniquely identifies each row (NOT NULL + UNIQUE)
FOREIGN KEY — Ensures relationships between tables stay valid
CHECK — Validates that values meet a specific condition Constraints are your last line of defense — they catch bad data that application code lets through.

Why Constraints Matter

DfData Integrity

Data integrity means your data is accurate, consistent, and trustworthy. Constraints enforce rules at the database level so that invalid data cannot be inserted — regardless of which application, user, or script modifies the database.

Without Constraints	With Constraints
Duplicate emails in users table	UNIQUE index rejects duplicates
Orders reference nonexistent customers	FOREIGN KEY prevents orphaned rows
Negative salaries inserted	CHECK constraint validates ranges
Required fields left blank	NOT NULL prevents missing values
Application bug inserts garbage	Database rejects invalid data

NOT NULL

DfNOT NULL Constraint

A NOT NULL constraint ensures a column cannot contain NULL values. Every row must have a value for this column. Applied at the column level during table creation.

-- Column-level NOT NULL constraint
CREATE TABLE employees (
    employee_id   INTEGER     NOT NULL,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100) NOT NULL,
    phone         VARCHAR(20)  -- NULL allowed (optional)
);

-- Attempting to insert NULL into a NOT NULL column fails
INSERT INTO employees (employee_id, first_name, last_name, email, phone)
VALUES (1, 'John', NULL, 'john@example.com', '555-0100');
-- ERROR: null value in column "last_name" violates NOT-null constraint

UNIQUE

DfUNIQUE Constraint

A UNIQUE constraint ensures all values in a column (or combination of columns) are different. A UNIQUE constraint automatically creates an index. Only one NULL is allowed per UNIQUE column (in most databases).

-- Single-column UNIQUE constraint
CREATE TABLE users (
    user_id    INTEGER      NOT NULL,
    email      VARCHAR(100) NOT NULL UNIQUE,
    username   VARCHAR(50)  NOT NULL UNIQUE,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

-- Multi-column UNIQUE constraint (composite)
CREATE TABLE course_enrollments (
    enrollment_id INTEGER NOT NULL,
    student_id    INTEGER NOT NULL,
    course_id     INTEGER NOT NULL,
    UNIQUE (student_id, course_id)  -- A student can't enroll in the same course twice
);

PRIMARY KEY

DfPRIMARY KEY

A PRIMARY KEY uniquely identifies each row in a table. It combines NOT NULL and UNIQUE — no NULLs allowed, and all values must be distinct. A table can have only one PRIMARY KEY.

-- Single-column primary key
CREATE TABLE departments (
    department_id   INTEGER      PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

-- Composite primary key
CREATE TABLE order_items (
    order_id    INTEGER NOT NULL,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL DEFAULT 1,
    unit_price  DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Table-level constraint (same result as above)
CREATE TABLE employees (
    employee_id INTEGER      NOT NULL,
    first_name  VARCHAR(50)  NOT NULL,
    last_name   VARCHAR(50)  NOT NULL,
    email       VARCHAR(100) NOT NULL,
    CONSTRAINT pk_employees PRIMARY KEY (employee_id)
);

FOREIGN KEY

DfFOREIGN KEY

A FOREIGN KEY establishes a link between two tables. It references the PRIMARY KEY (or UNIQUE key) of another table and ensures that values in the foreign key column must exist in the referenced table — or be NULL.

-- Foreign key referencing another table
CREATE TABLE orders (
    order_id      INTEGER      PRIMARY KEY,
    customer_id   INTEGER      NOT NULL,
    order_date    DATE         NOT NULL,
    total_amount  DECIMAL(10,2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

-- Foreign key with ON DELETE behavior
CREATE TABLE order_items (
    item_id     INTEGER      PRIMARY KEY,
    order_id    INTEGER      NOT NULL,
    product_id  INTEGER      NOT NULL,
    quantity    INTEGER      NOT NULL,
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE RESTRICT
);

Referential Action	Behavior
`CASCADE`	Delete/update child rows when parent is deleted/updated
`SET NULL`	Set foreign key to NULL when parent is deleted
`SET DEFAULT`	Set foreign key to its default value
`RESTRICT`	Reject the delete/update if children exist
`NO ACTION`	Same as RESTRICT in most databases (deferred check)

CHECK

DfCHECK Constraint

A CHECK constraint validates that values in a column satisfy a boolean expression. If the condition evaluates to FALSE, the row is rejected. NULL values pass CHECK constraints (NULL is not FALSE).

-- Column-level CHECK constraint
CREATE TABLE employees (
    employee_id INTEGER      PRIMARY KEY,
    first_name  VARCHAR(50)  NOT NULL,
    salary      DECIMAL(10,2) CHECK (salary > 0),
    age         INTEGER      CHECK (age >= 18)
);

-- Named CHECK constraint
CREATE TABLE products (
    product_id   INTEGER       PRIMARY KEY,
    product_name VARCHAR(100)  NOT NULL,
    price        DECIMAL(10,2) NOT NULL,
    weight_kg    DECIMAL(8,2),
    status       VARCHAR(20)   NOT NULL,
    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_weight_positive CHECK (weight_kg > 0),
    CONSTRAINT chk_status_valid CHECK (status IN ('Active', 'Discontinued', 'Pending'))
);

-- Multi-column CHECK constraint
CREATE TABLE reservations (
    reservation_id INTEGER PRIMARY KEY,
    start_date     DATE NOT NULL,
    end_date       DATE NOT NULL,
    CONSTRAINT chk_dates_valid CHECK (end_date >= start_date)
);

DEFAULT

DfDEFAULT Value

A DEFAULT value is automatically assigned to a column when no value is provided during INSERT. While not a constraint in the strictest sense, it works alongside constraints to ensure data completeness.

CREATE TABLE tickets (
    ticket_id   INTEGER      PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'Open',
    priority    VARCHAR(10)  NOT NULL DEFAULT 'Medium',
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    created_by  INTEGER      NOT NULL
);

Adding Constraints to Existing Tables

-- Add NOT NULL constraint
ALTER TABLE employees
ALTER COLUMN phone SET NOT NULL;

-- Add UNIQUE constraint
ALTER TABLE employees
ADD CONSTRAINT uq_employees_email UNIQUE (email);

-- Add CHECK constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);

-- Add FOREIGN KEY constraint
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Drop a constraint
ALTER TABLE employees
DROP CONSTRAINT chk_salary_positive;

Constraint Summary Table

Constraint	Purpose	NULL Allowed?	Multiple Per Table?
NOT NULL	Prevents NULL values	N/A (enforces non-NULL)	✅ (all columns)
UNIQUE	Ensures all values are different	Yes (one NULL)	✅
PRIMARY KEY	Uniquely identifies each row	❌ No	❌ One only
FOREIGN KEY	Ensures valid references	Yes	✅
CHECK	Validates a condition	Yes (NULL passes)	✅
DEFAULT	Provides a fallback value	N/A (used on INSERT)	✅

Best Practices

Practice	Why
Always define a PRIMARY KEY	Every table needs a reliable row identifier
Add NOT NULL to required fields	Prevent incomplete data at the source
Use CHECK constraints for business rules	Enforce ranges, enums, and logic in the database
Name your constraints	Makes error messages readable and debugging easier
Use FOREIGN KEY with ON DELETE behavior	Prevent orphaned rows and undefined behavior
Index foreign key columns	Improves JOIN performance and referential checks
Don't rely solely on application validation	Application code can be bypassed — constraints are the safety net

Name your constraints explicitly (e.g., CONSTRAINT chk_salary_positive). When a constraint is violated, the database reports the constraint name — making debugging trivial.

Key Takeaways

Constraints enforce data integrity at the database level — they work regardless of which application accesses the data
PRIMARY KEY = NOT NULL + UNIQUE — every table should have one
FOREIGN KEY ensures relationships between tables remain valid with configurable ON DELETE behavior
CHECK constraints validate business rules (ranges, enums, multi-column logic) directly in the database
Always name your constraints for readable error messages and easier maintenance

SQL Constraints

SQL Constraints

Why Constraints Matter

DfData Integrity

NOT NULL

DfNOT NULL Constraint

UNIQUE

DfUNIQUE Constraint

PRIMARY KEY

DfPRIMARY KEY

FOREIGN KEY

DfFOREIGN KEY

CHECK

DfCHECK Constraint

DEFAULT

DfDEFAULT Value

Adding Constraints to Existing Tables

Constraint Summary Table

Best Practices

Key Takeaways

Premium Content

Need Expert SQL Help?