databasedev.co.uk - database solutions and downloads for microsoft access

Relational Database Design

Relational Database Entity Integrity

Entity Integrity ensures that there are no duplicate records within the table and that the field that identifies each record within the table is unique and never null.

The existence of the Primary Key is the core of the entity integrity. If you define a primary key for each entity, they follow the entity integrity rule.

Entity integrity specifies that the Primary Keys on every instance of an entity must be kept, must be unique and must have values other than NULL.

Although most relational databases do not specifically dictate that a table needs to have a Primary Key, it is good practice to design a Primary Key for each table in the relational model. This mandates no NULL content, so that every row in a table must have a value that denotes the row as a unique element of the entity.

Entity Integrity is the mechanism the system provides to maintain primary keys. The primary key serves as a unique identifier for rows in the table. Entity Integrity ensures two properties for primary keys:

  • The primary key for a row is unique; it does not match the primary key of any other row in the table.
  • The primary key is not null, no component of the primary key may be set to null.

The uniqueness property ensures that the primary key of each row uniquely identifies it; there are no duplicates. The second property ensures that the primary key has meaning, has a value; no component of the key is missing.

The system enforces Entity Integrity by not allowing operations (INSERT, UPDATE) to produce an invalid primary key. Any operation that creates a duplicate primary key or one containing nulls is rejected.

Additional Reading:

Domain Integrity: A domain defines the possible values of an attribute. Domain Integrity rules govern these values.