SQL Constraints

In the earlier chapters, we have created a database and a table within the database.

Now, let's look at the SQL Constraints.

What is a Constraint?

Constraints are simply a set of rules or restrictions defined to limit the type of data that can be stored in a table.

  • They can be defined on one or more columns.
  • They can be defined while creating a table using the CREATE TABLE keyword or alter on an existing table using the ALTER TABLE keyword.

They ensure the accuracy and reliability of the table data.

While adding or updating table data, if any of the constraints are violated by the data, the action is immediately aborted.

Commonly Used Constraints

Here is a list of the most commonly used SQL constraints.

NOT NULL Constraint

The constraint NOT NULL defines a column to not accept NULL values.

  • We cannot insert a new row without providing a non-null value to such a column.
  • A null value or simply NULL cannot be zero (0), space, blank, or a zero-length character '' which contains blank.

In the below example, the columns id, name, and phone are defined to have NOT NULL values.

Run this on IDE

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

PRIMARY KEY Constraint

The constraint PRIMARY KEY defines a column or a set of columns to have values that uniquely identify a table row.

  • The primary key cannot be a NULL.
  • No two rows can have the same primary key in a table.
  • One or more columns can be used to define a primary key, but a table can have only one primary key.

In the below example, the column id is defined to have NOT NULL and PRIMARY KEY.

Run this on IDE

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 constraint UNIQUE defines one or more columns to contain unique values.

  • The NULL can be one of the unique values of a column.
  • No two rows can have the same value in a column defined as UNIQUE.
  • Multiple UNIQUE constraints can be used for multiple columns of a table, without any restrictions.

In the below example, the column phone is defined as UNIQUE.

Run this on IDE

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 constraint DEFAULT defines a default value of a column.

  • The INSERT statement will insert a record with the default value when a value is explicitly not provided.

In the below example, the column country is defined using the DEFAULT keyword.

Run this on IDE

CREATE TABLE customers (
    customer_id INT NOT NULL PRIMARY KEY,
    customer_name VARCHAR(30) NOT NULL,
    address VARCHAR(20),
    city VARCHAR(20),
    country VARCHAR(20) NOT NULL DEFAULT 'USA',
    postal_code VARCHAR(10)
);

FOREIGN KEY Constraint

The constraint FOREIGN KEY defines a relationship between the data of two tables, using a column or a combination of columns.

  • It establishes a logical relationship between the data on a table with the primary key on another table.

In the below example, the column supplier_id on the table orders is defined using the FOREIGN KEY keyword.

  • Column customer_id is defined as a FOREIGN KEY with the column customer_id on the customers table.
  • Column shipper_id is defined as a FOREIGN KEY with the column customer_id on the shippers table.

Run this on IDE

CREATE TABLE orders (
    order_id INT NOT NULL PRIMARY KEY,
    order_date DATE NOT NULL,
    order_value INT,
    customer_id INT FOREIGN KEY REFERENCES customers(customer_id),
    shipper_id INT FOREIGN KEY REFERENCES shippers(shipper_id)
);

Where the tables customers and shippers are defined as mentioned below.

CREATE TABLE customers (
    customer_id INT NOT NULL PRIMARY KEY,
    customer_name VARCHAR(30) NOT NULL,
    address VARCHAR(20),
    city VARCHAR(20),
    country VARCHAR(20) NOT NULL,
    postal_code VARCHAR(10)
);
CREATE TABLE shippers (
    shipper_id INT NOT NULL PRIMARY KEY,
    shipper_name VARCHAR(30) NOT NULL,
    phone VARCHAR(15) NOT NULL UNIQUE
);

CHECK Constraint

The constraint CHECK defines a restriction on the values that a column can allow.

  • It limits the value that a column can allow, and aborts the execution in case of other values.
  • It is not supported by the MySQL database server.

In the below example, the column salary is defined using the CHECK keyword, to limit the salary between 5000 and 99000.

Run this on IDE

CREATE TABLE employees (
    employee_id INT NOT NULL PRIMARY KEY,
    employee_name VARCHAR(55) NOT NULL,
    gender CHAR(1),
    birth_date DATE NOT NULL,
    hire_date DATE NOT NULL,
    salary INT NOT NULL CHECK (salary >= 5000 AND salary <= 99000),
    department_id INT FOREIGN KEY REFERENCES departments(department_id)
);

Overall

We now understood what a constraint is used for and have looked at some commonly used constraints.

Related Links