-1

I have three tables, and want to update some fields in these tables but the query doesn't work

This is my Query:

UPDATE 
customers cust
    left JOIN
contract cont ON cust.Customer_id = cont.Customers_Customer_id
    left JOIN
payments pay ON cust.Customer_id = pay.Customers_Customer_id
SET 
cust.Customer_Name = 'Sara',
cust.Customer_id = '1201941718', 
cust.Customer_Mobile1 = '01258863956',
cust.Customer_Phone = '0224598755', 
cust.Customer_Mail = 'sara@gmail.com', 
cont.Customers_Customer_id = '1201941718', 
pay.Customers_Customer_id = '1201941718'
where cust.Customer_id = '2';

The error:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (alhawel.payments, CONSTRAINT fk_Payments_Customers1 FOREIGN KEY (Customers_Customer_id) REFERENCES customers (Customer_id) ON DELETE NO ACTION ON UPDATE NO ACTION)

kchason
  • 2,836
  • 19
  • 25
Shahenda
  • 3
  • 2
  • This error is pretty self-explanatory, don't you think? – Alon Eitan Nov 14 '17 at 13:20
  • I update the foreign key but i don't know why this message appears? – Shahenda Nov 14 '17 at 13:30
  • This is data integrity issue, you set a foreign key constraint in the `pay` table that preventing you from updating the rows. I don't know the structure or the logic behind your DB structure so I can't be more specific unfortunately – Alon Eitan Nov 14 '17 at 13:33

2 Answers2

1

You have one or more payments associated to customer_id = 2

When you update customer_id = 2 to customer_id = '1201941718' that payment become orphan and the FK constraint detect the integrity violation.

What you can do is:

  • insert new Customer_id = '1201941718'
  • update payments to Customer_id = '1201941718'
  • delete Customer_id = 2

But in db you shouldn't change the id of a record. There is no benefit for it . If you need add some metadata to the entity add another field and leave the PK alone.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Not an expert, so I'm not sure about it - But what about `ON UPDATE CASCADE`? Would it not then update the referencing rows as well? – Alon Eitan Nov 14 '17 at 13:58
  • 1
    @AlonEitan That could work, here is an answer related to that topic https://stackoverflow.com/questions/36807004/how-to-update-primary-key – Juan Carlos Oropeza Nov 14 '17 at 14:02
0

I believe it isn't working because of the ON UPDATE NO ACTION in the foreign key. When you are updating the Customer_id in the Customer table, the foreign keys pointing to that will no longer be valid.

This answer deals with ON DELETE NO ACTION but it applies to update as well. I think you will need to modify the constraint to fix this error (which may not be the right thing to do for other reasons, knowing more about the schema may get a better answer).

xHergz
  • 11
  • 1
  • 1