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

  • 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

    Wednesday, September 10, 2014 4:54 AM

All replies

  • 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}

     

     

     

    Wednesday, September 10, 2014 11:22 PM
  • 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.

    Monday, September 15, 2014 7:51 AM
  • 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.

    Tuesday, September 16, 2014 4:15 AM