Bug #44699 During set up a foregin key NOT NULL and a constrains ON DELETE SET NULL-conflic
Submitted: 6 May 2009 16:01 Modified: 18 May 2010 12:44
Reporter: Dariusz Szulc Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.30 OS:Windows (Xp)
Assigned to: Assigned Account CPU Architecture:Any
Tags: foreign key, NOT NULL, set null, Validation

[6 May 2009 16:01] Dariusz Szulc
Description:
During designing database with  Inno DB type, I set up my foreign key on 'NOT NULL' then for the same foreign key I  set  constrain ON DELETE SET NULL, which - I guess - should couse a conflict.

Maybe some message with information about this situation will be  proper?

How to repeat:
I have no idea, I don't understand this field what's going about; sorry I'm non english player :P

Suggested fix:
Maybe some message with information about this situation will be  proper?
[7 May 2009 0:32] MySQL Verification Team
Thank you for the bug report.
[5 Jun 2009 17:53] Alfredo Kojima
A validation should be added for this situation.
[3 Nov 2009 17:36] Craig Fowler
Yeah, I'd like to chime in here and maybe provide a more concise description (also to say that this feature request is still valid in 5.2.6 alpha):

"Prevent impossible foreign key UPDATE/DELETE actions for InnoDB tables"

If a foreign key references a column that is a primary key or that is set 'Not Null' then only the RESTRICT or CASCADE actions should be available for that foreign key in the referencing table.

The other options are impossible and will result in a MySQL error on trying to use the CREATE or ALTER script generated by workbench, so there is nothing to gain from permitting these actions when they cannot lead to anything but an error scenario.

My personal recommendation would be to disable/grey out the impossible actions in the UI and add a small help link above the dropdowns titled something like "why are some of these unavailable?" that links to a help document explaining why they are disabled.
[18 Mar 2010 19:13] Matthew Turland
I agree with Craig Fowler. I believe this also occurs on 5.1.18 and can be caused by signed/unsigned column differences as well.
[18 May 2010 12:44] Susanne Ebrecht
This is a duplicate of bug #39124