Table of contents
1.
Introduction
2.
What are constraints?
2.1.
Types of constraints
3.
Available constraints in SQL
4.
Syntax
5.
NOT NULL Constraint
5.1.
Example of NOT NULL Constraint: 
6.
CHECK Constraint
6.1.
Example of CHECK Constraint
7.
UNIQUE Constraint
7.1.
Example of UNIQUE Constraint
8.
PRIMARY KEY 
8.1.
Example of PRIMARY KEY
9.
FOREIGN KEY
9.1.
Example of FOREIGN KEY
10.
DEFAULT Constraint
10.1.
Example of DEFAULT Constraint
11.
Frequently asked questions
11.1.
What are the available constraints in SQL?
11.2.
Difference between UNIQUE constraint and PRIMARY KEY.
11.3.
How many PRIMARY KEYS can be declared in a table?
11.4.
How many FOREIGN KEYS can be declared in a table?
11.5.
Can constraints be disabled or removed in SQL?
12.
Conclusion
Last Updated: Mar 27, 2024
Medium

Constraints

Author Pakhi Garg
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

While maintaining a lot of data in the database, it is essential to store the data carefully to avoid redundancy, make data reliable and maintain accuracy and integrity. Keeping an eye on all these necessities is a difficult task; that’s why we have constraints in SQL.

Introduction Image

In this article, we will study constraints in detail.

Also Read, LIMIT in SQL

What are constraints?

Constraints are the rules defined on the data being inserted in the columns of a table in a database. They ensure the accuracy, integrity, and reliability of data in the table. If the constraint rule defined is satisfied, data is inserted into the table. Otherwise, the insert operation is terminated if the constraint rule defined is not satisfied and the user needs to correct his data.

Types of constraints

There are two types of constraints in SQL-

1. Table-level constraints are applied to the entire table to restrict the type of data stored there. For example, there is a table named EMPLOYEE which contains the data of employees. We cannot have two employees with the same ID in such a table.  

The types of constraints that we can apply at the table level are- CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY.

2. Column-level constraints are applied to single or multiple columns to restrict the type of data being stored in columns. For example, there is a table named EMPLOYEE which contains the data of employees. The employee name column cannot accept NULL as a value in such a table.

The types of constraints that we can apply at the table level are- NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY.

Also see, DCL Commands in SQL and Tcl Commands in SQL

Available constraints in SQL

There are six constraints available in SQL:

  1. NOT NULL 
  2. CHECK
  3. UNIQUE
  4. PRIMARY KEY
  5. FOREIGN KEY
  6. DEFAULT
     
Constraints in SQL Image

We can specify these constraints when creating the table using the CREATE TABLE command or after creating the table using the ALTER TABLE command.

Syntax

Below is the syntax for defining a constraint using the CREATE TABLE statement.

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

We will now discuss the available constraints in SQL in detail.

NOT NULL Constraint

In SQL, the NOT NULL constraint is used to specify that a column cannot have a NULL value.

A NULL value represents the absence of any value or an unknown value. By setting the NOT NULL constraint on a column, you essentially say that every row in that column must have a value. If a NULL value is attempted to be inserted into a column with the NOT NULL constraint, the operation will fail, and an error will be returned.

Example of NOT NULL Constraint: 

The following SQL query creates a new EMPLOYEE table with ID, NAME, AGE, and SALARY fields. Fields ID, NAME, and AGE are set to NOT NULL.

CREATE TABLE EMPLOYEE (
      ID INT NOT NULL,
      NAME VARCHAR(50) NOT NULL,
      AGE INT NOT NULL,
      SALARY INT
); 

 

If the EMPLOYEE table is already created and the NOT NULL constraint is not defined, we can add the NOT NULL constraint to the SALARY field using the ALTER TABLE command.

ALTER TABLE EMPLOYEE MODIFY SALARY INT NOT NULL;

 

Drop NOT NULL Constraint

To drop the NOT NULL constraint from a column, we will simply change the constraint from NOT NULL to NULL.

ALTER TABLE EMPLOYEE MODIFY SALARY INT NULL;

CHECK Constraint

In SQL, a CHECK constraint ensures that the values in a column meet a specified or set of conditions. It is a type of integrity constraint used to enforce domain integrity.

When creating a table, you can specify a CHECK constraint for one or more columns. The constraint is created using the CHECK keyword, followed by the condition that the column values must satisfy.

Example of CHECK Constraint

The following SQL query creates a new STUDENT table with the fields ID, NAME, AGE, and GENDER. Here, we add a CHECK constraint to the AGE column to not have any student with an age less than 18 years.

CREATE TABLE STUDENT (
      ID INT NOT NULL,
      NAME VARCHAR(50) NOT NULL,
      AGE INT NOT NULL CHECK (AGE >= 18),
      GENDER VARCHAR(10)
); 

