RDBMS – Database Integrity

Database integrity refers to the accuracy or correctness of data in a database. Before deep diving into database integrity, ensure that you have gone through following article:

To enure the data integrity, constraints come into the picture. Constraints are rules applied on the data so as to ensure data integrity.

For example, let us say we have a table employee having columns Emp_Id, Name, Address, Pincode, and Salary. We may have many constraints on the table:

Name must have character only.
Pincode should have integer value and it must consist of six digits.
Salary must consist of numbers.
Emp_Id must be integer.

All these are constraints on the data. Constraints are decided based on rules and demands of the business.

Table of Contents

Keys

Keys are vital part of database and used to apply various types of constraints on the database. A key is a minimal set of columns that uniquely identifies or functionally determines every column value in a row.

Consider Employee table as discussed above, here Emp_Id is the key as it uniquely identifies a row in a table.

Various types of Keys:

Super Key: A super key is a set of columns that uniquely identifies every row in a table. It is different from key as it is not a minimal set of columns.For example: in the above Employee table example, Emp_Id and Name together makes a super key.
Composite Key: A key consisting of two or more columns is called as a composite key.There are situations when a single column cannot make up a key. This is because a single column cannot uniquely identify every row in the table. Instead, we need to have two or more columns together in order to identify every row in the table uniquely.
For example, Let us consider table SC that tells us which student study in which class.
Table SC

As we can see, neither the Student_Id nor the Class_Id can identify a row in the table uniquely. However, the two of them together can easily identify any row in the table uniquely. Hence, it is a composite key.
Candidate Key: A table can have more than one set of columns that could be chosen as the key. These are called candidate keys. Let us consider the table Employee having columns: Emp_Id, Name, Address, Pincode, Passport_Number and Salary.
Here either Emp_Id or Passport_Number can act as key as both can uniquely identify a record in table.
Primary Key: The primary key identifies every record in a table uniquely. If a table has two or more candidate keys, then we have to decide which of them becomes the primary key. Emp_Id, Passport_Number are some of the examples of primary key.
Sometime table does not have column which can be designated as primary key. In such cases, we may need to introduce an additional column which contains unique values, such a key is called surrogate key.
Alternate Key or Secondary Key: Alternate keys or secondary keys are keys that may or may not identify a record uniquely, but help in faster searching.
Any candidate key which is not a primary key is an alternate key. For example, In Employee table there are two candidate keys i.e. Emp_Id and Passport_Number. If we make Emp_Id as primary key, then Passport_Number would be alternate key.
Foreign Key: A foreign key is a set of columns in one table, which is a primary key in another table. It acts as link between two tables.
Earlier we have seen SC table containing Student_Id and Class_Id. This table tells us which student study in which class.Table SC

As relational database works by dividing data into different tables; we would have different tables for Student and Class. These tables would contain information about students and classes respectively. The SC table is used to establish relationship between them. Let’s say we have two tables Student and Class.Table Student

Table Class

Student_Id is primary key in Student table
Class_Id is primary key in Class table.The concept of foreign key comes in to picture when we want to establish relationship between table SC and Student as well as Class tables. For a given Student_Id in SC table, there must be exactly one student row in the Student table. Similarly, for a given Class_Id in SC table, there must be exactly one class row in the Class table. Thus Student_Id and Class_Id are foreign keys in SC table. There can be multiple entries for same Class_Id in SC table, thus foreign key can have same value in a table.