Constraints in SQL

What are Constraints in SQL?

Constraints are rules that can be applied to a table's data type. That is, we may use constraints to limit the type of data that can be recorded in a specific column in a table.
When using the CREATE TABLE statement to create a table, we can define constraints. The ALTER TABLE statement can also be used to specify constraints after a table has been created.

Syntax to Create Constraints

CREATE TABLE table_name
(
column1 data_type(size) constraint_name,
column2 data_type(size) constraint_name,
column3 data_type(size) constraint_name,
....
);

Different Constraints in Brief

The available constraints in SQL are:

🚀 NOT NULL :
This constraint states that a null value cannot be stored in a column. That is, if a column is marked as NOT NULL, we will no longer be able to store null values in that column.

🚀 UNIQUE :
When used with a column, this constraint specifies that all of the values in the column must be unique. That is, the values in any column's row cannot be repeated.

🚀 PRIMARY KEY :
A PRIMARY KEY is a field that can be used to uniquely identify each row in a table. And this constraint is used to designate a table field as the main key.

🚀 FOREIGN KEY :
A FOREIGN KEY is a field that can be used to uniquely identify each row in another table. This constraint is also used to designate a field as a Foreign key.

🚀 CHECK :
This constraint aids in validating the values of a column against a set of criteria. That is, it aids in ensuring that the value contained in a column satisfies a set of criteria.

🚀 DEFAULT :
When no value is supplied by the user, this constraint specifies a default value for the column.

Let's discuss each of them in brief with example,

NOT NULL

If we declare a table field to be NOT NULL. The field will never accept a null value after that. That is, you will not be able to insert a new row in the table without giving this field a value. The following query, for example, creates a table Employee with the fields ID and NAME set to NOT NULL. That is, if we want to insert a new row, we must specify values for these two fields.

CREATE TABLE Employee
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
Mobile number int(10)
);

UNIQUE

This restriction aids in the unique identification of each table row. i.e., for a given column, all rows should have the same value. In a table, we can have many UNIQUE columns. The query below, for example, creates a table called Employee with the column ID UNIQUE. To put it another way, no two employees can have the same ID.

CREATE TABLE Employee
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10) NOT NULL,
Mobile number int(10)
);

PRIMARY KEY

The primary key is a field that uniquely identifies each table row. If a column in a table is designated as a primary key, it cannot include NULL values, and all rows must have unique values for this field. To put it another way, this is a combination of NOT NULL and UNIQUE constraints. A primary key can only be one field in a table. The following query will construct a table called Employee with the field ID as the primary key.

CREATE TABLE Employee
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10) NOT NULL,
Mobile number int(10)
PRIMARY KEY(ID)
);

FOREIGN KEY

A foreign key is a table field that uniquely identifies each row of a different table. That is, this field refers to a table's main key. This usually results in a connection between the tables.

CHECK

We can provide a condition for a field using the CHECK constraint, which must be met when entering data for that field. For example, the query below creates a table Employee with the criteria (AGE >= 22) for the field AGE. That example, the user will be unable to enter any records in the database that have an AGE of less than 22.

CREATE TABLE Employee
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10) NOT NULL,
Mobile number int(10)
AGE int NOT NULL CHECK (AGE >= 22)
);

DEFAULT

This constraint is used to provide the fields a default value. That is, if the user does not indicate a value for these fields while adding new records to the database, the default value will be applied to them. The following query, for example, will create a table called Employee and set the default value for the field AGE to 22.

CREATE TABLE Employee
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10) NOT NULL,
Mobile number int(10)
AGE int DEFAULT 22
);