Post
Ask Question

Entity Integrity Constraints - Primary Key and Unique constraints

Introduction

 
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.
 

Entity Integrity Constraint

 
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.
 

Primary Key Constraint

 
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.
 

Unique Constraint

 
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.
 
General syntax to add a constraint on the column of a table
 
CREATE TABLE <TableName>
(<Col-1> <DataType> [<ConstraintType-1> <Constraint-2> .... <ConstraintType-n>],
<Col-2> <DataType> [<ConstraintType-1> <Constraint-2> .... <ConstraintType-n>],
<Col-3> <DataType> [<ConstraintType-1> <Constraint-2> .... <ConstraintType-n>],
...
...
<Col-N> <DataType> [<ConstraintType-1> <Constraint-2> .... <ConstraintType-n>],
CONSTRAINT <ConstraintName-1> <ConstraintType>(<ColName-1>[,<ColName-2>,...,<ColName-N>]),
CONSTRAINT <ConstraintName-2> <ConstraintType>(<ColName-1>[,<ColName-2>,...,<ColName-N>]),
...
...
CONSTRAINT <ConstraintName-N> <ConstraintType>(<ColName-1>[,<ColName-2>,...,<ColName-N>])
) 
 
In the above syntax please note the following points
 
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.
 
What is the difference between column-level constraints and 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.
 
Note
 
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).
  1. create database mytestdb1  
  2. use mytestdb1  
  3. CREATE TABLE stud  
  4. (rno int,  
  5. sname varchar(20),  
  6. age numeric(2,0),  
  7. gen varchar(6))  
  8. --When there is no constraint added on the column(s) of the table there is no restriction  
  9. INSERT INTO stud VALUES(1,'Amit',21,'Male')  
  10. SELECT * FROM stud  
  11. INSERT INTO stud VALUES(1,'Anil',21,'Male')  
  12. --Therefore when there is no constraint we can have more than one student with the same roll number. But the problem will be to uniquely identifying the students with roll number.  
  13. SELECT * FROM stud  
  14. DROP TABLE stud  
  15. --Adding a Primary Key constraint at column-level of the table...  
  16. CREATE TABLE stud  
  17. (rno int PRIMARY KEY,  
  18. sname varchar(20),  
  19. age numeric(2,0),  
  20. gen varchar(6))  
  21. --We can view the list of table properties "stud" using the following command...   
  22. sp_help stud  
  23. INSERT INTO stud VALUES(1,'Amit', 21, 'Male')  
  24. SELECT * FROM stud  
  25. INSERT INTO stud VALUES(2,'Anil',22,'Male')  
  26. SELECT * FROM stud  
  27. --ERROR - As duplicate values are not allowed, already a student exists with same rno  
  28. INSERT INTO stud VALUES(1,'Sunil', 22,'Male')  
  29. --ERROR - As NULL values are not allowed in a column with PRIMARY KEY constraint  
  30.  INSERT INTO stud(sname,age,gen) VALUES('Sunil', 22,'Male')  
  31. /*The reason for the error is that the column rno in the table stud is defined with Primary key constraint and Primary Key constraint doesn't allow NULL values, not even one null value.*/  
  32. DROP TABLE stud  
  33. --Adding the Primary Key constraint at table-level...  
  34. CREATE TABLE stud  
  35. (rno int,  
  36. sname varchar(20),  
  37. age numeric(2,0),  
  38. gen varchar(6),  
  39. CONSTRAINT pk1 PRIMARY KEY(rno))  
  40. sp_help stud  
  41. INSERT INTO stud VALUES(1,'Amit', 21,'Male')  
  42. SELECT * FROM stud  
  43. --ERROR - As duplicate values are not allowed, already a student exists with same rno  
  44. INSERT INTO stud VALUES(1,'Anil',21,'Male')  
  45. --ERROR - As NULL values are not allowed into the column that is defined with PRIMARY KEY constraint  
  46. INSERT INTO stud(sname,age,gen) VALUES('Anil',21,'Male')  
  47. sp_help stud  
  48. DROP TABLE stud  
  49. --Working with UNIQUE constraint...  
  50. --Adding the UNIQUE constraint at column-level as part of the table.....  
  51. CREATE TABLE stud  
  52. (rno int UNIQUE,  
  53. sname varchar(20),  
  54. age numeric(2,0),  
  55. gen varchar(6))  
  56. sp_help stud  
  57. INSERT INTO stud VALUES(1,'Amit',21,'Male')  
  58. SELECT * FROM stud  
  59. --ERROR - As duplicate values are not allowed, already a student exists with same rno  
  60. INSERT INTO stud VALUES(1,'Anil', 22,'Male')  
  61. INSERT INTO stud VALUES(NULL,'Sunil',22,'Male')  
  62. SELECT * FROM stud  
  63. --ERROR - As duplicate values are not allowed, already a student exists with one NULL value  
  64. INSERT INTO stud VALUES(NULL,'Hari',23,'Male')  
  65. DROP TABLE stud  
  66. --Adding the UNIQUE constraint as a table-level constraint.....  
  67. CREATE TABLE stud  
  68. (rno int,  
  69. sname varchar(20),  
  70. age numeric(2,0),  
  71. gen varchar(6),  
  72. CONSTRAINT unq1 UNIQUE(rno))  
  73. sp_help stud  
  74. INSERT INTO stud VALUES(1,'Amit',21,'Male')  
  75. SELECT * FROM stud  
  76. --ERROR - As duplicate values are not allowed, already a student exists with same rno  
  77. INSERT INTO stud VALUES(1,'Anil',22,'Male')  
  78. INSERT INTO stud(sname,age,gen) VALUES('Anil',22,'Male')  
  79. SELECT * FROM stud  
  80. --ERROR - As duplicate values are not allowed, already a student exists with NULL rno  
  81. INSERT INTO stud VALUES(null,'Harry',22,'Male')  
  82. --ERROR - As duplicate values are not allowed, already a student exists with NULL rno  
  83. INSERT INTO stud VALUES(NULL,'Harry',22,'Male')  
  84. --ERROR - As duplicate values are not allowed, already a student exists with NULL rno  
  85. INSERT INTO stud(sname,age,gen) VALUES('Harry',22,'Male') 
If you have any questions, please send an email to - [email protected]