Entity Integrity Constraints

Before jumping directly to entity integrity constraints, let’s discuss what integrity constraints are in DBMS.

In DBMS, integrity constraints are used to ensure data consistency and accuracy in any relational database. Integrity constraints are basically pre-defined sets of rules to maintain the integrity of the data in the table. Now we need to understand what data integrity means. So, data integrity can be defined as the overall accuracy or completeness of the data. To create better databases, we need to enforce data integrity wherever it is possible. If we make any changes to our database, then Integrity Constraints ensure us that any changes, like deletion, update, or insertion, do not result in any loss in data consistency. Integrity Constraints are of four types, which are mentioned below:

1. Domain Constraint
2. Entity Integrity Constraints
3. Referential Integrity Constraint
4. Key constraint

In this article, we discuss entity constraints.

Entity Integrity Constraints

The Entity Integrity Constraint is related to the primary key. The entity integrity states that the primary key cannot be null in any table. Now the question arises in our mind, why is the primary key value not null in any table? The answer to this question is that a primary key is used to identify unique individual rows in a table, and if the value of the primary key is null, then it will be difficult for us to identify those rows. Also, a primary key is used to make relationships with other tables. Except for the primary key, any column in any table can have a null value. Let’s understand this concept more clearly using the example given below.

For example, let's consider an employee table with emp_id, emp_name, and emp_salary.

Let's explore this table. As Emp_id is our primary key and the value of the primary key can not be null, this table is not allowed as there is no emp_id for emp_name Bikram.

Create Table Student (
Student_id varchar(5),
Name varchar(20) not null,
Dept_name varchar (20),
Primary key (student id)
);

As you see above, we created a table named "Student". In this table, we have Student_id, Name (as you have seen, not null with it, which means the Name column doesn’t allow any NULL value), and Dept_name. Student_id will be the primary key in this table. Let’s explore another example.

CREATE TABLE Employees (
Employee_id int NOT NULL,
Employee_name varchar(40) NOT NULL,
Age int,
PRIMARY KEY (Employee_id)
);

Now let's see how we can insert values into this table and we can also check whether the inserted values are addable or not.

INSERT INTO Employees VALUES
(‘101’, ‘Arnav’, ‘22’),
(‘102’, ‘Aman’, ‘25’),
(NULL, NULL, NULL);

This insertion is not allowed as we cannot put a NULL value in the employee ID as it is our primary key. Let’s see another example.

INSERT INTO Employees VALUES
(‘101’, ‘Arnav’, ‘22’),
(‘102’, ‘Ayush’, ‘12’),
(‘103’, ‘Ramesh’, ‘30’);

This insertion is allowed as all values in the primary key are not NULL and it also contains unique values. So, this insertion is good to go.

INSERT INTO Employees VALUES
(‘101’, ‘Arnav’, ‘22’),
(‘102’, ‘Ayush’, ‘12’),
(‘103’, ‘Ramesh’, NULL);

This insertion is also good to go as we can put age as NULL values because it is not our primary key and in the code snippet it is mentioned that NULL values are applicable (as a default value).

INSERT INTO Employees VALUES
(‘101’, ‘Arnav’, ‘22’),
(‘102’, ‘NULL’, ‘12’),
(‘103’, ‘Ramesh’, ‘30’);

This code is also good to go as none of the columns contain NULL values. You may get confused by seeing "NULL" in the Name column of Employee ID 102. This is accepted because "NULL" is placed in a string in this. Basically, it is not a NULL value, it is a name, "NULL" (yes, it is a name as it is written in double-quotes).

Entity Integrity Constraints are of two types, as mentioned below.

• Unique Constraint
• Primary key Constraint

Unique constraints are those that prohibit duplicate values in any column of a table; they can allow NULL values but not duplicate values. This unique constraint can be applied in one column or more than one column.

Primary key constraints are those constraints that are associated with the columns of the table, and they do not allow any NULL values in the table so we cannot maintain a NULL value in our primary key column. A primary key constraint can be said to be a combination of unique and NOT NULL constraints together.

The advantage with an Entity Integrity Constraint

When we go through our table in the Database Management System, we see there are no duplicate values in our table and no NULL value in our primary key constraint. Then we can easily identify unique values and easily make relations with other tables as foreign key values can take proper references to primary keys.

Refer to Scaler Topics to understand all types of integrity constraints.

Author: Arnav bhardwaj