Skip to main content

SQL Constraints

Introduction​

Constraints are rules enforced on data columns in tables to ensure the accuracy and reliability of the data stored in a database. They are an essential part of database design as they help maintain data integrity and prevent invalid data from being entered into the database.

Think of constraints as guardrails that keep your data clean, consistent, and reliable. Without constraints, your database could become filled with inaccurate, duplicate, or inconsistent data, making it difficult to use for any practical purpose.

Types of SQL Constraints​

Let's explore the most common types of SQL constraints:

1. NOT NULL Constraint​

The NOT NULL constraint ensures that a column cannot have a NULL value. A NULL value means "no value" or "unknown value" and is different from zero or an empty string.

Syntax:​

sql
CREATE TABLE table_name (
    column_name data_type NOT NULL,
    ...
);

Example:​

sql
CREATE TABLE Students (
    StudentID int NOT NULL,
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    Age int
);

In this example, StudentID, FirstName, and LastName columns cannot be empty, but the Age column can have NULL values.

2. UNIQUE Constraint​

The UNIQUE constraint ensures that all values in a column (or a combination of columns) are different. This prevents duplicate entries in the specified column(s).

Syntax:​

sql
-- Single column
CREATE TABLE table_name (
    column_name data_type UNIQUE,
    ...
);

-- Multiple columns
CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    CONSTRAINT constraint_name UNIQUE (column1, column2, ...)
);

Example:​

sql
CREATE TABLE Students (
    StudentID int NOT NULL,
    Email varchar(100) UNIQUE,
    Phone varchar(15) UNIQUE,
    ...
);

In this example, each student must have a unique email address and phone number.

3. PRIMARY KEY Constraint​

The PRIMARY KEY constraint uniquely identifies each record in a table. It must contain UNIQUE values and cannot contain NULL values. A table can have only ONE primary key, but that key can consist of multiple columns (composite key).

Syntax:​

sql
-- Single column
CREATE TABLE table_name (
    column_name data_type PRIMARY KEY,
    ...
);

-- Multiple columns
CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...)
);

Example:​

sql
CREATE TABLE Students (
    StudentID int PRIMARY KEY,
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    ...
);

In this example, StudentID is the primary key of the Students table.

4. FOREIGN KEY Constraint​

The FOREIGN KEY constraint is used to link two tables together. It identifies a column (or a set of columns) in one table that refers to a column (or set of columns) in another table. The referenced column is typically the primary key of the referenced table.

Syntax:​

sql
CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    CONSTRAINT constraint_name
    FOREIGN KEY (column1, column2, ...)
    REFERENCES parent_table(parent_key_column1, parent_key_column2, ...)
);

Example:​

sql
CREATE TABLE Courses (
    CourseID int PRIMARY KEY,
    CourseName varchar(100) NOT NULL,
    ...
);

CREATE TABLE Enrollments (
    EnrollmentID int PRIMARY KEY,
    StudentID int NOT NULL,
    CourseID int NOT NULL,
    EnrollmentDate date NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

In this example, the Enrollments table has foreign keys that reference the Students and Courses tables, establishing relationships between them.

5. CHECK Constraint​

The CHECK constraint ensures that all values in a column satisfy certain conditions.

Syntax:​

sql
CREATE TABLE table_name (
    column1 data_type CHECK (condition),
    ...
);

-- Or
CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    CONSTRAINT constraint_name CHECK (condition)
);

Example:​

sql
CREATE TABLE Students (
    StudentID int PRIMARY KEY,
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    Age int CHECK (Age > 0 AND Age < 120),
    ...
);

In this example, the Age column must contain values between 1 and 119.

6. DEFAULT Constraint​

The DEFAULT constraint provides a default value for a column when no value is specified.

Syntax:​

sql
CREATE TABLE table_name (
    column1 data_type DEFAULT default_value,
    ...
);

Example:​

sql
CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    ProductID int NOT NULL,
    OrderDate date DEFAULT CURRENT_DATE,
    Status varchar(20) DEFAULT 'Pending',
    ...
);

In this example, if no value is provided for OrderDate, it will default to the current date, and Status will default to 'Pending'.

Relationships Between Tables​

Constraints, particularly foreign keys, help establish relationships between tables. In a relational database, we commonly use these relationship types:

Practical Example: Building a Library Database​

Let's create a simple library database schema with constraints to see how they work in practice:

sql
-- Create Authors table
CREATE TABLE Authors (
    AuthorID int PRIMARY KEY,
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    BirthYear int CHECK (BirthYear > 1000 AND BirthYear <= EXTRACT(YEAR FROM CURRENT_DATE)),
    CountryOfOrigin varchar(50)
);

