Data Integrity: How to Use Constraints in SQL

Data integrity is a crucial aspect of database management, ensuring the accuracy, consistency, and reliability of the data stored in relational databases. SQL constraints are the rules that you define to enforce data integrity and ensure that the data follows predefined business rules. Constraints in SQL are used to limit the type of data that can be inserted into a table.

In this article, we'll cover three essential types of constraints: PRIMARY KEY, FOREIGN KEY, and CHECK, and provide examples of how they are used to maintain data integrity.

1. PRIMARY KEY Constraint

What is it?

A PRIMARY KEY constraint ensures that each record in a table is unique and identifies a record uniquely. It also enforces NOT NULL on the column(s) involved, ensuring that the key column(s) cannot contain NULL values.

When to use it?

Use the PRIMARY KEY constraint to identify a unique record in the table.
A table can have only one primary key, but the primary key can consist of one or multiple columns (composite key).

Example:

Consider a table called students where we store the student ID and name.

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

In this case:

The student_id is the primary key, meaning each student must have a unique ID.
The student_name cannot be NULL because it's specified as NOT NULL.

Inserting Data:

INSERT INTO students (student_id, student_name)
VALUES (1, 'John Doe'), (2, 'Jane Smith');

Result:

student_id	student_name
1	John Doe
2	Jane Smith

If we try to insert a record with a duplicate student_id:

INSERT INTO students (student_id, student_name)
VALUES (1, 'Jack Brown');

Error:

ERROR: Duplicate entry '1' for key 'PRIMARY'

The PRIMARY KEY constraint ensures that the student_id is unique.

2. FOREIGN KEY Constraint

What is it?

A FOREIGN KEY constraint is used to link two tables together. It ensures that the value in one table matches a value in another table, maintaining referential integrity between the two.

When to use it?

Use the FOREIGN KEY constraint when one table depends on another (e.g., a child table with references to the parent table).
It ensures that a value in the foreign key column matches a value in the referenced column of the other table, and prevents inserting a value in the foreign key column that does not exist in the referenced table.

Example:

Let's create a courses table where students enroll in courses. We'll use a FOREIGN KEY to link the students table to the courses table.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Here:

enrollments.student_id references the students.student_id (the primary key in the students table).
enrollments.course_id references the courses.course_id (the primary key in the courses table).

Inserting Data:

INSERT INTO courses (course_id, course_name)
VALUES (101, 'Math 101'), (102, 'History 101');

INSERT INTO enrollments (enrollment_id, student_id, course_id)
VALUES (1, 1, 101), (2, 2, 102);

Result:

students table:

student_id	student_name
1	John Doe
2	Jane Smith

courses table:

course_id	course_name
101	Math 101
102	History 101

enrollments table:

enrollment_id	student_id	course_id
1	1	101
2	2	102

Referential Integrity:

If you try to insert an enrollment with a student_id that doesn't exist in the students table:

INSERT INTO enrollments (enrollment_id, student_id, course_id)
VALUES (3, 3, 101);  -- student_id 3 doesn't exist in students table

Error:

ERROR: insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"

The FOREIGN KEY constraint ensures that the student_id exists in the students table before enrolling in a course.

3. CHECK Constraint

What is it?

A CHECK constraint ensures that the values in a column meet a specific condition. It allows you to enforce domain integrity by restricting the values that can be inserted into a column.

When to use it?

Use the CHECK constraint to enforce business rules directly in the database, like ensuring a salary value is within a certain range, or an age value is greater than a threshold.

Example:

Let's say we have a products table where each product has a price. We can use a CHECK constraint to ensure that the price of a product is greater than 0.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    CHECK (price > 0)  -- Enforce that price must be greater than 0
);

Inserting Data:

INSERT INTO products (product_id, product_name, price)
VALUES (1, 'Laptop', 999.99), (2, 'Smartphone', 499.99);

Result:

product_id	product_name	price
1	Laptop	999.99
2	Smartphone	499.99

Enforcing Business Rules:

If we try to insert a product with a non-positive price:

INSERT INTO products (product_id, product_name, price)
VALUES (3, 'Tablet', -50.00);

Error:

ERROR: check constraint "products_price_check" is violated

The CHECK constraint ensures that the price is always positive, enforcing a business rule that no product can have a negative or zero price.

How to Enforce Business Rules with SQL Constraints

SQL constraints are powerful tools for enforcing business rules directly at the database level. Here are a few practical examples:

Enforcing Unique Data: Use PRIMARY KEY or UNIQUE constraints to ensure no duplicate values in columns such as student_id or email addresses.
Example:
```
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);
```
Ensuring Referential Integrity: Use FOREIGN KEY constraints to ensure that relationships between tables are consistent, such as ensuring that every order has a valid customer_id.
Validating Data Ranges: Use the CHECK constraint to enforce conditions such as ensuring an employee's age is within a valid range (e.g., age between 18 and 65).
Example:
```
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    CHECK (age BETWEEN 18 AND 65)
);
```

Conclusion

SQL constraints like PRIMARY KEY, FOREIGN KEY, and CHECK are essential for maintaining data integrity and enforcing business rules at the database level. They ensure the correctness, consistency, and reliability of data by:

Enforcing uniqueness and preventing duplicates (PRIMARY KEY),
Maintaining relationships between tables (FOREIGN KEY),
Validating data conditions (CHECK).