🎉 75% of content is free forever — Unlock Premium from $10/mo →
CW
Search courses…
💼 Servicesℹ️ About✉️ ContactView Pricing Plansfrom $10
Get started free →Sign in

SQL Constraints

SQL Database ObjectsDesign🟢 Free Lesson

Advertisement

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

Constraint Types OverviewNOT NULLRequired fieldsNo empty valuesemail VARCHAR NOT NULLUNIQUEDistinct valuesOne NULL allowedemail UNIQUEPRIMARY KEYNOT NULL + UNIQUERow identifierid INT PRIMARY KEYFOREIGN KEYCross-table refsReferential integrityFK → PK linkCHECKCustom validationBoolean expressionCHECK (age >= 18)CONSTRAINT name CHECK (condition) — Name constraints for easy debugging
Without ConstraintsWith Constraints
Duplicate emails in users tableUNIQUE index rejects duplicates
Orders reference nonexistent customersFOREIGN KEY prevents orphaned rows
Negative salaries insertedCHECK constraint validates ranges
Required fields left blankNOT NULL prevents missing values
Application bug inserts garbageDatabase rejects invalid data

NOT NULL

-- 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

-- 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

-- 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

-- 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 ActionBehavior
CASCADEDelete/update child rows when parent is deleted/updated
SET NULLSet foreign key to NULL when parent is deleted
SET DEFAULTSet foreign key to its default value
RESTRICTReject the delete/update if children exist
NO ACTIONSame as RESTRICT in most databases (deferred check)

CHECK

-- 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

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

ConstraintPurposeNULL Allowed?Multiple Per Table?
NOT NULLPrevents NULL valuesN/A (enforces non-NULL)✅ (all columns)
UNIQUEEnsures all values are differentYes (one NULL)✅
PRIMARY KEYUniquely identifies each row❌ No❌ One only
FOREIGN KEYEnsures valid referencesYes✅
CHECKValidates a conditionYes (NULL passes)✅
DEFAULTProvides a fallback valueN/A (used on INSERT)✅

Best Practices

PracticeWhy
Always define a PRIMARY KEYEvery table needs a reliable row identifier
Add NOT NULL to required fieldsPrevent incomplete data at the source
Use CHECK constraints for business rulesEnforce ranges, enums, and logic in the database
Name your constraintsMakes error messages readable and debugging easier
Use FOREIGN KEY with ON DELETE behaviorPrevent orphaned rows and undefined behavior
Index foreign key columnsImproves JOIN performance and referential checks
Don't rely solely on application validationApplication code can be bypassed — constraints are the safety net
←38 Indexes40 Alter Table→

Need Expert SQL Help?

Get personalized tutoring, project support, or professional consulting.

Contact Us →View Services

Advertisement