Home » Courses » SQL Crash Course

SQL Constraints – Enforcing Data Integrity

Lesson 17: SQL Constraints – Enforcing Data Integrity

Estimated Read Time: 25-30 minutes

Objective: In this lesson, we will learn about SQL Constraints, which are used to define rules for the data in a table. Constraints ensure that the data in the database is accurate, consistent, and valid. We will explore different types of constraints, such as PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL, and learn how to use them effectively.

1. What Are SQL Constraints?

SQL Constraints are rules or conditions placed on a database table’s columns. Constraints help ensure that the data entered into the database is valid, consistent, and follows the business rules defined by the organization.

By using constraints, you can prevent invalid data from being inserted, ensure the integrity of the relationships between tables, and enforce rules that your application depends on.

2. Types of SQL Constraints

There are several types of constraints in SQL that can be applied to columns or tables. Let’s look at each of them:

2.1 PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table. It ensures that no two rows have the same value in the specified column(s) and that the column cannot have NULL values.

For example, in the employees table, you might have a employee_id column as the primary key:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL
);

In the above example, the employee_id column is the primary key, meaning each employee must have a unique ID, and the value cannot be NULL.

2.2 FOREIGN KEY

The FOREIGN KEY constraint establishes a relationship between two tables. It ensures that a column in one table matches a valid value in another table’s primary key column.

For example, let’s say you have an orders table that references the employees table with the employee_id:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    employee_id INT,
    order_date DATE,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Here, the employee_id column in the orders table is a foreign key that references the employee_id column in the employees table. This ensures that any order in the orders table must correspond to a valid employee in the employees table.

2.3 UNIQUE

The UNIQUE constraint ensures that all values in a column are different. It prevents duplicate entries in a column, similar to the PRIMARY KEY constraint, but allows NULL values (unless explicitly defined). You can apply the UNIQUE constraint to one or more columns.

For example, if you want to ensure that no two employees have the same email address:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

In this case, the email column will not allow duplicate values, ensuring that each employee has a unique email address.

2.4 CHECK

The CHECK constraint is used to limit the values that can be placed in a column. It ensures that the data entered meets a certain condition. The condition is defined using a boolean expression.

For example, to ensure that the salary in the employees table is always greater than 0:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL CHECK (salary > 0)
);

In this example, the salary column is constrained to allow only positive values. Any attempt to insert a negative salary will result in an error.

2.5 NOT NULL

The NOT NULL constraint ensures that a column cannot have a NULL value. This constraint is used to ensure that essential data is not omitted from the database.

For example, to ensure that the name column in the employees table cannot be NULL:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL
);

In this case, every employee must have a name, and it cannot be left blank (NULL).

3. How to Add Constraints After Table Creation

You can also add constraints to an existing table after it has been created. To do this, use the ALTER TABLE statement followed by the ADD CONSTRAINT clause.

For example, to add a CHECK constraint to the salary column in the employees table:

ALTER TABLE employees
ADD CONSTRAINT check_salary CHECK (salary > 0);

This will ensure that only positive salaries can be inserted or updated in the table.

4. Example: Combining Multiple Constraints

You can apply multiple constraints to a single column or table. Here’s an example that combines NOT NULL, CHECK, and UNIQUE constraints:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL CHECK (salary > 0)
);

In this example:

The name column cannot be NULL.
The email column must have unique values.
The salary column must have positive values.

5. Benefits of Using Constraints

Using constraints offers several benefits:

Data Integrity: Constraints ensure that the data entered into the database is valid and follows predefined rules.
Consistency: Constraints help maintain consistency across tables and prevent erroneous or contradictory data.
Business Rules Enforcement: Constraints can enforce business rules directly in the database, reducing the need for application-level checks.

6. Conclusion

In this lesson, we explored SQL constraints and how they help enforce data integrity in your database. We learned about the different types of constraints:

PRIMARY KEY – Uniquely identifies each row.
FOREIGN KEY – Defines relationships between tables.
UNIQUE – Ensures all values in a column are distinct.
CHECK – Enforces specific conditions on column values.
NOT NULL – Ensures columns cannot have NULL values.

In the next lesson, we will dive into SQL Normalization and how to design efficient, scalable databases that reduce redundancy and improve performance.

SQL Crash Course