Types of Constraints on SQL Database and Table

Do you know that today your data is more important than ever? 

The insights from big data and high-powered analytics help in cost and time reduction, new product development, and smart decision-making.

Fact: Data is oil, use it well.

Let’s say you have a vast amount of data about the users of your software. Will you be able to get insights from users’ data if the data is not reliable and accurate enough to solve some problems?

So it’s super important for us to increase the efficiency and accuracy of data present in the database.

What are SQL Constraints?

These are some sets of rules and restrictions that are enforced on the attributes of the table to maintain the consistency and integrity of the database.
Constraints are used to ensure the correctness and accuracy of data stored in a database. Allows users to determine what data should be a part of the database, and what kind of modifications are allowed.

There are two types of constraints that can be applied:

1. Column-level constraints — These constraints are applied to a single column
2. Table-level constraints — These constraints are applied to the whole table

How to apply Constraints in SQL?

There are two ways of applying constraints in SQL:

• At the time of creation, add the constraint’s name to impose the specified constraint.

Syntax — 

CREATE TABLE table_name
(
    cloumn_name date_type(size) constraint_name,
    ……

)

• After the creation, we need to use the ALTER TABLE command followed by the constraint’s name.

Syntax — 

ALTER TABLE table_name
(
    MODIFY cloumn_name date_type(size) constraint_name,
    …..
)

Now, let us understand the different types of constraints.

Types of Constraints in SQL —

• NOT NULL Constraint:

NOT NULL is a column-level constraint that ensures that a column will never hold a NULL value, considering it as a not accepted value for that column. 

We can have more than one NOT NULL value in a table.

Syntax with an example to impose NOT NULL constraint — 

CREATE TABLE command:

CREATE TABLE employee
(
    emp_id INT NOT NULL, emp_name VARCHAR(10) NOT NULL
);

ALTER TABLE command:

ALTER TABLE employee MODIFY emp_id INT NOT NULL;

• DEFAULT Constraint:

It specifies a default value for a column if no value is given while inserting a tuple into the table. 

If no value is given while inserting the value then it will consider the given default value for that column.

Syntax with an example to impose DEFAULT constraint — 

CREATE TABLE command:

CREATE TABLE employee
(
    emp_id INTEGER NOT NULL UNIQUE,
    emp_name VARCHAR(10),
    department VARCHAR(10) DEFAULT(‘Sales’)
);

ALTER TABLE command:

ALTER TABLE employee
ALTER department SET DEFAULT(‘Sales’);

• CHECK Constraint:

It makes sure that all values in a column satisfy a specified condition before insertion. It is used to specify the range of values that can be inserted in any column.

If the value passes the given condition then only it will be inserted into a table. Otherwise, the operation will be aborted. 

Syntax and an example of CHECK Constraint — 

CREATE TABLE command:

CREATE TABLE employee
(
    emp_id INTEGER NOT NULL UNIQUE,
    emp_name varchar(10),
    age INTEGER CHECK (age >= 18)
);

ALTER TABLE command:

ALTER TABLE employee ADD CONSTRAINT CHECK (age >= 18);

• UNIQUE Constraint:

It ensures that a column will have a unique value for all tuples in a table. It doesn’t allow duplicate values in a column. But UNIQUE constraints allow NULL values.

It helps to uniquely identify each row in a table. Like NOT NULL constraint, we can declare one or more columns as UNIQUE. 

If you don’t want to store NULL value in a column, you can use NOT NULL and UNIQUE constraints together to do so.

Syntax and an example of UNIQUE Constraint — 

CREATE TABLE command:

CREATE TABLE employee
(
    emp_id INTEGER NOT NULL UNIQUE,
    emp_name varchar(10),
    phone INTEGER UNIQUE
);

ALTER TABLE command:

ALTER TABLE student ADD constraint UNIQUE(phone);

• PRIMARY KEY Constraint:

A PRIMARY KEY is a field that is used to uniquely identify a row in a table. It must contain only UNIQUE values and not NULL values.

UNIQUE + NOT NULL = PRIMARY KEY

A table can contain only one primary key. The primary key can have a single or multiple columns.

It will help us to retrieve results from a table easily. 

Syntax and an example of PRIMARY KEY Constraint —

CREATE TABLE command:

CREATE TABLE employee(
    emp_id INT NOT NULL UNIQUE,
    emp_name VARCHAR(10) NOT NULL,
    phone NUMBER NOT NULL UNIQUE,
    PRIMARY KEY (emp_id, phone)
);

ALTER TABLE command:

ALTER TABLE employee
ADD PRIMARY KEY (emp_id);

ALTER TABLE employee
ADD PRIMARY KEY (emp_id, phone);

• FOREIGN KEY Constraint:

A FOREIGN KEY is a field that is used to link two different tables. It is a simple attribute in one table which points to the primary key of another table.

A foreign key in one table is called a child table and the table which contains a foreign key as a primary key is referred to as a parent table.

FOREIGN KEY is a helpful attribute in SQL JOINS operations.

Syntax and an example of FOREIGN KEY Constraint —

CREATE TABLE command:

CREATE TABLE department (
    dept_id INT NOT NULL,
    emp_id INT,
   
    PRIMARY KEY (dept_id),
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
);

ALTER TABLE command:

ALTER TABLE department
ADD FOREIGN KEY (emp_id) REFERENCES employee(emp_id);

So, these are the constraints that are helpful to keep our data more accurate and prevent us from duplication of data. It maintains consistency in the data.

Do let us know your thoughts on this article. Till then Happy Learning!!