Referential integrity

A relational database concept , My SQL

1. Referential Integrity

2. Referential integrity is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key . Primary key field changes must be applied to all foreign keys, or not at all. The same restriction also applies to foreign keys in that any updates (but not necessarily deletions) must be propagated to the primary parent key. What is Referential Integrity?

3. Databases use tables to organize the information they contain. They are similar to spreadsheets, such as Excel, but vastly more capable for advanced uses . Databases function with the use of primary keys and foreign keys, which maintain the relationship between the tables.  Contd …

4. The primary key of a database table is a unique identifier assigned to each record. Each table will have 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.  Some database software—such as Microsoft Access—assigns the primary key automatically, but the random key has no real meaning. Primary Key

5. 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, and 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. Foreign Key

6. Using a relational database management system with referential integrity offers several advantages : P revents the entry of duplicate data Prevents one table from pointing to a nonexistent field in another table. Guaranteed consistency between "partnered" tables Advantages

7. Prevents the deletion of a record that contains a value referred to by a foreign key in another table Prevents the addition of a record to a table that contains a foreign key unless there is a primary key in the linked table Contd …

8. Another example of Referential Integrity is Employee and Department relationship. If we have dept_id as foreign key in Employee table than by using referential integrity constraints we can avoid creating  Employee without department or non existing department. In short Referential Integrity makes primary key foreign key relationship viable. Let's first create Employee and Department table with primary key, foreign key and referential Integrity constraints. Referential Integrity examples on MySQL Tables

9. SQL statements in previous slide will create both Department and Employee table. dept_id is now foreign key in Employee table. In this SQL, while creating foreign key we have specified ON DELETE clause which tells, what needs to done when a record from parent table is deleted. CASCADE referential action allows to  delete or update all matching rows from child table , after deleting a record in parent table. This way Referential Integrity preserve data integrity of relationship. Contd …

10. In the previous slide, When we inserted first record in Department table it ran fine but when we insert a record in Employee table with dept_id = 2 which is  not present in Department i.e. parent table,  failed to Referential integrity or foreign key constraint check. Contd …

11. THANK YOU