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 11:37	Modified:	15 Feb 2005 22:39
Reporter:	Hakan Kuecuekyilmaz
Status:	Closed
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.0.2/5.0.3	OS:	Linux (Linux)
Assigned to:	Heikki Tuuri	Target Version:

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.