What Is Referential Integrity and How Can a Programmer Avoid These Problems?
Referential integrity is a constraint applied to a relational database -- a database in which the data and relations between them are organized in tables of rows and columns -- so that inconsistent data isn't entered. Most relational database management systems define referential integrity rules that programmers apply when creating a relationship between two tables.
Other People Are Reading
-
Referential Integrity Rule
-
Essentially, referential integrity states that a database cannot contain any unmatched foreign key values. A foreign key is a column in a database table that contains values also found in the primary key column -- a unique identifier that labels a row in the table -- in another table. For example, consider a database table named “department”, in which a column called “dept-no” is the primary key. It relates to another table named “employee”, in which “dept_no” is a foreign key. An employee cannot belong to a particular department if the corresponding “dept_no” doesn’t already exist in the “department” table. If the program to add employees enforces referential integrity, any attempt to insert an employee into an unknown department won't occur.
Benefits
-
Aside from ensuring that references between data are intact and valid, defining referential integrity in a database has numerous advantages. Referential integrity uses existing code in a database engine rather than requiring programmers to write custom program code from scratch. As a result, program development is faster, less prone to errors and consistent across multiple application programs that access a database.
-
Consequences
-
Unfortunately, programming languages typically lack a mechanism for enforcing referential integrity and, even when a relational database management system supports such a mechanism, programmers often fail to use it. The consequence of ignoring referential integrity is program code that contains defects, or bugs, performs poorly and is difficult to extend.
Enforcement
-
Programmers can enforce referential integrity -- and avoid “orphan” records in a database -- by enabling it for a relationship between two tables. In Microsoft Access, for example, enforcing referential integrity causes any operation that would violate referential integrity to be rejected. Such operations include updates to a database that change the target of a reference, or deletions that remove the target of a reference. Furthermore, Microsoft Access also includes a set of options, known as “cascade” options. These options allow referential updates and deletions to be propagated throughout the database, so that all related rows are changed accordingly.
-
