What are SQL constraints and its different types?

As large amounts of data is present in the databases, it is very important for all of us to to increase the accuracy and reliability of data present in the database. Well, the SQL constraints are used to maintain the same. There are different types of constraints which could be used. In this article, I will discuss those constraints with examples.

The following topics will be covered in this article:

1. What are Constraints?
2. Constraints available in SQL:

• • • NOT NULL Constraint
    • UNIQUE Constraint
    • CHECK Constraint
    • DEFAULT Constraint
    • INDEX Constraint

What are SQL Constraints?

SQL Constraints are used to specify the rules for the data in a table. These are used to limit which type of data must be stored in the database, and aims to increase the accuracy and reliability of the data stored in the database.

So, constraints make sure that there is no violation in terms of a transaction of the data, yet there is any violation found; the action gets terminated.

There are two types of constraints which  can be applied:

1. Column-level constraints – These constraints are applied to a single column
2. Table-level constraints – These constraints are the application to the complete table

Moving forward in this article, let us understand the different types of constraints. Also, I am going to consider the following table to help you understand better.

Different SQL Constraints available:

NOT NULL Constraint

The NOT NULL constraint makes sure that a column cannot have a NULL value. You can use the NOT NULL constraint either while creating the table database or while modifying it.

Example

NOT NULL Constraint on CREATE TABLE

Write a query to create the above Students table, where the StudentID and StudentName cannot be NULL.

CREATE TABLE Students( 
StudentID int NOT NULL, 
StudentName varchar(255) NOT NULL, 
Age int, City varchar(255) );

NOT NULL Constraint on ALTER TABLE

Write a query to alter the above Students table, where a new column of DOB must be added, and it should not have any NULL values.

ALTER TABLE Students ADD COLUMN DOB year NOT NULL;

Moving on in this article on SQL Constraints, let us understand how to use the UNIQUE constraint.

UNIQUE Constraint

The UNIQUE constraint is used to make sure that all the values in a column are unique. You can use the UNIQUE constraint either on multiple columns or on a single column with. Apart from this, you can go forward and use the UNIQUE constraint to modify the existing tables.

Note: 

1. While creating tables, a PRIMARY KEY constraint automatically has a UNIQUE constraint, to guarantee the uniqueness of a column.
2. A table can have many UNIQUE constraints but can have a single primary key constraint.

Example:

UNIQUE Constraint on CREATE TABLE

Write a query to create a table Students, with columns StudentID, StudentName, Age and City. Here, the StudentID must be unique for each and every record.

CREATE TABLE Students ( 
StudentID int NOT NULL UNIQUE, 
StudentName varchar(255) 
NOT NULL, Age int, City varchar(255) );

Name a UNIQUE constraint on multiple columns

To name a unique constraint and to define it for multiple columns you can refer to the following example:

Write a query to create a table Students, with columns StudentID, StudentName, Age and City. Here, the StudentID, and StudentName must be unique for each and every record.

CREATE TABLE Students ( 
StudentID int NOT NULL, 
StudentName varchar(255) NOT NULL, 
Age int, 
City varchar(255) CONSTRAINT Stu_Example 
UNIQUE (StudentID,StudentName) );

Here, Stu_Example is the name given to the unique constraint applied on the StudentID and StudentName.

UNIQUE Constraint on ALTER TABLE

Write a query to alter the Students table, where a UNIQUE constraint must be added to the StudentID column.

ALTER TABLE Students ADD UNIQUE (StudentID);

Similarly, if you want to use the UNIQUE constraint on multiple columns and also name it, you can write a query as follows:

ALTER TABLE Students ADD CONSTRAINT Stu_Example UNIQUE (StudentID,StudentName);

Drop a UNIQUE constraint

To drop the constraint specified on a column, you can use the naming convention which you might have mentioned while adding the constraint.

For example, if we have to write a query to drop the UNIQUE constraint we created above, you can write the query as follows:

ALTER TABLE Students DROP CONSTRAINT Stu_Example;

Next in this article on SQL Constraints, let us understand how to use the CHECK constraint.

