I have a Customers table and a Jobs table. The Jobs table contains a foreign key relation on the Customer ID, with each job able to have one customer.
When I try to delete a customer, I get the error "Integrity constraint violation (#23000)".
I then changed the relationship in the database to "ON DELETE CASCADE ON UPDATE CASCADE", however I still get the integrity constraint violation.
Will the problem be fixed if I re-run the CRUD creation for those tables, or do I have to add code to my model to support the cascading delete of the Job record on deletion of the related Customer?
I will suggest you figure out what is wrong with your database or tables perhaps you can try recreate the tables, let your database take care of it instead of handling it yourself.
in case you wanna do it manually you can make use of the ActiveRecord hooks like beforeDelete/afterDelete.
I was hoping that changing the ON DELETE and ON UPDATE settings in my InnoDB table would be enough but as I say I still get the error message about the constraint. I guess the real question is whether the error is coming from Yii2 or from the MySQL database itself.
You were both right, there was an additional table relying on the Jobs table that also needed to have its ON actions set to CASCADE. It’s all working now. Thanks for the help folks!