educative.io

We cannot delete or update the record of the referenced relation if the corresponding record exists in the referencing relation

Courses
,
#1

We cannot delete or update the record of the referenced relation if the corresponding record exists in the referencing relation.

This is mentioned in Integrity Rules and Constraints chapter in Module1 but it is not correct according to me as we can update the referenced table record even if some record in another table is referencing it

Course: https://www.educative.io/collection/10370001/5119687241236480
Lesson: https://www.educative.io/collection/page/10370001/5119687241236480/5885999802482688

#2

Hello @ahmed_anwar,
A logical rule about the values in one or more columns in one or more tables is known as a foreign key constraint, also known as a referential constraint or a referential integrity constraint.

For instance, a set of tables may share information about vendors for a firm. A vendor’s name may occasionally change. We can define a referential constraint stating that the supplier’s ID in a table must match a vendor ID in the supplier information. The insert, update or delete operations that would typically result in the absence of vendor information are prevented by this constraint.
I hope it will help.
Happy learning

#3

I am asking about updation part. We can update the record even it is referenced in other table.

#4

any reply?

#5

is there any one from educative team to answer it?

Course: https://www.educative.io/collection/5352985413550080/6335716377231360
Lesson: https://www.educative.io/collection/page/5352985413550080/6335716377231360/5956068212473856

#6

@Muhammad_Ali_Shahid

#7

Hi @ahmed_anwar
Referential Integrity Constraint ensures that there must always exist a valid relationship between two relational database tables. This valid relationship between the two tables confirms that a foreign key exists in a table. It should always reference a corresponding value or attribute in the other table or be null.
Let’s take an example
Consider an Employee and a Department table where Dept_ID acts as a foreign key between the two tables

Screenshot 2022-09-02 at 2.34.35 PM
Screenshot 2022-09-02 at 2.34.35 PM1244Ă—858 33.1 KB

Screenshot 2022-09-02 at 2.34.44 PM
Screenshot 2022-09-02 at 2.34.44 PM1244Ă—582 14.2 KB

In the above example, Dept_ID acts as a foreign key in the Employees table and a primary key in the Department table. Row having DeptID=4 violates the referential integrity constraint since DeptID 4 is not defined as a primary key column in the Departments table.

So the above case shouldn’t arise so taking care of the above-mentioned issue Yes we can update the record.

Hope it will help , Thank you.

1 Like
#8

Let’s say we have a record in the Department table with Dept_Id=4 and we update that record by changing its name. Will it allow me to do it or not? If you read my first question, I am just asking about the update stuff.

Course: https://www.educative.io/collection/5352985413550080/6335716377231360
Lesson: https://www.educative.io/collection/page/5352985413550080/6335716377231360/5982697882124288

#11

Hi @ahmed_anwar
As you asked in question 1: Can we update the referenced table record even if some record in another table is referencing it?
In the above tables, we have Employees Table which is a referencing table, and Department Table which is a referenced table. There are certainly different possibilities which come under your question, let me explain them one by one:

  • If we update the primary key of the Department Table which is Dept_ID. Let’s change the Dept_ID 1 to 10 then it violates the integrity because in Employee Table it acts like a foreign key and in Employee Table we have 1, not 10.
  • If we update the other columns of the Department Table which are not primary keys there like Dept_Name. Let’s change the Dept_Name of 1 from Sales to Accounting then it will not violate the integrity because in Employee Table it does not act like a foreign key and in Employee Table we still have 1 which is now pointing to the Accounting department instead of Sales.

Hope it will help now, Thank you.

1 Like
#12

First of all, we can’t update the PK of a table and it will not let us do it, even if it is referenced or not. Correct me If i am wrong

#13

Yes, you are right we can’t update PK because it is against the integrity rule.