Constraints in SQL

1. Overview

Constraints in SQL are rules applied to table columns to enforce data integrity and ensure the accuracy and reliability of the data within a database. These constraints help maintain the consistency of the database by restricting the types of data that can be stored in tables.

In this tutorial, we’ll explore different types of constraints in SQL, detailing their behavior, and providing practical examples.

Notably, we’ll use the tables inside the Baeldung University database schema for illustration purposes.

2. NOT NULL

The NOT NULL constraint ensures a column cannot have a NULL value. In other words, this constraint enforces that a column must always contain a value. In practice, we cannot insert a new record, or update a record without adding a value to this column.

For example, let’s use the Department table where each department must have a unique identifier (id). Here, we enforce that the id column cannot be NULL using the NOT NULL constraint:

CREATE TABLE Department (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50),
    code VARCHAR(4),
    UNIQUE (id)
);

In this example, the id column is defined with the NOT NULL constraint, ensuring that every department entry must have a unique and non-NULL identifier. This helps maintain the integrity of the department records by preventing entries with missing id values.

3. UNIQUE

The UNIQUE constraint ensures that all values in a column are distinct. It prevents duplicate values in the specified column. Additionally, we can have more than one UNIQUE column in a table.

For example, let’s use the Student table where each student must have a unique identifier (id). We can enforce that the id column must have unique values using the UNIQUE constraint:

CREATE TABLE Student (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(60),
    national_id BIGINT NOT NULL,
    birth_date DATE,
    enrollment_date DATE,
    graduation_date DATE,
    UNIQUE (id)
);

In this example, the id column is defined with the UNIQUE constraint, ensuring that each student entry has a distinct id. This prevents duplicate student records with the same id value. Thus, we maintain the integrity and uniqueness of student identifiers in the database.

4. PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table. It ensures that the column or combination of columns designated as the primary key has unique values and cannot contain NULL. The primary key enforces entity integrity by uniquely identifying each row.

For example, we have a Department table where each department must have a unique identifier (id). So, we can enforce that the id column must have unique values and cannot be NULL using the PRIMARY KEY constraint:

CREATE TABLE Department (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50),
    code VARCHAR(4),
    UNIQUE (id)
);

In this example, the id column is defined with the PRIMARY KEY constraint, ensuring that each department entry has a unique id value and that it’s not NULL. This constraint is essential for maintaining the uniqueness and integrity of department identifiers in the database.

5. FOREIGN KEY

The FOREIGN KEY constraint is used to link two tables together. It establishes a relationship between a column or columns in one table and a column or columns in another table. Additionally, the FOREIGN KEY ensures that the values in a column or a set of columns match the values in a column or set of columns in another table. This maintains referential integrity between the tables.

As an example, we have two tables: Department and Program. The relationship between them makes each program belong to a specific department. Now, we ensure that the department_id in the Program table references a valid id in the Department table. We can enforce this relationship using the FOREIGN KEY constraint:

CREATE TABLE Department (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50),
    code VARCHAR(4),
    UNIQUE (id)
);

CREATE TABLE Program (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50),
    description VARCHAR(250),
    start_date DATE,
    end_date DATE,
    type VARCHAR(20),
    department_id INT,
    CONSTRAINT program_department_id_fkey FOREIGN KEY (department_id) REFERENCES Department(id),
    UNIQUE (id)
);

In this example, the Department table has a primary key constraint on the id column to ensure unique and non-NULL values. Additionally, the Program table has a foreign key constraint (program_department_id_fkey) on the department_id column.

This constraint ensures that each value in the department_id column of the Program table matches a valid id value in the Department table. Thus, we enforce referential integrity, ensuring that a program cannot reference a non-existent department.

Additionally, if we try to delete a row from the Department table that is referenced by rows in the Program table, the action fails due to the foreign key constraint. The reason is that deleting the department would leave invalid references in the Program table, violating referential integrity.

6. CHECK

The CHECK constraint is used to limit the range of values that can be placed in a column. It ensures that all values in a column satisfy specific conditions defined by the constraint. This helps maintain data integrity by enforcing business rules at the database level.

For example, let’s suppose we have a Department table and we want to ensure that the department code is always four characters long and that the name field is not empty. We can enforce such rules using the CHECK constraint:

CREATE TABLE Department (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50) CHECK (name <> ''),
    code VARCHAR(4) CHECK (LENGTH(code) = 4),
    UNIQUE (id)
);

In this example, the name column has a CHECK constraint to ensure that it’s not an empty string. Additionally, the code column has a CHECK constraint to ensure that it’s exactly four characters long.

7. DEFAULT

The DEFAULT constraint is used to provide a default value for a column when no value is specified during an insert. This helps to ensure that a column always has a valid value and can simplify data entry by automatically assigning common default values.

For example, in the Department table, we might want to ensure that the code column has a default value of GEN (for General) if no specific code is provided. Consequently, we can enforce a default value using the DEFAULT constraint:

CREATE TABLE Department (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50),
    code VARCHAR(4) DEFAULT 'GEN',
    UNIQUE (id)
);

The DEFAULT constraint in this example helps to ensure that the code column is always populated with a meaningful value. In particular, even if the user doesn’t explicitly provide one. This can be useful for maintaining consistency and reducing the chance of errors or incomplete data entries.

8. Conclusion

In this article, we’ve explored various constraints in SQL, including NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT. These constraints are essential for maintaining data integrity and enforcing business rules within a database.

By understanding and utilizing constraints, we can ensure the accuracy, consistency, and reliability of data.