Cannot delete or update a parent row: a foreign key constraint fails

Question

User1132102078 posted

This is a scenario

Table (Telephone)

• id
• Number

Table (Person_has_Telephone)

• Person_id(PK)
• Telephone_id(PK)
  • Constraint - Table Telephone

....

Delete all Person_has_Telephone where Person_id = 1, 

Delete all Telephone where Id = Telephone_id from Person_has_Telephone <--- fail and got the above error..

* Telephone Table is the Parent table, isn't it?

What is wrong with it? How can I fix this?

Thanks

Answer 1

User269602965 posted

This design indicates Each Telephone can have one or more persons assigned to it.

Telephone is the parent table (ONE SIDE) and Person_had_Telephone is the child table (MANY SIDE).

So for a given telephone.id, are you trying to delete all the person records associated with that telephone.id?

(deleting each child record that matches a given telephone.id)

In SQL Delete syntax is usually

DELETE FROM TABLE X WHERE {WHERE filter criteria}

 

 

 

Answer 2

User753101303 posted

Hi,

Show really the full statement?

For now it seems you delete all telephones for a person and then delete some telephones but we don't know which criteria you used (could it be that you really try to delete them all?) Do you want to delete then all unused telephones?

It could be something such as :

DELETE Telephone FROM Telephone WHERE NOT EXITS(SELECT 1 FROM Person_Has_Telephone WHERE Telephone_id=id) that is you delete all telephones which are not found in the person_has_telephone table.

Answer 3

User364663285 posted

This is a scenario

Table (Telephone)

• id
• Number

Table (Person_has_Telephone)

• Person_id(PK)
• Telephone_id(PK)
  • Constraint - Table Telephone

....

Delete all Person_has_Telephone where Person_id = 1, 

Delete all Telephone where Id = Telephone_id from Person_has_Telephone <--- fail and got the above error..

* Telephone Table is the Parent table, isn't it?

What is wrong with it? How can I fix this?

Thanks

You have to delete the FK record first before you delete the current one, as it is being referenced by another table.