Databases use tables to organize the information they contain. They are similar to spreadsheets, such as Excel, but vastly more capable for advanced users. Databases function with the use of primary keys and foreign keys, which maintain the relationship between the tables.
Referential integrity is a database feature in relational database management systems. It ensures the relationships between tables in a database remain accurate by applying constraints to prevent users or applications from entering inaccurate data or pointing to data that doesn't exist.
Primary Key
The primary key of a database table is a unique identifier assigned to each record. Each table has one or more columns designated as the primary key. A Social Security number can be a primary key for a database listing of employees because each Social Security number is unique.
However, because of privacy concerns, an assigned company ID number is a better choice to function as a primary key for employees. Some database software, such as Microsoft Access, assigns the primary key automatically, but the random key has no real meaning. It is better to use a key with meaning to the record.
The simplest way to enforce referential integrity is not to allow changes to a primary key.
Foreign Key
A foreign key is an identifier in a table that matches the primary key of a different table. The foreign key creates the relationship with a different table. Referential integrity refers to the relationship between these tables.When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table.
Example of Referential Integrity Rules
Consider, for example, the situation where you have two tables: Employees and Managers. The Employees table has a foreign key attribute entitled ManagedBy, which points to the record for each employee’s manager in the Managers table. Referential integrity enforces the following three rules:
You cannot add a record to the Employees table unless the ManagedBy attribute points to a valid record in the Managers table. Referential integrity prevents the insertion of incorrect details into a table. Any operation that doesn't satisfy the referential integrity rule fails.
If the primary key for a record in the Managers table changes, all corresponding records in the Employees table are modified using a cascading update.
If a record in the Managers table is deleted, all corresponding records in the Employees table are deleted using a cascading delete.