-- Create Books table
CREATE TABLE Books (
    ISBN varchar(13) PRIMARY KEY CHECK (LENGTH(ISBN) = 13),
    Title varchar(200) NOT NULL,
    PublicationYear int CHECK (PublicationYear > 1000 AND PublicationYear <= EXTRACT(YEAR FROM CURRENT_DATE)),
    Genre varchar(50),
    AvailableCopies int DEFAULT 0 CHECK (AvailableCopies >= 0)
);

-- Create BookAuthors table (for many-to-many relationship)
CREATE TABLE BookAuthors (
    ISBN varchar(13),
    AuthorID int,
    PRIMARY KEY (ISBN, AuthorID),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN) ON DELETE CASCADE,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE CASCADE
);

-- Create Members table
CREATE TABLE Members (
    MemberID int PRIMARY KEY,
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    Email varchar(100) UNIQUE NOT NULL,
    JoinDate date DEFAULT CURRENT_DATE,
    MembershipStatus varchar(20) DEFAULT 'Active' CHECK (MembershipStatus IN ('Active', 'Expired', 'Suspended'))
);

-- Create Loans table
CREATE TABLE Loans (
    LoanID int PRIMARY KEY,
    ISBN varchar(13) NOT NULL,
    MemberID int NOT NULL,
    LoanDate date DEFAULT CURRENT_DATE,
    DueDate date NOT NULL,
    ReturnDate date,
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN),
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    CHECK (ReturnDate IS NULL OR ReturnDate >= LoanDate),
    CHECK (DueDate >= LoanDate)
);

In this example:

  • PRIMARY KEY constraints uniquely identify each record
  • NOT NULL constraints ensure essential data is provided
  • CHECK constraints validate data values
  • DEFAULT constraints provide fallback values
  • FOREIGN KEY constraints establish relationships between tables
  • UNIQUE constraints prevent duplicate emails for members

Adding, Modifying, and Dropping Constraints​

Adding Constraints to an Existing Table​

sql
-- Adding a NOT NULL constraint
ALTER TABLE Students 
MODIFY Email varchar(100) NOT NULL;

-- Adding a UNIQUE constraint
ALTER TABLE Students
ADD CONSTRAINT unique_email UNIQUE (Email);

-- Adding a CHECK constraint
ALTER TABLE Students
ADD CONSTRAINT check_age CHECK (Age > 0 AND Age < 120);

-- Adding a FOREIGN KEY constraint
ALTER TABLE Enrollments
ADD CONSTRAINT fk_student
FOREIGN KEY (StudentID) REFERENCES Students(StudentID);

Dropping Constraints​

sql
-- Dropping a UNIQUE constraint
ALTER TABLE Students
DROP CONSTRAINT unique_email;

-- Dropping a PRIMARY KEY constraint
ALTER TABLE Students
DROP PRIMARY KEY;

-- Dropping a FOREIGN KEY constraint
ALTER TABLE Enrollments
DROP CONSTRAINT fk_student;

Best Practices for Using Constraints​

  1. Always define a primary key for each table to uniquely identify records.

  2. Use NOT NULL for required fields to prevent missing data.

  3. Use appropriate data types to automatically enforce certain constraints (e.g., DATE types for dates).

  4. Name your constraints explicitly to make troubleshooting easier.

  5. Consider performance implications of constraints, especially on large tables.

  6. Use CHECK constraints to enforce business rules directly in the database.

  7. Use DEFAULT values to simplify data entry and ensure consistency.

  8. Design with normalization in mind to reduce redundancy and improve data integrity.

Summary​

SQL constraints are essential tools for maintaining data integrity in your databases. They provide rules that data must follow before being stored, ensuring that your database remains accurate, consistent, and reliable.

We covered the main types of constraints:

  • NOT NULL prevents missing values
  • UNIQUE prevents duplicate values
  • PRIMARY KEY uniquely identifies records
  • FOREIGN KEY establishes relationships between tables
  • CHECK validates data based on conditions
  • DEFAULT provides fallback values

By properly implementing constraints, you're building a solid foundation for your database applications and preventing many common data issues before they occur.

Exercises​

  1. Create a Products table with appropriate constraints for an e-commerce database.

  2. Modify the library database schema above to add a constraint that ensures the book's publication year is less than or equal to the current year.

  3. Create a Employees and Departments table with a one-to-many relationship and appropriate constraints.

  4. Add a check constraint to the Members table that ensures the email address contains an '@' symbol.

  5. Design a database schema for a school system with at least 4 tables and appropriate constraints.

Additional Resources​


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)