SQL constraints define rules for the data in a database to maintain accuracy, consistency, and integrity. They ensure that only valid data is stored, preventing issues like duplicate entries, missing values, or invalid relationships.

How to Use SQL Constraints

SQL constraints are applied at the column or table level when creating or modifying a table. They control the values allowed in a column and enforce relationships between tables.

Syntax for Adding Constraints

When defining constraints in SQL, you can specify them in the CREATE TABLE statement.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    salary DECIMAL(10,2) CHECK (salary > 0),
    department_id INT DEFAULT 1
);

Alternatively, constraints can be added later using ALTER TABLE.

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

When to Use SQL Constraints

SQL constraints help prevent bad data from entering your database. Here are three common use cases.

Enforcing Data Integrity

Constraints protect data consistency by ensuring that only valid entries are accepted. A CHECK constraint, for example, prevents negative salaries.

ALTER TABLE employees ADD CONSTRAINT salary_positive CHECK (salary >= 0);

Preventing Duplicate Records

A UNIQUE constraint ensures that values in a column do not repeat, preventing duplicate email addresses.

ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

Maintaining Relationships Between Tables

A FOREIGN KEY constraint enforces referential integrity, ensuring that records in related tables remain consistent.

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

This prevents orders from referencing non-existent customers.

Examples of SQL Constraints

Defining a Primary Key Constraint

A PRIMARY KEY constraint ensures that every row has a unique identifier.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

This prevents duplicate or null values in the student_id column.

Adding a Unique Constraint

A UNIQUE constraint ensures that values in a column do not repeat.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE
);

Each username must be different, preventing duplicate entries.

Applying a Default Constraint

A DEFAULT constraint assigns a value when none is provided.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_status VARCHAR(20) DEFAULT 'Pending'
);

If no value is provided for order_status, it defaults to "Pending."

Using a Check Constraint

A CHECK constraint enforces specific conditions on column values.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price > 0)
);

Negative prices are not allowed.

Learn More About SQL Constraints

Altering and Dropping Constraints

You can modify or remove constraints using ALTER TABLE.

Adding Constraints

To add a constraint after table creation:

ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

Deleting Constraints

To remove a constraint:

ALTER TABLE employees DROP CONSTRAINT unique_email;

In SQL Server, you must specify the constraint name:

ALTER TABLE employees DROP CONSTRAINT chk_salary;

Composite Constraints

You can apply constraints to multiple columns, such as composite primary keys.

CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

This ensures that a student can only enroll in the same course once.

Constraints and Performance

While constraints improve data integrity, excessive constraints can slow down inserts and updates. Consider indexing heavily constrained columns to maintain performance.