If the STUDENT table is already created and the CHECK constraint is not defined, we can add a CHECK constraint to the AGE field using the ALTER TABLE command.

ALTER TABLE STUDENT MODIFY AGE INT NOT NULL CHECK (AGE >= 18);

OR

ALTER TABLE STUDENT ADD CONSTRAINT CheckConstraint CHECK ( AGE >= 18 );

 

Drop CHECK Constraint

The following SQL query will drop the CHECK constraint from the table.

ALTER TABLE STUDENT DROP CONSTRAINT CheckConstraint;

UNIQUE Constraint

In SQL, a UNIQUE constraint ensures that the data stored in a column or a group of columns is unique across all the rows in a table. It ensures that the values in the specified column or group of columns are unique and cannot be duplicated.

When a UNIQUE constraint is applied to a column or a group of columns, the database engine checks that no two rows in the table have the same value for that column or group of columns. If two rows have the same value, the database engine will raise an error, and the INSERT or UPDATE operation will be rejected.

Example of UNIQUE Constraint

The following SQL query will create a new PRODUCT table with ID, NAME, and PRICE fields. The field NAME is set to UNIQUE.

CREATE TABLE PRODUCT (
      ID INT NOT NULL,
      NAME VARCHAR(50) NOT NULL UNIQUE,
      PRICE INT
); 

 

If the PRODUCT table is already created and the UNIQUE constraint is not defined, we can add the UNIQUE constraint to the NAME field using the ALTER TABLE command.

ALTER TABLE PRODUCT MODIFY NAME INT NOT NULL UNIQUE;

OR

ALTER TABLE PRODUCT ADD CONSTRAINT UniqueConstraint UNIQUE ( NAME );

 

Drop UNIQUE Constraint

The following SQL query will drop the UNIQUE constraint from the table.

ALTER TABLE PRODUCT DROP CONSTRAINT UniqueConstraint;

PRIMARY KEY 

In SQL, a PRIMARY KEY constraint is a type of constraint that is used to uniquely identify each row of a table. It specifies that a column or a combination of columns in a table must contain unique values, and no two rows can have the same values in the column(s) specified as the primary key.

A primary key can consist of one or more columns, and it must be unique, not null, and contain a value for each row in the table. It is used to enforce referential integrity and ensure that each row in a table can be uniquely identified.

Example of PRIMARY KEY

The following SQL query will create a new DEPARTMENT table with the fields ID, NAME, and ADDRESS. The field ID is declared as PRIMARY KEY.

CREATE TABLE DEPARTMENT (
      ID INT PRIMARY KEY,
      NAME VARCHAR(50) NOT NULL,
      ADDRESS VARCHAR(50)
); 

 

If the DEPARTMENT table is already created and the PRIMARY KEY is not defined, we can add the PRIMARY KEY using the ALTER TABLE command.

ALTER TABLE DEPARTMENT ADD PRIMARY KEY (ID);

 

Making multiple fields PRIMARY KEY 

A table can have only one PRIMARY KEY, consisting of multiple fields. When multiple fields are used as a PRIMARY KEY, they are called a COMPOSITE KEY. If a table has a PRIMARY KEY defined on any field(s), then we cannot have two records having the same value of that field(s).

For example, the following SQL query will create a new DEPARTMENT table with the fields ID, NAME, and ADDRESS. The combination of fields ID and NAME is declared as the PRIMARY KEY.

CREATE TABLE DEPARTMENT (
      ID INT,
      NAME VARCHAR(50),
      ADDRESS VARCHAR(50),
      PRIMARY KEY ( ID, NAME)
); 

 

If the DEPARTMENT table is already created and the PRIMARY KEY is not defined, we can add the PRIMARY KEY to the ID and NAME fields using the ALTER TABLE command.

ALTER TABLE DEPARTMENT ADD PRIMARY KEY (ID, NAME);

 

Drop PRIMARY KEY

The following SQL query will drop the PRIMARY KEY from the table.

ALTER TABLE DEPARTMENT DROP PRIMARY KEY;

FOREIGN KEY

In SQL, a FOREIGN KEY is a field or a combination of fields in a table that refers to another table's PRIMARY KEY or a UNIQUE constraint. It establishes a relationship between two tables by ensuring the referential integrity of the data.

In other words, the FOREIGN KEY constraint links two tables together by defining a column or group of columns in one table that references another table's PRIMARY KEY or UNIQUE constraint. This creates a parent-child relationship between the two tables. The child table contains the FOREIGN KEY column(s) referencing the parent table's PRIMARY KEY or UNIQUE constraint.

Example of FOREIGN KEY

