Bug #44116 Allow ON CASCADE SET NULL with multi-column NOT NULL FKs

• Submitted: 6 Apr 2009 17:44
• Modified: 28 Sep 2010 14:20
• Reporter: Jaka Jančar
• Status: Won't fix
• Category: MySQL Server
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[6 Apr 2009 17:44] Jaka Jančar

Description:
It's impossible to set ON CASCADE SET NULL on a foreign key if any of the constrained columns are defined as NOT NULL.

I propose allowing it, as long as at least one column is nullable, possibly with a special keyword.

How to repeat:
Suppose we have a table with a composite PK (perhaps because of partitioning reasons) consisting of groupId and localId:

CREATE TABLE a (
    groupId INT NOT NULL,
    localId INT NOT NULL,
    
    PRIMARY KEY (groupId, localId)
) ENGINE=InnoDB;

We have another table whose rows may contain references to rows from the first table. We however know that the referenced rows will always belong to the same group, and thus want to a) enforce this and b) avoid redundant columns. We do:

CREATE TABLE b (
    groupId INT NOT NULL,
    localId INT NOT NULL,
    
    relatedARowLocalId INT NULL,
    
    PRIMARY KEY (groupId, localId),
    FOREIGN KEY (groupId, relatedARowLocalId) REFERENCES a(groupId, localId) ON DELETE SET NULL
) ENGINE=InnoDB;

However when creating the second table, InnoDB will report:
> You have defined a SET NULL condition though some of the columns are defined as NOT NULL.

Suggested fix:
If the proposed change was implemented, when the parent row was deleted, the nullable columns of the child table (relatedARowLocalId) would be SET NULL, and others (groupId) would be left alone.

[6 Apr 2009 17:45] Jaka Jančar

I incorrectly set severity.

[28 Sep 2010 14:20] Susanne Ebrecht

Hello,

I am looking for the sense here.

You are only allowed to choose one of these:

DELETE CASCADE
DELETE RESTRICT
DELETE SET NULL
DELETE NO ACTION

It's not possible to mix.

When I use DELETE RESTRICT (which should be default given by SQL Standard) then
nothing will get deleted as long as you have referenced values.

When you use DELETE CASCADE then all rows in all tables that are referenced and have the value will get deleted.

NOT NULL is a constraint.

When you set NOT NULL constraint to a table then of course you can't use SET NULL.

All this is given that way in SQL standard and MySQL is behaving standard conform here.