Do you think IIT Guwahati certified course can help you in your career?
No
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.
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.
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.
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;
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.