The following SQL query will create a new CUSTOMER table with CUST_ID, NAME, and ADDRESS fields. Here, CUST_ID is declared as the PRIMARY KEY.

CREATE TABLE CUSTOMER (
      CUST_ID INT PRIMARY KEY,
      NAME VARCHAR(50) NOT NULL,
      ADDRESS VARCHAR(50)
); 

The following SQL query will create another new table called ORDERS with the fields ORD_ID, DATE, CUST_ID, and AMOUNT. The CUST_ID field is declared as FOREIGN KEY, which derives its value from the CUST_ID field of the CUSTOMER table.

CREATE TABLE ORDERS (
       ORD_ID INT PRIMARY KEY,
       DATE DATE,
       CUST_ID INT, 
       AMOUNT DOUBLE,
       FOREIGN KEY CUST_IDX (CUST_ID) REFERENCES CUSTOMER (CUST_ID) 
);  

 

If the ORDERS table is already created and FOREIGN KEY is not defined, we can also declare FOREIGN KEY using the ALTER TABLE command.

ALTER TABLE ORDERS ADD FOREIGN KEY ( CUST_ID ) REFERENCES CUSTOMER ( CUST_ID );

 

Drop FOREIGN KEY

To drop a FOREIGN KEY, you must first find out the constraint that has created that relation. 

You can find the constraints using the following command:

SHOW CREATE TABLE ORDERS;

After that, you will have a similar output as:

Table Create Table
ORDERS CREATE TABLE `ORDERS` (\n  `ORD_ID` int NOT NULL,\n  `DATE` date DEFAULT NULL,\n  `CUST_ID` int DEFAULT NULL,\n  `AMOUNT` double DEFAULT NULL,\n  PRIMARY KEY (`ORD_ID`),\n  KEY `CUST_IDX` (`CUST_ID`),\n  CONSTRAINT `ORDERS_ibfk_1` FOREIGN KEY (`CUST_ID`) REFERENCES `CUSTOMER` (`CUST_ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Here the constraint is mentioned as ‘ORDERS_ibfk_1’. Now we simply need to drop this constraint using the following command.

The following SQL query will drop the FOREIGN KEY from the table.

ALTER TABLE ORDERS DROP FOREIGN KEY ORDERS_ibfk_1;

DEFAULT Constraint

In SQL, a Default constraint is used to provide default values to the fields of a table if the user does not specify any value for that field while inserting data into the table.

Example of DEFAULT Constraint

The following SQL query will create a new EMPLOYEE table with the fields ID, NAME, and SALARY. The field SALARY is set to 10000 by default using the DEFAULT constraint.

CREATE TABLE EMPLOYEE (
      ID INT PRIMARY KEY,
      NAME VARCHAR(50) NOT NULL,
      SALARY DOUBLE DEFAULT 10000
); 

 

If the EMPLOYEE table is already created and the DEFAULT constraint is not defined, then we can add the DEFAULT constraint using the ALTER TABLE command.

ALTER TABLE EMPLOYEE MODIFY SALARY DOUBLE DEFAULT 10000;

 

Drop DEFAULT Constraint

The following SQL query will drop the DEFAULT constraint from the table.

ALTER TABLE EMPLOYEE ALTER COLUMN SALARY DROP DEFAULT;

Also Read - Cardinality In DBMS

Frequently asked questions

What are the available constraints in SQL?

There are six constraints available in SQL. These are NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and DEFAULT.

Difference between UNIQUE constraint and PRIMARY KEY.

The UNIQUE constraint can accept only one NULL value, and the rest of the values must be unique, whereas PRIMARY KEY does not accept any NULL value; all the values must be unique. Moreover, there can be only one PRIMARY KEY in a table, but multiple fields with UNIQUE constraints in a table.

How many PRIMARY KEYS can be declared in a table?

Only one PRIMARY KEY can be declared in a table. However, we can declare a single column as a PRIMARY KEY or a combination of multiple columns.

How many FOREIGN KEYS can be declared in a table?

There can be multiple FOREIGN KEYS in a table. But to declare a column as FOREIGN KEY, that column must be a PRIMARY KEY of some other table.

Can constraints be disabled or removed in SQL?

Yes, constraints can be disabled or removed using the ALTER TABLE statement in SQL. However, this should only be done in certain circumstances, as removing or disabling constraints can compromise the integrity of the data in the database.

Conclusion

In this article, we have briefly discussed constraints, types of constraints - table-level constraints and column-level constraints and available constraints in SQL, i.e., NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and DEFAULT. We went thoroughly through each constraint and discussed their examples.

Reader, don’t stop here. Start your DBMS journey with the DBMS course. Solve SQL problems here.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Also, enroll in our courses and refer to the mock test and problems available. Have a look at the interview experiences and interview bundle for placement preparations.

Happy Coding!

Live masterclass