SQL Сonstraints

Introduction

In a table, each column is assigned a specific data type, meaning you cannot insert text into a column with an INT data type or insert a decimal number into a column with a BOOLEAN data type. These data type restrictions help prevent errors, but sometimes, they can be particular. For instance, personal ID numbers should always be unique, and customers must be adults. These specific restrictions on the values in columns are called constraints. The most common constraints are NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, and FOREIGN KEY.

Example

Take employees with the columns: personal_id, first_name, last_name, and age with this simple SQL query:

CREATE TABLE employees (
    personal_id INT,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    age INT
);

We need to modify this table and add some constraints to it.

In this example MySQL syntax is being used. In other SQL dialects the queries may be slightly different.

The NOT NULL constraint will not allow adding a null value to a column. In the table employees, it's possible to make the age column not null.

To add the NOT NULL constraint, use ALTER TABLE MODIFY statement:

ALTER TABLE employees
MODIFY age INT NOT NULL;

With this SQL query, adding a new employee to the table is impossible without stating their age. If there are NULL values in the age column, it will cause an error.

To remove this constraint, use ALTER TABLE MODIFY again without the NOT NULL attribute:

ALTER TABLE employees
MODIFY age INT;

It is possible to use this constraint in the CREATE TABLE statement. Just add it to the end of the column type declaration:

CREATE TABLE employees (
    personal_id INT,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    age INT NOT NULL
);

UNIQUE constraint

The UNIQUE constraint will prohibit adding duplicate values to the column. In the example, the employee identifier should be unique, so it is required to add this constraint to the personal_id column.

To add a UNIQUE constraint to the column when creating the table, specify it after column type declaration:

CREATE TABLE employees (
    personal_id INT UNIQUE,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    age INT 
);

To add UNIQUE to an existing table with a simple query using ALTER TABLE ADD UNIQUE statement:

ALTER TABLE employees
ADD UNIQUE (personal_id);

After the query execution, personal_id becomes unique. If duplicate values are already in this column, it will cause an error, so check the table before adding this constraint.

Sometimes it is required to make more than one column unique. In this case, define a named constraint at the end of the CREATE TABLE statement:

CREATE TABLE employees (
    personal_id INT,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    age INT, 
    CONSTRAINT uq_id_last_name UNIQUE (personal_id, last_name) 
);

To drop a named constraint, use ALTER TABLE DROP INDEX statement:

ALTER TABLE employees
DROP INDEX uq_id_last_name;

CHECK constraint

The CHECK constraint allows adding a logical expression. For example, all employees in the example should be older than sixteen. To add the CHECK constraint in CREATE TABLE use the following query:

CREATE TABLE employees (
    personal_id INT,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    age INT CHECK (age > 16)
);

Use the ALTER TABLE ADD CHECK statement to add this constraint to an existing table. The syntax will be the same as with the UNIQUE constraint.

To drop an unnamed CHECK, use ALTER TABLE DROP CHECK:

ALTER TABLE employees
DROP CHECK age;

The CHECK constraint can be named and assigned to multiple columns. Similar to the UNIQUE example above, specify a named CHECK constraint when creating a table. To use the ALTER TABLE, ADD CONSTRAINT statement and add a named CHECK constraint for one or multiple columns to an existing table with the following query:

ALTER TABLE employees 
ADD CONSTRAINT chk_employee CHECK (age > 16 AND personal_id > 0);

Both the CHECK constraint and a named UNIQUE constraint may be added using the ALTER TABLE ADD CONSTRAINT command.

To delete a named CHECK constraint, use the ALTER TABLE DROP CHECK statement.

DEFAULT constraint

The DEFAULT constraint defines the initial value in a column, which will appear when nothing is being inserted. To determine some default values in our table, the query will be:

CREATE TABLE employees (
    personal_id INT,
    first_name VARCHAR(30) DEFAULT 'John',
    last_name VARCHAR(30) DEFAULT 'Doe',
    age INT DEFAULT 17
);

If a new row is added with only personal_id, the columns first_name, last_name, and age will be defined as John, Doe, and 17, respectively.

To add the DEFAULT constraint to an existing table, use the ALTER TABLE ALTER SET DEFAULT statement:

ALTER TABLE employees
ALTER first_name SET DEFAULT 'John';

To delete an existing DEFAULT constraint, use the ALTER TABLE ALTER DROP DEFAULT statement:

ALTER TABLE employees 
ALTER first_name DROP DEFAULT;

Combining constraints

A column can have more than one constraint. It is helpful to combine NOT NULL and DEFAULT constraints to avoid errors when adding some new information.

If chosen constraints are in place, they can be applied when creating a table using the CREATE statement. To re-create the mentioned employees table using all these valuable constraints at the same time:

CREATE TABLE employees (
    personal_id INT NOT NULL UNIQUE,
    first_name VARCHAR(30) NOT NULL DEFAULT 'John',
    last_name VARCHAR(30) NOT NULL DEFAULT 'Doe',
    age INT DEFAULT 17, 
    CHECK (age > 16) 
);