You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Referential Integrity
In order to remove redundant data in database tables, the design of the database is normalized - almost always resulting in multiple tables linked together using foreign keys. This introduces a new problem because the database engine will need to keep track of the relationship to ensure that each foreign key must actually link to a valid value in another table. This is call referential integrity.
When referential integrity is enforced, all foreign keys are properly matched with existing data in the referenced table. This simply means that if Table A contains a foreign key that references to a record in Table B, the record in Table B must exist.
For example, consider the following tables:
product table
category table
Let's assume that the above tables contain the following data:
What happens if the user tries to create a new record in the product table with a category_id of "0003"? There is no such record in the category table. As such, such a record in the product table will contain an invalid reference to the category table.
What happens if the user tries to delete the 1st record in the category table? Deleting the 1st record will effectively invalidate the category reference in the first 2 records in the product table.
If referential integrity is applied, both the above transactions would not be allowed by the database engine. The database engine enforces referential integrity through the use of referential constraints and triggers. Referential constraints prevent situations as describe in scenario 1 above from happening while triggers prevent situations as described in scenario 2 above from happening.
When referential integrity is enforced, all foreign keys are properly matched with existing data in the referenced table. This simply means that if Table A contains a foreign key that references to a record in Table B, the record in Table B must exist.
For example, consider the following tables:
product table
| column | |
|---|---|
| product_id | primary key |
| description | |
| category_id | foreign key |
category table
| column | |
|---|---|
| category_id | primary key |
| description |
Let's assume that the above tables contain the following data:
| product_id | description | category_id |
|---|---|---|
| 0001 | table | 0001 | 0002 | chair | 0001 | 0003 | fan | 0002 |
| category_id | description |
|---|---|
| 0001 | furniture |
| 0002 | electrical |
What happens if the user tries to create a new record in the product table with a category_id of "0003"? There is no such record in the category table. As such, such a record in the product table will contain an invalid reference to the category table.
What happens if the user tries to delete the 1st record in the category table? Deleting the 1st record will effectively invalidate the category reference in the first 2 records in the product table.
If referential integrity is applied, both the above transactions would not be allowed by the database engine. The database engine enforces referential integrity through the use of referential constraints and triggers. Referential constraints prevent situations as describe in scenario 1 above from happening while triggers prevent situations as described in scenario 2 above from happening.
Advertising
Advertising: