What are the various types of constraints in SQL?
Constraints are rules and regulations that are enforced on the data. They are used to validate data. It specifies conditions that the data must satisfy.
SQL constraints are used to specify rules for the data in a table. They are used to limit the type of data that can be stored in a particular column of a table. Constraints ensure that the data stored is valid. This ensures the accuracy and reliability of the data in the database.
Constraints could be either at the column level or at the table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the entire table.
Types of SQL Constraints:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
NOT NULL Constraint
By default, a column can hold NULL values. If we don't enter any value in a particular column, it will store NULL if the default value has not been specified. The NULL value is omitted in various operations in a database. To ensure that a column does not contain a NULL value, NOT NULL constraint is used.
NOT NULL constraint applied to a column ensures that the particular column always contains a value, i.e., we cannot insert a new record, or update a record without providing a value for that field.
A NULL is not the same as no data, blank, or a zero-length character string such as ‘ ’, rather, it represents unknown data or that no data entry has been made.
For example:
CREATE TABLE Employees (
Emp_Id INT(3) NOT NULL,
Emp_Name VARCHAR(30) NOT NULL,
Salary DOUBLE(8,2)
);
This query will create a table Employees with fields Emp_Id, Emp_Name, and Salary. Emp_Id and Emp_Name are specified with NOT NULL constraint that means they cannot contain NULL value while salary may contain NULL value.
If Employees table has already been created, then to add a NOT NULL constraint to the Emp_Id and Emp_name column use following syntax:
ALTER TABLE Employees MODIFY Emp_Id INT(3) NOT NULL;
OR
ALTER TABLE Employees ADD [CONSTRAINT notNullEmp] NOT NULL(Emp_Id);
INSERT INTO Employees(Emp_Id,Emp_Name) VALUES(001,”John Doe”);
This statement will store values in specified fields and store NULL in salary field. As Salary field doesnot have a NOT NULL constraint, thus no error will be generated.
INSERT INTO Employees(Emp_Id,Salary) VALUES(007,200000);
This statemnt will store NULL in Emp_Name as no value has been assigned to it. But Emp_Name field has a NOT NULL constraint thus an error will be generated as NULL value cannot be assigned in Emp_NameUNIQUE Constraint
UNIQUE constraint ensures that all the values stored in a column are different from each other. The UNIQUE Constraint prevents two records from having identical values in a column, i.e., the values must not be repeated.
We can use UNIQUE constraint in a single column or multiple columns.
UNIQUE constraint allows columns to have NULL values.
For example:
CREATE TABLE Employees (
Emp_Id INT(3) UNIQUE,
Emp_Name VARCHAR(30),
Salary DOUBLE(8,2)
);
This query will create a table Employees(Emp_Id, Emp_Name and Salary). The Emp_Id has a UNIQUE constraint thus each row of Emp_Id field will have a different value.
If the Employees table has already been created, then to add a UNIQUE constraint to the Emp_Id column:
ALTER TABLE Employees MODIFY Emp_Id INT(3) UNIQUE;
OR
ALTER TABLE Employees ADD [CONSTRAINT myUniqueConstraint] UNIQUE(Emp_Id);
To drop a UNIQUE constraint, use the following SQL query:
ALTER TABLE Employees DROP [UNIQUE Emp_Id | CONSTRAINT myUniqueConstraint];PRIMARY KEY Constraint
A primary key constraint uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.
A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
The field with a PRIMARY KEY constraint can be used to uniquely identify each record of a table in various database operations.
For example:
CREATE TABLE Employees (
Emp_Id INT(3) PRIMARY KEY,
Emp_Name VARCHAR(30),
Salary DOUBLE(8,2)
);
This query will create a table Employees(Emp_Id, Emp_Name and Salary) with Emp_Id field as PRIMARY KEY constraint.
To create a PRIMARY KEY constraint on the “EMP_ID” column when Employees table already exists, use the following SQL syntax:
ALTER TABLE Employees MODIFY Emp_Id INT(3) PRIMARY KEY;
OR
ALTER TABLE Employees ADD [CONSTRAINT PK_Emp_Id] PRIMARY KEY (Emp_Id);
To drop PRIMARY KEY Constraints from a table :
ALTER TABLE Employees DROP [PRIMARY KEY | CONSTRAINT PK_Emp_Id];FOREIGN KEY Constraint
Foreign Key Constraint is used to link two tables. It is used to establish a relationship between the data in two tables.
A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. The table containing the FOREIGN KEY is called the child table, and the table containing the candidate key is called the parent table.
FOREIGN KEY is used to enforce referential integrity. It is used to prevent actions that would destroy links between tables. It also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
For example:
CREATE TABLE Employees (
Emp_Id INT(3) PRIMARY KEY,
Emp_Name VARCHAR(30),
Dept_Id INT(5) REFERENCES Department(Dept_Id)
);
This query creates a table Employees(Emp_Id, Emp_Name and Dept_Id) with Dept_Id as FOREIGN KEY which links Employees table to Department table.
To create a FOREIGN KEY constraint on the “Dept_Id” column when Employees table already exists, use the following SQL syntax:
ALTER TABLE Employees ADD [CONSTRAINT fKey] FOREIGN KEY (Dept_Id) REFERENCES Department(Dept_Id);
To drop FOREIGN KEY Constraints from a table :
ALTER TABLE Employees DROP [FOREIGN KEY Dept_Id | CONSTRAINT fKey];CHECK Constraint
CHECK constraint is used to limit the value range that can be placed in a column. Using check constraint, we can specify conditions for a field, which will be evaluated at the time of entering the data to a column. If the condition evaluates to false, the record violates the constraints and it will not be entered in the table.
If we define a CHECK constraint on a single column it allows only certain values for this column.
If we define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
For example:
CREATE TABLE Employees (
Emp_Id INT(3) PRIMARY KEY,
Emp_Name VARCHAR(30),
Salary DOUBLE(8,2) CHECK(Salary > 20000)
);
This query will create a table Employees(Emp_Id,Emp_Name and Salary).CHECK Constraint has been applied on Salary field, thus at the time of entering data into the table, this constraint will be evaluated,if it return false the data will not be entered.
To create a CHECK constraint on the Salary column when Employees table already exists, use the following SQL syntax:
ALTER TABLE Employees ADD [CONSTRAINT CHK_Salary] CHECK (Salary > 20000);
To drop a CHECK constraint, use the following SQL:
ALTER TABLE Employees DROP [CONSTRAINT CHK_Salary | CHECK Salary];DEFAULT Constraint
DEFAULT constraint is used to provide a default value for the fields of a table. That is, if at the time of entering new records in the table if the user does not specify any value for these fields then the default value will be assigned to them. The user needs to define these default values.
For example:
CREATE TABLE Employees (
Emp_Id INT(3),
Emp_Name VARCHAR(30),
Salary DOUBLE(8,2) DEFAULT 20000.00
);
This query will create a table Employees(Emp_Id, Emp_Name, Salary) with DEFAULT Constraint applied to Salary field, thus if no value is entered into Salary field, it will automatically store the default value i.e. 20000.00.
To create a DEFAULT constraint on the Salary column when Employees table already exists, use the following SQL syntax:
ALTER TABLE Employees ADD CONSTRAINT CHK_Salary DEFAULT 20000 FOR Salary;
OR
ALTER TABLE Persons ALTER City SET DEFAULT 20000;
To drop a DEFAULT constraint, use the following SQL:
ALTER TABLE Employees ALTER Salary DROP DEFAULT;So, these are some of the constraints that are used ion SQL. Hope you learned something new today. That's it for this blog.
Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here .
Keep Learning :)
Team AfterAcademy!
