Entity integrity is crucial for effective database construction. It involves applying a unique primary key to every table, preventing duplicate records and ensuring each record can be specifically identified. The primary key must be non-null and unique, or else the integrity of the database is compromised.
Entity integrity is one of the main rules for effective database construction. It refers to the process of applying a primary key to every table in a database, where the key must be a row or a combination of rows that are unique non-null values. Maintaining entity integrity, each record in each table of a database can be specifically identified via the primary key. This prevents duplicate records and other problems that could indirectly compromise the integrity of the database. In the absence of entity integrity, entries in a database can accidentally overlap, as it will not be possible to extract a specific entry from the table.
When considering the first part of entity integrity, a “unique” value, it is important to understand the concept of a primary key. A primary key in a database is a value used to specifically identify each individual record. To be effective, a primary key must be unique; in other words, it must be impossible to retrieve multiple records by looking for a single primary key. A Social Security number would be a good primary key for a database focused on individual personal records in the United States, as no two people ever have the same number. Anyone searching the database for a specific Social Security number would, by definition, only retrieve a single record at a time.
To see the problems that arise in the absence of entity integrity through unique primary keys, one might imagine a database in which an individual’s last name is used as the primary key. Since more than one person may share a last name, entering “Smith” or “Jones” as search criteria could yield dozens of records. If this happens, the integrity of the database is in question because the end user no longer has a method to retrieve individual records.
The second part of entity integrity states that the value chosen as the primary key must never be a null or empty value for any item in the database. If the primary key value remains null for one or more records in the database, some records will become impossible to retrieve, because a null value is not sufficient to uniquely identify these records. In other words, if a Social Security number is used as the primary key and an employee is not from the United States and therefore does not have one, the foreign employee’s record will be impossible to identify using the primary key, invalidating the integrity of the database .
Protect your devices with Threat Protection by NordVPN
