Entity Integrity Constraints - Primary Key and Unique constraints

This post will explain constraints in Microsoft SQL Server, specifically PRIMARY KEY and UNIQUE Constraints.

All constraints are categorized into 3 types:

• Entity Integrity Constraints.
• Domain Integrity Constraints.
• Referential Integrity Constraints.

A constraint is nothing but an additional condition that is associated with the column of a table.

In a database, we store data in a table. We want to store data that is consistent, accurate, and reliable in a table. In order to make the data consistent, accurate, and reliable, we need to associate these constraint(s) with the column of a table.

We can use any of the following two constraints as a part of this category

• Primary Key
• Unique

An entity integrity constraint is used to identify/recognize the row or record of a table uniquely.

When this constraint is associated with the column of a table it will not allow NULL values into the column and it will maintain unique values as part of the table.

We can add only one Primary Key constraint on a table.

We can say that a Primary Key constraint is a combination of Unique and NOT NULL constraints.

When this constraint is associated with the column(s) of a table it will not allow us to store a repetition of data/values in the column, but Unique constraint allows ONE NULL value. More than one NULL value is also considered as repetition, hence it does not store a repetition of NULL values also.

A constraint that is added immediately next to the column definition is known as a Column-level constraint. All constraints that are added after the columns definition of the table is completed are known as Table-level constraints.

We know that a constraint means an additional condition associated with the column of the table. Whenever we want to associate multiple conditions on a single column it is better to add the constraint as a column-level constraint.

Whenever we want to associate multiple conditions/constraints on a single column of a table rather than defining a single constraint multiple times as part of the table for each and every column, it is better to add the single constraint on a group of columns collectively as a table-level constraint.

When we define a table-level constraint we must begin with the keyword "CONSTRAINT" followed by Constraint-Name. The name of each constraint must be unique, as constraints are also objects in a Database. When adding a table-level constraint, we must mention the column name which we want to associate the constraint with.

Advantage with an Entity Integrity Constraint (Primary Key or Unique)

When there are no duplicate values in the roll number column of the table, we can easily identify each and every student record in the stud table uniquely based on the RNO column. That is the reason why Primary Key/Unique constraints are known as Entity Integrity Constraints. Similarly, we can define a product table using a product_id column with a Primary Key constraint. In the Customers table of a bank table, each customer account_number value is unique. This is how we can distinguish one customer from another. Therefore, our responsibility must be to identify/recognize that column in the table with which the data can be uniquely recognized and associate that column in the table with Primary Key constraint.

When we add a constraint as a column-level constraint it will be SQL Server which will give the name to the constraint. However, when we add the constraint as a table-level constraint, we need to give the name of the constraint. It is our choice to give the name of the constraint (we must follow the same rules that we follow when we give a name to the table/column when giving name to a constraint).