MySQL Bugs: #62464: On Delete and On Update set to "SET NULL" conflict with "NOT NULL"

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

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

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.

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.