How to delete a row ONLY in the parent table referenced by the Foregin key from the child table

Question

I want to delete a row / tuple from the parent table, but it gives an error message because it has a FOREIGN KEY link in its child table.

However, in my case, I want to delete the record only from the parent table and save the data in the child table .

Can this be achieved?

I know the use of ON DELETE CASCADE , but I want to know if there is a solution for the sequenario I described?

Answer 1

Perhaps with some conventions in your data. To save the child data table, you will need to do ON DELETE SET NULL . This will leave data, but set the FK value to NULL (in the child table). And this is because of data integrity: while you can store your data, your FK cannot refer to a non-existent row in the parent table in terms of enforcing FK restrictions. This way it will be set to NULL this.

If you want to “save” the FK value , then you should definitely not use FK at all, because this behavior violates what FK does. So, just do not use this restriction, but be aware of possible integrity.

Answer 2

The foreign key restriction point is to prevent orphan entries in the child table. Thus, no, this cannot be done unless you cancel the foreign key relationship.

If you rely on "ON DELETE CASCADE", deleting the parent record will delete all the corresponding child elements.

If you want to delete the parent element, but save it, you need to reset the foreign key constraint or set the constraint to "ON DELETE SET NULL". If you set "ON DELETE SET NULL", then when you delete the parent record, the child records will remain, but the value of the foreign key column will be set to NULL.

Answer 3

delete row ONLY in the parent table referenced by the Foregin key from the child table

If several tables were displayed in one table, in this case all foreign keys i.e.: -

 $table->integer('customer_id')->unsigned()->nullable(); $table->foreign('customer_id')->references('id') ->on('customers')->onDelete(`SET NULL`);