SQL Constraints

What is Constraint?

A constraint is a rule applied to one or more columns of a table to restrict the type of values that can be stored in those columns. Constraints ensure data accuracy and integrity within a database table.

There are various types of constraints in SQL, such as:

• NOT NULL
• PRIMARY KEY
• UNIQUE
• DEFAULT
• FOREIGN KEY
• CHECK

Now, let's explore each of these constraints in detail.

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values.

This constraint mandates that when inserting a new row into the table, a non-NULL value must be provided for columns with the NOT NULL constraint.

The following SQL statement creates a table named persons with four columns. The id, name, and phone columns are configured to disallow NULL values:

CREATE TABLE persons (
id INT NOT NULL,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL
);

PRIMARY KEY Constraint

The PRIMARY KEY constraint defines a column or set of columns whose values uniquely identify each row in a table. No two rows in the table can have the same primary key value. Additionally, a primary key column cannot accept NULL values.

The following SQL statement creates a table named persons and designates the id column as the primary key, ensuring uniqueness and non-null values:

CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL
);

UNIQUE Constraint

The UNIQUE constraint ensures that one or more columns in a table contain unique values.

While both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce uniqueness on a column or combination of columns that is not designated as the primary key.

The following SQL statement creates a table named persons and designates the phone column as unique, preventing duplicate values:

CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);

DEFAULT Constraint

The DEFAULT constraint defines a default value for columns in a table.

A column default is a value automatically inserted by the database engine when an INSERT statement does not explicitly assign a value to that column.

The following SQL statement sets a default value for the country column:

CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE,
country VARCHAR(30) NOT NULL DEFAULT 'Australia'
);

FOREIGN KEY Constraint

A foreign key (FK) is a column or combination of columns used to establish and enforce a relationship between data in two tables.

Below is a diagram illustrating the relationship between the employees and departments tables. The dept_id column in the employees table corresponds to the primary key column in the departments table, making dept_id a foreign key in the employees table.

In MySQL, you can create a foreign key by defining a FOREIGN KEY constraint when creating a table. The following statement establishes a foreign key on the dept_id column of the employees table that references the dept_id column of the departments table:

CREATE TABLE employees (
emp_id INT NOT NULL PRIMARY KEY,
emp_name VARCHAR(55) NOT NULL,
hire_date DATE NOT NULL,
salary INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CHECK Constraint

The CHECK constraint restricts the values that can be entered into a column.

For instance, you can limit the range of values for a salary column by creating a CHECK constraint that only allows values between 3,000 and 10,000. This ensures that salaries outside the typical range cannot be entered. Here’s an example:

CREATE TABLE employees (
emp_id INT NOT NULL PRIMARY KEY,
emp_name VARCHAR(55) NOT NULL,
hire_date DATE NOT NULL,
salary INT NOT NULL CHECK (salary >= 3000 AND salary <= 10000),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);