How to delete a row ONLY in parent table, which is referenced by a Foregin Key from the child table

Question

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

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

Is it possible to achieve this?

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

Galingale

If you want to delete part of your data structure, but also keep parts of it, consider a soft delete. That is a deleted field in your database, and then use views or where clauses to use only rows where deleted = 0. You can also have the foreign key as a composite key of both the id and the deleted field, then use on update cascade to ensure the child records become soft deleted too. (Also, why is this marked as Four different RDBMS? Are you really using all four?) – Favouritism

Maintain what data in the child table? The whole row including the value in the FK column? That would be contradicting the FK constraint. – Odericus

Answer 1

It is possible with some agreements in your data. To maintain child table data you'll have to do ON DELETE SET NULL. This will leave data, but set FK to NULL value (in child table). And that is because of data-integrity: while you can keep your data, your FK can not refer to non-existent row of parent table in terms of enforcing FK constraint. Thus, it will be set to NULL by this.

If you want to "save" value of FK - then you definitely should not use FK at all because such behavior violates what FK is. So then just don't use that constraint, but be aware of possible integrity fails.

Ortiz

Answer 2

The point of a foreign key constraint is to prevent orphan records in the child table. So, no, it's not possible to do that, unless you drop the foreign key relationship.

If you rely on 'ON DELETE CASCADE', then deleting the parent record will result in all the corresponding children to be deleted.

If you want to delete the parent, but keep the children, you need to drop the foreign key constraint, or set the constraint to be '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 foreign key column value will be set to NULL.

Devotee

@Alexander, logically I agree with Mark. what's the point of having a foreign key if you circumvent its purpose through normal operations? You're better off with no foreign key and doing your logic to verify the existence of data in the parent table within the code base. – Fontes

I modified my answer to account for ON DELETE SET NULL. – Devotee

Thank you @MarkJ.Bobak for your answer, but i accepted it as FIFO rule :) Thanks for your reply. – Galingale

Answer 3

delete a row ONLY in parent table, which is referenced by a Foregin Key from the child table

If Multiple table has been mapped in one table in that case all foreign key i.e :-

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

Ivories