mysql hy000 1005,MySQL “ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)”

问题

I was working on creating some tables in database foo, but every time I end up with errno 150 regarding the foreign key. Firstly, here's my code for creating tables:

CREATE TABLE Clients

(

client_id CHAR(10) NOT NULL ,

client_name CHAR(50) NOT NULL ,

provisional_license_num CHAR(50) NOT NULL ,

client_address CHAR(50) NULL ,

client_city CHAR(50) NULL ,

client_county CHAR(50) NULL ,

client_zip CHAR(10) NULL ,

client_phone INT NULL ,

client_email CHAR(255) NULL ,

client_dob DATETIME NULL ,

test_attempts INT NULL

);

CREATE TABLE Applications

(

application_id CHAR(10) NOT NULL ,

office_id INT NOT NULL ,

client_id CHAR(10) NOT NULL ,

instructor_id CHAR(10) NOT NULL ,

car_id CHAR(10) NOT NULL ,

application_date DATETIME NULL

);

CREATE TABLE Instructors

(

instructor_id CHAR(10) NOT NULL ,

office_id INT NOT NULL ,

instructor_name CHAR(50) NOT NULL ,

instructor_address CHAR(50) NULL ,

instructor_city CHAR(50) NULL ,

instructor_county CHAR(50) NULL ,

instructor_zip CHAR(10) NULL ,

instructor_phone INT NULL ,

instructor_email CHAR(255) NULL ,

instructor_dob DATETIME NULL ,

lessons_given INT NULL

);

CREATE TABLE Cars

(

car_id CHAR(10) NOT NULL ,

office_id INT NOT NULL ,

engine_serial_num CHAR(10) NULL ,

registration_num CHAR(10) NULL ,

car_make CHAR(50) NULL ,

car_model CHAR(50) NULL

);

CREATE TABLE Offices

(

office_id INT NOT NULL ,

office_address CHAR(50) NULL ,

office_city CHAR(50) NULL ,

office_County CHAR(50) NULL ,

office_zip CHAR(10) NULL ,

office_phone INT NULL ,

office_email CHAR(255) NULL

);

CREATE TABLE Lessons

(

lesson_num INT NOT NULL ,

client_id CHAR(10) NOT NULL ,

date DATETIME NOT NULL ,

time DATETIME NOT NULL ,

milegage_used DECIMAL(5, 2) NULL ,

progress CHAR(50) NULL

);

CREATE TABLE DrivingTests

(

test_num INT NOT NULL ,

client_id CHAR(10) NOT NULL ,

test_date DATETIME NOT NULL ,

seat_num INT NOT NULL ,

score INT NULL ,

test_notes CHAR(255) NULL

);

ALTER TABLE Clients ADD PRIMARY KEY (client_id);

ALTER TABLE Applications ADD PRIMARY KEY (application_id);

ALTER TABLE Instructors ADD PRIMARY KEY (instructor_id);

ALTER TABLE Offices ADD PRIMARY KEY (office_id);

ALTER TABLE Lessons ADD PRIMARY KEY (lesson_num);

ALTER TABLE DrivingTests ADD PRIMARY KEY (test_num);

ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Offices FOREIGN KEY (office_id) REFERENCES Offices (office_id);

ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Clients FOREIGN KEY (client_id) REFERENCES Clients (client_id);

ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Instructors FOREIGN KEY (instructor_id) REFERENCES Instructors (instructor_id);

ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Cars FOREIGN KEY (car_id) REFERENCES Cars (car_id);

ALTER TABLE Lessons ADD CONSTRAINT FK_Lessons_Clients FOREIGN KEY (client_id) REFERENCES Clients (client_id);

ALTER TABLE Cars ADD CONSTRAINT FK_Cars_Offices FOREIGN KEY (office_id) REFERENCES Offices (office_id);

ALTER TABLE Clients ADD CONSTRAINT FK_DrivingTests_Clients FOREIGN KEY (client_id) REFERENCES Clients (client_id);

These are the errors that I get:

mysql> ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Cars FOREIGN KEY

(car_id) REFERENCES Cars (car_id);

ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)

I ran SHOW ENGINE INNODB STATUS which gives a more detailed error description:

------------------------

LATEST FOREIGN KEY ERROR

------------------------

100509 20:59:49 Error in foreign key constraint of table foo/#sql-12c_4:

FOREIGN KEY (car_id) REFERENCES Cars (car_id):

Cannot find an index in the referenced table where the

referenced columns appear as the first columns, or column types

in the table and the referenced table do not match for constraint.

Note that the internal storage type of ENUM and SET changed in

tables created with >= InnoDB-4.1.12, and such columns in old tables

cannot be referenced by such columns in new tables.

See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

for correct foreign key definition.

