SQL Constraints

Overview

SQL Constraints are rules enforced on data columns to limit the type of data that can go into a table. Constraints ensure data integrity and accuracy in your database.

Constraints can be specified when a table is created (with CREATE TABLE) or after the table is created (with ALTER TABLE). They help maintain data quality and prevent invalid data from entering the database.

Types of Constraints

SQL supports several types of constraints:

NOT NULL Constraint

The NOT NULL constraint ensures a column cannot have NULL values:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)  -- Can be NULL
);

UNIQUE Constraint

The UNIQUE constraint ensures all values in a column are different:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE
);

CHECK Constraint

The CHECK constraint validates values in a column:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INT CHECK (stock_quantity >= 0)
);

DEFAULT Constraint

The DEFAULT constraint sets a default value for a column when no value is specified:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'Pending',
    total_amount DECIMAL(10, 2) DEFAULT 0.00
);

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each row (covered in detail in PRIMARY KEY tutorial):

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

FOREIGN KEY Constraint

The FOREIGN KEY constraint links tables together (covered in detail in FOREIGN KEY tutorial):

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Combining Constraints

You can use multiple constraints on a single column:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL UNIQUE,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    category VARCHAR(50) DEFAULT 'General',
    created_date DATE DEFAULT CURRENT_DATE
);

Adding Constraints After Table Creation

Constraints can be added using ALTER TABLE:

-- Add NOT NULL
ALTER TABLE Customers
MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- Add UNIQUE
ALTER TABLE Customers
ADD UNIQUE (email);

-- Add CHECK
ALTER TABLE Products
ADD CHECK (price > 0);

-- Add DEFAULT
ALTER TABLE Orders
ALTER COLUMN status SET DEFAULT 'Pending';

Dropping Constraints

Constraints can be removed using ALTER TABLE:

-- Remove NOT NULL (varies by database)
ALTER TABLE Customers
MODIFY COLUMN email VARCHAR(100) NULL;

-- Remove UNIQUE
ALTER TABLE Customers
DROP INDEX email;

-- Remove CHECK (syntax varies)
ALTER TABLE Products
DROP CHECK chk_price;

Benefits of Constraints

• Data integrity: Ensures accurate and consistent data
• Prevents errors: Stops invalid data from entering database
• Enforces business rules: Implements logical rules at database level
• Improves reliability: Database ensures data quality
• Documentation: Constraints document data requirements

Best Practices

• Use PRIMARY KEY: Every table should have a primary key
• Set NOT NULL: For required fields
• Use FOREIGN KEY: Maintain referential integrity
• Add CHECK constraints: Validate data at database level
• Set appropriate defaults: Provide sensible default values
• Document constraints: Make rules clear

Next Steps

Learn about specific constraints: PRIMARY KEY and FOREIGN KEY.