SQL Integrity Constraints

Integrity Constraints in SQL: An Overview

Constraints in SQL are some rules that enforce the data to be entered into the database table. Constraints are used to restrict the type of data that can be inserted into a database table. The integrity constraints are one of the protocols that the table's data columns should follow. Achieving the complete configuration of the constraints ensures that the data in the database is accurate and reliable to be consumed in the application.

The given SQL Server tutorial deals with the ways to define SQL Constraints and the various types of SQL Constraints.

Read More: SQL Server Interview Questions and Answers

Ways to Define SQL Constraints

Constraints can be defined in two ways:

1. Column Level

The constraints can be specified immediately after the column definition with the CREATE TABLE statement. These are called column-level constraints.

2. Table Level

The constraints can be specified after all the columns are defined with the ALTER TABLE statement. This is called table-level constraints.

Read More: Basics of SQL Commands

Types of SQL Constraints

In Microsoft SQL Server we have six types of constraints

Read More: Different Types of SQL Keys

1. Primary Key Constraints

The primary key is a set of one or more fields/columns of a table that uniquely identifies each record/row in the database table. It can not accept null, or duplicate values

The primary key is a field of a table that is used to uniquely identify every record or table row. 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

1. The primary key constraint at the column level

   Syntax

    
   column name datatype [CONSTRAINT constraint_name] PRIMARY KEY
   

   Example

   
   CREATE TABLE Employee
   ( id number(5),
   name char(20),
   dept char(10),
   age number(2),
   salary number(10),
   location char(10),
   CONSTRAINT emp_id_pk PRIMARY KEY (id)
   );
   

2. The primary key constraint at the table level

   Syntax

    
   [CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)
   

   Example

   
   CREATE TABLE Employee
   ( id number(5), NOT NULL,
   name char(20),
   dept char(10),
   age number(2),
   salary number(10),
   location char(10),
   ALTER TABLE employee ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (id)
   );
   

2. Unique Key Constraints

A unique key is a set of one or more fields/columns of a table that uniquely identifies 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 columns that uniquely identify a specific record in a table is known as a "unique key" constraint. A unique key is also a type of integrity constraint that 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.

1. Unique key constraint at column level

   Syntax

    
   [CONSTRAINT constraint_name] UNIQUE
   

   Example

   
   CREATE TABLE Employee
   ( id number(5) PRIMARY KEY,
   name char(20),
   dept char(10),
   age number(2),
   salary number(10),
   location char(10) UNIQUE
   ); 
   

2. Unique key constraint at table level

   Syntax

    
   [CONSTRAINT constraint_name] UNIQUE(column_name)
   

   Example

   
   CREATE TABLE Employee
   ( id number(5) PRIMARY KEY,
   name char(20),
   dept char(10),
   age number(2),
   salary number(10),
   location char(10),
   CONSTRAINT loc_un UNIQUE(location)
   );
   

Read More - Commonly Asked DBMS interview questions

3. Foreign Key Constraints or Referential Integrity

A 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 feasibility. 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 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.

1. Foreign key constraint at column level

   Syntax

    
   [CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)
   

   Example

   
   CREATE TABLE product
   ( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY,
   product_name char(20),
   supplier_name char(20),
   unit_price number(10)
   );
   
   CREATE TABLE order_items
   ( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,
   product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),
   product_name char(20),
   supplier_name char(20),
   unit_price number(10)
   );
   

2. Foreign key constraint at table level

   Syntax

    
   [CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);
   

   Example

   
   CREATE TABLE order_items
   ( order_id number(5) ,
   product_id number(5),
   product_name char(20),
   supplier_name char(20),
   unit_price number(10)
   CONSTRAINT od_id_pk PRIMARY KEY(order_id),
   CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
   );
   

4. Not Null Constraints

This constraint ensures that all rows in the database table must contain a value for the column that is specified as not null i.e. 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 that 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 constraints.

Syntax

 
[CONSTRAINT constraint name] NOT NULL

Example

CREATE TABLE employee
( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10)
);

5. 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 a 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.

1. Check constraints at the column level

   Syntax

    
    CREATE TABLE table_name
   (
   col1 datatype [CONSTRAINT constraint_name] CHECK (condition), 
   col2 datatype
   );
   

   Example

   
   CREATE TABLE employee
   ( id number(5) PRIMARY KEY,
   name char(20),
   dept char(10),
   age number(2),
   gender char(1) CHECK (gender in ('M','F')),
   salary number(10),
   location char(10)
   );
   

2. Check constraints at the table level

   Syntax

    
    ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(condition) 
   

   Example

   
   CREATE TABLE employee
   ( id number(5) PRIMARY KEY,
   name char(20),
   dept char(10),
   age number(2),
   gender char(1),
   salary number(10),
   location char(10),
   CONSTRAINT gender_ck CHECK (gender in ('M','F'))
   );
   

Summary

So here we completed one more very important topic. You just need to follow the tutorials in a sequence to get a wholesome understanding. For practical experience, consider our SQL Server Course.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE