SQL - Constraints

SQL Constraints

What Are SQL Constraints?

SQL constraints are rules enforced on data columns or tables to ensure data accuracy, reliability, and integrity. They prevent invalid data from being entered into the database. By defining constraints, you enforce your application’s business rules at the database level, which is a crucial aspect of robust system design.

Constraints can be defined at the column level (part of a column definition) or at the table level (separate from column definitions). Table-level constraints are necessary when a constraint involves multiple columns (e.g., a composite primary key).

Creating Constraints

Constraints are typically defined when a table is created using the CREATE TABLE statement. They can also be added to an existing table using the ALTER TABLE statement.

-- General syntax for creating a table with constraints
CREATE TABLE table_name (
    column1 datatype column_level_constraint,
    column2 datatype,
    ...,
    table_level_constraint(column2, ...)
);

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value. This is used for fields that must always contain a value, like a user’s name or a product’s price.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL
);

-- This will FAIL because `last_name` is NOT NULL
INSERT INTO employees (employee_id, first_name, hire_date) VALUES (1, 'John', '2023-01-15');

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or a set of columns) are different from one another. Unlike a PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns may accept a single NULL value in most database systems.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT uq_username UNIQUE (username),
    CONSTRAINT uq_email UNIQUE (email)
);

-- This will SUCCEED
INSERT INTO users VALUES (1, 'jdoe', 'john.doe@example.com');

-- This will FAIL because the email 'john.doe@example.com' is already in use.
INSERT INTO users VALUES (2, 'asmith', 'john.doe@example.com');

PRIMARY KEY Constraint

A PRIMARY KEY is a special constraint that uniquely identifies each record in a table. It is effectively a combination of NOT NULL and UNIQUE. A table can have only one PRIMARY KEY, which can consist of a single column or multiple columns (a composite key).

CREATE TABLE products (
    product_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    -- Table-level primary key definition
    PRIMARY KEY(product_id)
);

FOREIGN KEY Constraint (Referential Integrity)

A FOREIGN KEY creates a link between two tables, enforcing referential integrity. It ensures that a value in a column (or a group of columns) in one table must match a value in the PRIMARY KEY of another table. This prevents “orphan” records, such as an order that doesn’t belong to any customer.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date TIMESTAMP NOT NULL,
    customer_id INT NOT NULL,
    -- Foreign key linking orders to customers
    CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE RESTRICT -- Prevents deleting a customer who has orders
);

-- This will FAIL if a customer with ID 999 does not exist.
INSERT INTO orders VALUES (101, NOW(), 999);

The ON DELETE and ON UPDATE clauses define what happens to the child records (in orders) when the parent record (in customers) is deleted or updated. Common options include CASCADE, SET NULL, RESTRICT, and NO ACTION.

CHECK Constraint

The CHECK constraint is used to validate the values being entered into a column. It ensures that the value meets a specific condition. Modern RDBMS like PostgreSQL, SQL Server, and MySQL (version 8.0.16+) fully support CHECK constraints.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock_quantity INT NOT NULL,
    CONSTRAINT chk_stock_positive CHECK (stock_quantity >= 0)
);

-- This will FAIL because the price is not greater than 0.
INSERT INTO products VALUES (1, 'Sample Product', -50.00, 100);

DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when no value is specified during an INSERT operation.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending'
);

-- Inserts an order using default values for order_date and status
INSERT INTO orders (order_id) VALUES (101);
-- The new row will have the current timestamp and 'Pending' status.

The INDEX Pseudo-Constraint

While not a constraint in the sense of restricting data, an INDEX is a special lookup table that the database search engine can use to speed up data retrieval. Creating an index on a column significantly improves the performance of SELECT queries with WHERE clauses. However, it slows down data modification (INSERT, UPDATE, DELETE) because the index must also be updated.

-- Create an index on the last_name column of the employees table
CREATE INDEX idx_employees_lastname ON employees(last_name);

-- This query will now be much faster on a large table.
SELECT * FROM employees WHERE last_name = 'Smith';

Modifying and Dropping Constraints

You can drop an existing constraint using the ALTER TABLE command. You typically need to know the constraint’s name, which is why naming your constraints explicitly (e.g., CONSTRAINT fk_orders_customers ...) is a best practice.

-- Dropping a foreign key constraint from the ORDERS table
ALTER TABLE orders DROP CONSTRAINT fk_orders_customers;

-- Dropping a primary key constraint (syntax may vary by RDBMS)
ALTER TABLE products DROP PRIMARY KEY;

Data Integrity Best Practices

Enforcing data integrity at the database layer is fundamental to building reliable applications. This concept is closely related to the ACID properties (Atomicity, Consistency, Isolation, Durability) that guarantee reliable transaction processing.