1

i am trying to delete a row of information from a parent table without deleting anything from the child table. the foreign key constraint is set to delete no action. because i want to delete the information while leaving the corresponding information in the child table intact. i ant to be able to do this without changing it to delete cascade.......can anybody help me........p.s my database is literally a table consisting of parents and the child table is also literally children....HELP. i have tried doing this but i keep getting the error message. Foreign key constraint prevents update or delete.

berty
  • 2,178
  • 11
  • 19
Thomas Darko
  • 11
  • 1
  • 3
  • 2
    You'd have to remove the `foreign key` constraint then... But why would you want to leave a child record without a corresponding parent record? That's why the constraint is there to begin with. – sgeddes Aug 01 '16 at 19:04
  • i wanted to but have been told it can be done without removing the constraint.........thanks by the way. – Thomas Darko Aug 01 '16 at 19:06

1 Answers1

2

You can define the child table Id to be NULLable and define the foreign key in ON DELETE SET NULL mode. It means that when the parent record is deleted, children records or updated to replace the deleted Id by NULL. For more information, view this page : https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

berty
  • 2,178
  • 11
  • 19