SQL Integrity Constraints or Constraints

31 Aug 2022

Intermediate

164K Views

5 min read

Constraints are some rules that enforce on the data to be entered into the database table. Basically, constraints are used to restrict the type of data that can insert into a database table.

The integrity constraints are one of the protocols that should be followed by the table's data columns. Constrains are generally used to restrict the multiple types of information that can be entered into a table to ensure the integrity of the data. Achieving the complete configuration of the constraints ensures that the data in the database is accurate and reliable to be consumed in the application. We can apply the integrity constraints at the column or table level. The table-level Integrity constraints apply to the entire table, while the column level constraints are only applied to one column.

Constraints can be defined in two ways:

Column Level
The constraints can be specified immediately after the column definition with the CREATE TABLE statement. These are called column-level constraints.
Table Level
The constraints can be specified after all the columns are defined with the ALTER TABLE statement. This is called table-level constraints.

Types of SQL Constraints

In Microsoft SQL Server we have six types of constraints

Primary Key Constraints
The primary key is a set of one or more fields/columns of a table that uniquely identify each record/row in the database table. It can not accept null, duplicate values.
The primary key is a field of a database table that is used to uniquely identifies every record or the table row. And at the same time, the primary key is also one type of integrity constraint ultimately. The primary keys must have distinct values which means one of the columns of the table should have a unique value from the other. By default with the primary key, null values are not allowed in a primary key column of the table. Any table may only have a single primary key, that can be made up of one or multiple fields. By doing all these, it creates a composite primary key when the multiple fields are used as the primary key

Primary key constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] PRIMARY KEY,
col2 datatype
); 
```
Primary key constraint at table level
```
 ALTER TABLE table_name
ADD[CONSTRAINT constraint_name] PRIMARY KEY (col1,col2)
```
Unique Key Constraints
A unique key is a set of one or more fields/columns of a table that uniquely identify each record/row in a database table. It is like a Primary key but it can accept only one null value and it can not have duplicate values.
A set of one or more table fields or the column that uniquely identifies a specific record in a table is known as a "unique key" constrain. A unique key is also a type of integrity constraint which is similar to the primary key but keep in mind that a unique key will only accept one null value and should not have any duplicate values across the table. Both the special key and the primary key help to identify that a column or a group of columns is unique. A Unique key is generated automatically by default. Below is a simple example showing the usage of a unique key.

Unique key constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] UNIQUE,
col2 datatype
); 
```
Unique key constraint at table level
```
 ALTER TABLE table_name
ADD[CONSTRAINT constraint_name] UNIQUE (col1,col2)
```
Foreign Key Constraints
Foreign Key is a field in a database table that is the Primary key in another table. It can accept multiple nulls and duplicate values.
Foreign keys are generally used to ensure the consistency of the database data while providing some feasibilities. The foreign is also a type of integrity constraint as we have the primary key. The foreign key constraint identifies any column referencing the primary key in another different table. It defines the relationship or connection between the two columns of the same table or in between the different tables. To define a column as a foreign key, it should be defined as a Primary Key in the table to which it is referring. Below is the example to create the foreign key constrain.

Foreign key constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_column_name),
col2 datatype
); 
```
Foreign key constraint at table level
```
 ALTER TABLE table_name
ADD[CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_col)
```
Not Null Constraints
This constraint ensures that all rows in the database table must contain a value for the column which is specified as not null means a null value is not allowed in that column.
This constraint is used to ensure that all the rows of the table contain a definite value for the column which is specified as not null. IT means that the null value is not allowed. Below is a simple example that shows how to use not null constrain.

Not Null constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] NOT NULL, 
col2 datatype
);
```
Not Null constraint at table level
```
 ALTER TABLE table_name
ALTER COLUMN col1 datatype NOT NULL
```
Check Constraints
This constraint defines a business rule on a column in the database table that each row of the table must follow this rule.
A check constraint is generally defined on a specific column or the set of columns to apply the strict limit with the series of values, it can be added to these columns, by using a predefined condition. The check constraint triggered into the action to evaluate the newly added or the modified values, where the value that fulfills the condition will be added to the table seamlessly, otherwise, the insert operation will be discarded somehow. this is allowed to specify the multiple check constraints for the same column.

Check constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] CHECK (condition), 
col2 datatype
); 
```
Check constraint at table level
```
 ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(condition) 
```