Bug #62464 On Delete and On Update set to "SET NULL" conflict with "NOT NULL"
Submitted: 17 Sep 2011 16:32 Modified: 8 Feb 2014 1:26
Reporter: Artur Stępień Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.34, 5.2.35 OS:Any (Windows 7, Mac OS X)
Assigned to: CPU Architecture:Any
Tags: errno 150, ondelete, onupdate

[17 Sep 2011 16:32] Artur Stępień
Description:
WB makes possible to set On Delete and On Update to "SET NULL" on Foreign Key fields that are marked as "NOT NULL".

Synchronizing with database results in:
ERROR: Error 1005: Can't create table 'bsupport_pm.#sql-67c3_3ef94' (errno: 150)

Obviously i shouldn`t mark this field as NOT NULL but it is hard to find what couse errno 150 after making such small mistake

How to repeat:
- Create two tables with InnoDB storage engine
- Create column named COLUMN1 on table TABLE_A and mark it as NOT NULL
- Add foreign key on COLUMN1 to table TABLE_B and set On Update to SET NULL
- Synchronize with database

Suggested fix:
- Do not allow to chose "SET NULL" in On Update on foreign key that is build on column marked as "NOT NULL"
- or set more descriptive error message in such situations
[24 Sep 2011 11:07] Valeriy Kravchuk
Thank you for the bug report (yes, I think this is actually a bug). Verified with recent 5.2.35 on Mac OS X also.
[8 Feb 2014 1:26] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.1.2 release, and here's the changelog entry:

When setting "On Delete" and "On Update" to "SET NULL" on Foreign Key
fields that were marked as "NOT NULL", a generic "ERROR: Error 1005: Can't
create table" error was emitted. The generated error is now more specific.

Thank you for the bug report.