MySQL Bugs: #7831: ALTER TABLE ADD CONSTRAINT gives wrong error message

Bug #7831	ALTER TABLE ADD CONSTRAINT gives wrong error message
Submitted:	12 Jan 2005 10:37	Modified:	15 Feb 2005 21:39
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.2/5.0.3	OS:	Linux (Linux)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
ALTER TABLE t1 ADD CONSTRAINT PRIMARY KEY gives wrong error message on InnoDB.

Tested it also with 5.0.3

How to repeat:
[11:40] root@test>CREATE TABLE t1 (a int) ENGINE InnoDB;
Query OK, 0 rows affected (0.21 sec)

5.0.2-alpha-debug-log
[11:41] root@test>ALTER TABLE t1 ADD CONSTRAINT PRIMARY KEY (a);
ERROR 1005 (HY000): Can't create table './test/#sql-851_2f.frm' (errno: 150)

The same for 4.1.9

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.9

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t1 (a int) ENGINE InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE t1 ADD CONSTRAINT PRIMARY KEY (a);
ERROR 1005 (HY000): Can't create table './test/#sql-c93_1.frm' (errno: 150)
mysql>

This is not exactly a bug, rather a feature request.
Adding the primary key constraint fails because the field is not declared as not
null. A primary key is a unique key where all key columns must be defined as not null.

You can print error messages consisting errno with perror e.g.

jan@hundin:~/trans/mysql-5.0/extra> perror 150
Error code 150:  Unknown error 150
150 = Foreign key constraint is incorrectly formed
 
We really need better error reporting tool to MySQL.

If column is defined as NOT NULL, it still fails:

5.0.2-alpha-debug-log
[13:32] root@test>CREATE TABLE t1 (a int not null) ENGINE InnoDB;
Query OK, 0 rows affected (0.10 sec)

5.0.2-alpha-debug-log
[13:33] root@test>ALTER TABLE t1 ADD CONSTRAINT PRIMARY KEY (a);
ERROR 1005 (HY000): Can't create table './test/#sql-4e31_1.frm' (errno: 150)
5.0.2-alpha-debug-log

Fixed in 4.1 and 5.0.
--Heikki

Hi,

ALTER TABLE table_infos ADD  CONSTRAINT fk_cust_payterms
FOREIGN KEY (activities_id)
REFERENCES activities (id);

is also creating error in MySQL 5.0.27:

[ALTER - 0 row(s), 0.094 secs]  [Error Code: 1005, SQL State: HY000]  Can't create table '.\test\#sql-af8_2.frm' (errno: 150)

Janeve,

please post the complete table definitions.

You can use SHOW INNODB STATUS\G to look at the detailed description of the latest foreign key error.

Regards,

Heikki

Main problem when you receive this error in foreign key is related to the INT and UNSIGNED INT difference in key pair. You should verify that both fields have SAME attributes: generally a primary key is unsigned with auto_increment; if you reference it with a foreign key that is tied to a field that is simply an INT you receive this 150 error. Typing "perror 150" you receive an answer that is quite unuseful... :-)
SamPisa

omg, i had same problem and your comment help a lot. I had reference between BIGINT and INT.

You gents have defined at least 3 different errors, which are all coming up for the user as the same generic error.

It is a lack of feature indeed; and a very ugly one at that. I might even say it's a usability issue.  Proper error reporting is missing. 

"can't create table" is a not an adequate error message
if/when the error was "could not create key due to a mismatch in the fields (int vs bigint or unsigned vs signed)