Constraints

Pakhi Garg
Last Updated: May 13, 2022

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.

 

In this article, we will study constraints in detail.

 

What are constraints?

Constraints are the rules defined on the data being inserted in the columns of a table in a database. They ensure 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: These constraints are applied to the entire table to restrict the type of data being stored in the table. For example, there is a table named EMPLOYEE which contains the data of employees. In such a table, we cannot have two employees with the same ID. 
    The types of constraints that we can apply at the table level are- CHECK, UNIQUE, PRIMARY KEY and FOREIGN KEY.
     
  2. Column level constraints: These 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. In such a table, the employee name column cannot accept NULL as a value.
    The types of constraints that we can apply at the table level are- NOT NULL, CHECK, UNIQUE, PRIMARY KEY and FOREIGN KEY.

 

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

 

Source: eduCBA

 

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

 

Syntax

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

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

 

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

NOT NULL Constraint

By default, a column can have NULL values. If we do not want NULL values in a column, we need to define the NOT NULL constraint. Once this constraint is defined then that column will never accept NULL values. 

Example of NOT NULL Constraint

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

CREATE TABLE EMPLOYEE (
      ID INT NOT NULL,
      NAME VARCHAR(50NOT 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

Through the CHECK constraint, we can restrict the data being inserted in the column of a table. In the CHECK constraint, we define a condition that is checked while inserting the data. If the condition is satisfied, then data is inserted; else, data is not inserted. This constraint ensures that no corrupted information is entered in a column. We can specify more than one CHECK constraint for a specific column.

Example of CHECK Constraint

The following SQL query creates a new table called STUDENT 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(50NOT NULL,
      AGE INT NOT NULL CHECK (AGE >= 18),
      GENDER VARCHAR(10)
); 

 

If the STUDENT table is already created and CHECK constraint is not defined, we can add 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

This constraint ensures that no duplicate values are entered in a column of a table. A column will have only unique values. It is different from PRIMARY KEY as the UNIQUE constraint can have only one NULL value, whereas PRIMARY KEY cannot have any NULL value.

Example of UNIQUE Constraint

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

CREATE TABLE PRODUCT (
      ID INT NOT NULL,
      NAME VARCHAR(50NOT 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 UNIQUENAME );

 

Drop UNIQUE Constraint

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

ALTER TABLE PRODUCT DROP CONSTRAINT UniqueConstraint;

 

PRIMARY KEY 

The PRIMARY KEY is used to identify a record in a table uniquely. There is only one PRIMARY KEY  in a table which can consist of single or multiple fields. PRIMARY KEYS are always unique and cannot have NULL values.

Example of PRIMARY KEY

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

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

 

If the DEPARTMENT table is already created and the PRIMARY KEY is not defined, we can add the PRIMARY KEY to the ID field 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, which can also consist of multiple fields. When multiple fields are used as a PRIMARY KEY, they are called 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 table called DEPARTMENT with the fields ID, NAME and ADDRESS. The combination of fields ID and NAME are declared as the PRIMARY KEY.

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

 

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

ALTER TABLE DEPARTMENT ADD PRIMARY KEY (IDNAME);

Drop PRIMARY KEY

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

ALTER TABLE DEPARTMENT DROP PRIMARY KEY;

 

FOREIGN KEY

Also known as a referencing key, a FOREIGN KEY is used to uniquely identify each row of another table. A FOREIGN KEY is a column or multiple columns whose value matches the PRIMARY KEY of some other table. This key establishes a link between two tables.  

Example of FOREIGN KEY

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

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

Now, 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 references CUSTOMER ( CUST_ID ), 
      AMOUNT DOUBLE
); 

 

If the ORDERS table is already created and FOREIGN KEY is not defined then 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

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

ALTER TABLE ORDERS DROP FOREIGN KEY;

 

DEFAULT Constraint

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 table called EMPLOYEE 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(50NOT 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;

 

Frequently asked questions

  1. What are the available constraints in SQL?
    Ans. There are six constraints available in SQL. These are NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY and DEFAULT.
     
  2. Difference between UNIQUE constraint and PRIMARY KEY.
    Ans. 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 there can be multiple fields with UNIQUE constraints in a table.
     
  3. How many PRIMARY KEYS can be declared in a table?
    Ans. Only one PRIMARY KEY can be declared in a table. However, we can either declare a single column as PRIMARY KEY or a combination of multiple columns.
     
  4. How many FOREIGN KEYS can be declared in a table?
    Ans. 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.

Key Takeaways

In this article, we have briefly discussed what are 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.

Happy Learning!

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think