Bug #21701 Error Msg Incorrect: On delete set null on not null column
Submitted: 17 Aug 2006 17:28 Modified: 28 Aug 2006 14:49
Reporter: Jared Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.24 OS:Linux (Redhat Linux AS 4)
Assigned to: CPU Architecture:Any

[17 Aug 2006 17:28] Jared Smith
Description:
This is effectively a cosmetic error about the error message that I received back being a bit deceptive as to the actual problem.

I created 2 tables one which had a foreign key referencing the other and the foreign key had the on delete set null however I had messed up and put a not null constraint on the column. The DB caught this error on my part and yielded the following error message:

ERROR 1005 (HY000): Can't create table './analyzer/#sql-47c1_16.frm' (errno: 150)

I believe this error message could be improved to tell what the problem was in this case.  Perhaps the error could be something more like:

ERROR 1005 (HY000): Can't set null on not null column 

How to repeat:
mysql> CREATE TABLE `retention_rule` (
    ->   `rule_id` bigint(20) unsigned NOT NULL,
    ->   `name` varchar(64) default NULL,
    ->   PRIMARY KEY  (`rule_id`),
    ->   UNIQUE KEY `AK1an_retention_rule` (`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE `retention_rule_items` (
    ->   `rule_item_id` bigint(20) unsigned NOT NULL,
    ->   `rule_id` bigint(20) unsigned NOT NULL,
    ->   PRIMARY KEY  (`rule_item_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> ALTER TABLE retention_rule_items
    ->        ADD CONSTRAINT fkey
    ->               FOREIGN KEY (rule_id)
    ->                              REFERENCES retention_rule  (rule_id)
    ->                              ON DELETE SET NULL;
ERROR 1005 (HY000): Can't create table './analyzer/#sql-47c1_16.frm' (errno: 150)
mysql>
mysql>
mysql> alter table retention_rule_items modify column rule_id bigint(20) unsigned NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> ALTER TABLE retention_rule_items
    ->        ADD CONSTRAINT fkey
    ->               FOREIGN KEY (rule_id)
    ->                              REFERENCES retention_rule  (rule_id)
    ->                              ON DELETE SET NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
Change error message in this case to be something like "Can't set null on not null column"
[28 Aug 2006 10:00] Valeriy Kravchuk
Thank you for a problem report. In fact, you got information about the real error, 150. Try to execute "perror 150", and you'll get:

MySQL error code 150: Foreighn key constraint is incorrectly formed

Isn't it descriptive enough?
[28 Aug 2006 14:49] Jared Smith
Yes that error looks sufficient I wasn't aware of the perror command and expected the that the error I saw was the only error.  I changed the status to close since that description does sufficiently cover this case.  Thanks for clearing things up and letting me know about the perror command.