What is Entity Integrity?

When working with relational databases, data accuracy and consistency are paramount. When we talk about maintaining the integrity of the data within a relational database, several types of integrity come into play. One of the most fundamental is entity integrity.

This concept is crucial for ensuring that each record within a database table is uniquely identifiable, which in turn supports the reliability of the data. In this article, we will delve into what entity integrity is, why it matters, and how it is implemented in practice. We’ll also look at an example to clarify the concept further.

Understanding Entity Integrity

Entity integrity is a fundamental principle in relational databases, designed to ensure that each row in a table is uniquely identifiable. This is achieved by enforcing a constraint on the primary key of the table. A primary key is a column or a combination of columns that uniquely identifies each record within a table. The entity integrity rule states that:

Every table must have a primary key – No table in a relational database can exist without having a primary key.
The primary key must have unique values – Each value in the primary key column(s) must be unique across all records.
The primary key cannot contain NULL values – Since a primary key uniquely identifies a row, allowing NULL values would defeat its purpose, as NULL indicates the absence of a value.

While it’s certainly possible to create tables that don’t adhere to these rules, they do ensure that every row in each table is distinct and can be referenced precisely, which is crucial for maintaining the integrity of the data across the database.

Why Entity Integrity Matters

Entity integrity is important to relational databases for several reasons:

Data Consistency: By ensuring that each record can be uniquely identified, entity integrity helps maintain consistent and accurate data. This prevents errors such as duplicate entries or records with missing identifiers.
Efficient Data Retrieval: When each record is uniquely identifiable, queries run more efficiently. The database can quickly locate and retrieve the specific data requested, improving performance.
Data Relationships: In a relational database, tables are often linked by foreign keys, which reference primary keys in other tables. Entity integrity ensures that these relationships are maintained correctly, preventing issues like orphaned records.
Data Accuracy: With a strict entity integrity rule, the risk of inaccurate or incomplete data entering the system is minimized. This leads to more reliable data for analysis and decision-making.

Implementing Entity Integrity in Databases

Implementing entity integrity typically involves the following steps:

Defining the Primary Key: When designing a table, identify the column(s) that will serve as the primary key. This could be a single column or a combination of columns.
Enforcing Uniqueness: Use database constraints to enforce the uniqueness of the primary key. Most database management systems (DBMS) automatically enforce this when a primary key is defined.
Preventing NULL Values: Ensure that the primary key column(s) are defined as NOT NULL, which prevents the insertion of NULL values into the primary key.
Testing the Constraints: Regularly test the database to ensure that entity integrity is maintained, especially after any changes to the database schema or data.

An Example of Entity Integrity

Let’s consider a simple example involving a database used by a retail company to manage customer orders.

Scenario: A retail company stores customer order details in a table called Orders. This table includes columns such as OrderID, CustomerID, OrderDate, and TotalAmount.

Table Structure:

OrderID	CustomerID	OrderDate	TotalAmount
1001	C101	2024-08-01	$250.00
1002	C102	2024-08-03	$150.00
1003	C101	2024-08-05	$300.00

Primary Key: The OrderID column is designated as the primary key because it uniquely identifies each order placed by customers.

Entity Integrity Rule Application:

Primary Key: The table has a primary key.
Uniqueness: Each OrderID value is unique, ensuring that no two orders have the same identifier.
Non-Null Values: The OrderID cannot be NULL, guaranteeing that every order has a distinct identifier.

Here’s an example of SQL code that creates the above table (the one with the primary key):

CREATE TABLE Orders (
    OrderID INT NOT NULL,                 -- Set to NOT NULL to disallow NULL values
    CustomerID VARCHAR(10) NOT NULL,      
    OrderDate DATE NOT NULL,              
    TotalAmount DECIMAL(10, 2) NOT NULL,  
    PRIMARY KEY (OrderID)                 -- Define OrderID as the Primary Key to enforce Entity Integrity
);

Many RDBMSs also allow us to define the primary key in the column definition itself:

CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,     -- Set to NOT NULL and as the primary key
    CustomerID VARCHAR(10) NOT NULL,      
    OrderDate DATE NOT NULL,              
    TotalAmount DECIMAL(10, 2) NOT NULL
);

This eliminates the need to define it at the end, like in the first script.

Violation of Entity Integrity

Imagine a scenario where the OrderID column allows NULL or duplicate values:

OrderID	CustomerID	OrderDate	TotalAmount
NULL	C101	2024-08-01	$250.00
1002	C102	2024-08-03	$150.00
1002	C101	2024-08-05	$300.00

In this case, the database cannot distinguish between the two orders with the same OrderID, and the order with a NULL OrderID cannot be uniquely identified. This situation could lead to confusion, data corruption, and unreliable reporting.

By enforcing entity integrity, the database prevents such issues, ensuring that each order is properly recorded and easily retrievable.

Database.Guide