MySQL Bugs: #1538: DELETE IGNORE command

Bug #1538	DELETE IGNORE command
Submitted:	12 Oct 2003 9:06	Modified:	26 Nov 2005 13:24
Reporter:	Ronald van Raaij	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
I would like to see a "DELETE IGNORE" that works as (and imho completes the set) INSERT IGNORE or UPDATE IGNORE.

How to repeat:
At this moment queries that delete rows that have associated foreign key parent rows abort with an error. Adding the IGNORE option that is present for INSERT and UPDATE would simplify matters.

Can you please provide some examples?

I don't understand what is the reason of such feature. What should be behaviour of DELETE IGNORE for different ON DELETE definitions?

ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

It makes sence only for RESTRICT. But if there is RESTRICT it is probably used with purpose. Otherwise DBA can choose NO ACTION which will have same effect as your proposed DELETE IGNORE.

Please elaborate.

The "ON DELETE IGNORE" is in the table definition. That means a query will ALWAYS ignore the error. However in some cases you may want the query to fail and abort, whereas in other cases you may want the query to continue. When you change the table definition, the option is gone.

Ok. You make your point, but what about all of CASCADE, SET NULL, NO ACTION, RESTRICT ?

Imagine for example you have DELETE_Priv granted on table with ON DELETE CASCADE FK, but you don't have DELETE privilege granted on foreign tables.

What will happen in this case if you send DELETE IGNORE?

Foreign key constraints should work either for all rows, or for no rows at all. That is their purpose. So, if the only reason for your feature request is to allow to detele only some rows being referenced, I think, nobody will bother implementing this.

For all other purposes ON DELETE ... clause pointed our by Alexander Keremidarski is sufficient.

There are no other reasons to prevent deleting rows (other than permissions or rows being currently locked by other sessions), so I see no need for this feature. Even if it looks complementary to others already implemented...