------------

I searched around on StackOverflow and elsewhere online - came across a helpful blog post here with pointers on how to resolve this error - but I can't figure out what's going wrong. Any help would be appreciated!

回答1:

You should make car_id a primary key in cars.

回答2:

Note: I had the same problem, and it was because the referenced field was in a different collation in the 2 different tables (they had exact same type).

Make sure all your referenced fields have the same type AND the same collation!

回答3:

Check that BOTH tables have the same ENGINE. For example if you have:

CREATE Table FOO ();

and:

CREATE Table BAR () ENGINE=INNODB;

If you try to create a constraint from table BAR to table FOO, it will not work on certain MySQL versions.

Fix the issue by following:

CREATE Table FOO () ENGINE=INNODB;

回答4:

Subtle, but this error got me because I forgot to declare a smallint column as unsigned to match the referenced, existing table which was "smallint unsigned." Having one unsigned and one not unsigned caused MySQL to prevent the foreign key from being created on the new table.

id smallint(3) not null

does not match, for the sake of foreign keys,

id smallint(3) unsigned not null

回答5:

I got this completely worthless and uninformative error when I tried to:

ALTER TABLE `comments` ADD CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

My problem was in my comments table, user_id was defined as:

`user_id` int(10) unsigned NOT NULL

So... in my case, the problem was with the conflict between NOT NULL, and ON DELETE SET NULL.

回答6:

Also both the tables need to have same character set.

for e.g.

CREATE TABLE1 (

FIELD1 VARCHAR(100) NOT NULL PRIMARY KEY,

FIELD2 VARCHAR(100) NOT NULL

)ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_bin;

to

CREATE TABLE2 (

Field3 varchar(64) NOT NULL PRIMARY KEY,

Field4 varchar(64) NOT NULL,

CONSTRAINT FORIGEN KEY (Field3) REFERENCES TABLE1(FIELD1)

) ENGINE=InnoDB;

Will fail because they have different charsets. This is another subtle failure where mysql returns same error.

回答7:

I use Ubuntu linux, and in my case the error was caused by incorrect statement syntax (which I found out by typing perror 150 at the terminal, which gives

MySQL error code 150: Foreign key constraint is incorrectly formed

Changing the syntax of the query from

alter table scale add constraint foreign key (year_id) references year.id;

to

alter table scale add constraint foreign key (year_id) references year(id);

fixed it.

回答8:

The referenced field must be a "Key" in the referenced table, not necessarily a primary key. So the "car_id" should either be a primary key or be defined with NOT NULL and UNIQUE constraints in the "Cars" table.

And moreover, both fields must be of the same type and collation.

回答9:

I also received this error (for several tables) along with constraint errors and MySQL connecting and disconnecting when attempting to import an entire database (~800 MB). My issue was the result of The MySQL server max allowed packets being too low. To resolve this (on a Mac):

Opened /private/etc/my.conf

Under # The MySQL server, changed max_allowed_packet from 1M to 4M (You may need to experiment with this value.)

Restarted MySQL

The database imported successfully after that.

Note I am running MySQL 5.5.12 for Mac OS X (x86 64 bit).

回答10:

check to make the field you are referencing to is an exact match with foreign key, in my case one was unsigned and the other was signed so i just changed them to match and this worked

ALTER TABLE customer_information

ADD CONSTRAINT fk_customer_information1

FOREIGN KEY (user_id)

REFERENCES users(id)

ON DELETE CASCADE

ON UPDATE CASCADE

回答11:

Solved:

Check to make sure Primary_Key and Foreign_Key are exact match with data types.

If one is signed another one unsigned, it will be failed.

Good practice is to make sure both are unsigned int.

回答12:

I was using a duplicate Foreign Key Name.

Renaming the FK name solved my problem.

Clarification:

Both tables had a constraint called PK1, FK1, etc. Renaming them/making the names unique solved the problem.

回答13:

The referenced column must be an index of a single column or the first column in multi column index, and the same type and the same collation.

My two tables have the different collations. It can be shown by issuing show table status like table_name and collation can be changed by issuing alter table table_name convert to character set utf8.

回答14:

all, I solved a problem and wanted to share it:

I had this error <>

The issue was in that in my statement:

alter table system_registro_de_modificacion add foreign key

(usuariomodificador_id) REFERENCES Usuario(id) On delete restrict;

I had incorrectly written the CASING: it works in Windows WAMP, but in Linux MySQL it is more strict with the CASING, so writting "Usuario" instead of "usuario" (exact casing), generated the error, and was corrected simply changing the casing.

来源：https://stackoverflow.com/questions/2799021/mysql-error-1005-hy000-cant-create-table-foo-sql-12c-4-errno-150