CODEWITHSUNDEEP
mysqlsqldatabase
kiki
kiki

Reputation: 81

Understanding referential integrity in databases

I just started learning databases. My professor mentioned the term "referential integrity" and I'm trying to understand it.

Here's my understanding. If I have

Table Manager: Manager_id , Manager_name Table Employee: Employee_id , Manager_id , Employee_name

Manager_id is the primary key for "Manager table" & foreign key for "Employee table"

If I delete/update any Manager_id from Manager then all the entries having that manager id will get deleted/updated. Thats cascading update or delete.

But what if I try to delete or update manager_id in the "employee table" ? Will it correspondingly delete the entries from Manager table?

Upvotes: 0

Views: 66

Answers (2)

Randy
Randy

Reputation: 16677

The 'cascade' part goes from the Primary Key towards the Foreign Key, but not the other way around.

When you delete Manager1 let's say, then it makes no sense for Employee1 to have Manager1 as a manager, since they are no longer defined.

But when you change Employee1's manager from Manager1 to Manager2 - then that is still fine assuming both managers still exist.

Upvotes: 1

Kaspars Ozols
Kaspars Ozols

Reputation: 7017

If I delete/update any Manager_id from Manager then all the entries having that manager id will get deleted/updated. Thats cascading update or delete.

This happens only if you have defined UPDATE/DELETE rules for Foreign Key. By default, cascading UPDATES/DELETES are disabled as it might cause unexpected changes in your data.

But what if I try to delete or update manager_id in the "employee table" ? Will it correspondingly delete the entries from Manager table?

It will delete only single employee as related manager could be referenced by some other employee as well.

Upvotes: 2

Related Questions