CHECK Constraint

The CHECK constraint makes sure that all values in a column satisfy a specific condition.

Example:

CHECK Constraint on CREATE TABLE

Write a query to create a table Students, with columns StudentID, StudentName, Age and City. Here, the city must be MUmbai.

CREATE TABLE Students ( 
StudentID int NOT NULL UNIQUE, 
StudentName varchar(255) NOT NULL, 
Age int, 
City varchar(255)CHECK (City==’Mumbai’) );

CHECK constraint on multiple columns

To use the check constraint on multiple columns you can write a query as below:

Write a query to create a table Students, with columns StudentID, StudentName, Age and City. Here, the City must be Mumbai, and the age of students must be > 19.

CREATE TABLE Students ( 
StudentID int NOT NULL, 
StudentName varchar(255) NOT NULL, 
Age int, 
City varchar(255)CHECK (City==’Mumbai’ AND Age>19));

Similarly, you can use the CHECK constraint with the ALTER TABLE command also. Refer below.

CHECK Constraint on ALTER TABLE

Write a query to alter the Students table, where a CHECK constraint must be added to the City column. Here, the city must be Mumbai.

ALTER TABLE Students ADD CHECK (City=='Mumbai');

Similarly, if you want to use the CHECK constraint by giving it a name you can write a query as follows:

ALTER TABLE Students ADD CONSTRAINT StuCheckExample CHECK (City=='Mumbai');

Drop a CHECK constraint

To drop the constraint specified on a column, you can use the naming convention which you might have mentioned while adding the constraint.

For example, if we have to write a query to drop the CHECK constraint we created above, you can write the query as follows:

ALTER TABLE Students DROP CONSTRAINT StuCheckExample;

Moving on in this article on SQL Constraints, let us understand how to use the DEFAULT constraint.

DEFAULT Constraint

The DEFAULT constraint is used to mention a set of default values for a column when no value is specified. Similar to that of the other constraints, we can use this constraint on the CREATE and ALTER table command.

Example

Write a query to create a table Students, with columns StudentID, StudentName, Age and City. Also when there is no value inserted in the City column, automatically Delhi must be included.

CREATE TABLE Students ( 
StudentID int NOT NULL, 
StudentName varchar(255) NOT NULL, 
Age int, 
City varchar(255)DEFAULT ‘Delhi’);

DEFAULT Constraint on ALTER TABLE

To use the DEFAULT constraint with the ALTER TABLE command, you can write a query as follows:

ALTER TABLE Students ADD CONSTRAINT StuDefauExample DEFAULT 'Mumbai' FOR City;

Drop a DEFAULT constraint

To drop the DEFAULT constraint you can use the ALTER TABLE command as follows:

ALTER TABLE Students ALTER COLUMN City DROP DEFAULT;

Next in this article on SQL Constraints, let us understand how to use the INDEX constraint.

INDEX Constraint

The INDEX constraint is used to create indexes in the table, With the help of these indexes you can create and retrieve data from the database very quickly.

Syntax

--Create an Index where duplicate values are allowed
CREATE INDEX IndexName
ON TableName (ColumnName1, ColumnName2, ...ColumnName(N));

--Create an Index where duplicate values are not allowed
CREATE UNIQUE INDEX IndexName
ON TableName (ColumnName1, ColumnName2, ...ColumnName(N));

Example

Write a query to create an index with the name Stu_index on the Students table which stores the StudentName.

CREATE INDEX Stu_index ON Students (StudentName);

Similarly, to delete an index from the table, you have to use the DROP command with the name of the index.

DROP INDEX Students.Stu_index;

Apart from the above constraints the PRIMARY KEY and the FOREIGN KEY are also considered as constraints. The PRIMARY KEY constraint is used to define constraints on how a specific column uniquely identifies every tuple.  The FOREIGN KEY constraint is used to relate two tables based on a relationship.

With this, we come to an end to this article. I hope you understood how to use the various constraints present in the database. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this article on SQL Constraints and I will get back to you.