CODEWITHSUNDEEP
mysqlsqldatabaserelational-databaseforeign-key-relationship
rfc1484
rfc1484

Reputation: 9857

Foreign key error when creating table (errno 150)

I'm trying to create a relationship between two tables using the following queries:

create table card 
(id int not null auto_increment, 
post_id bigint(20) unsigned not null, 
primary key(id));

create table user_comment 
(id int not null auto_increment, 
comment_author longtext, 
comment_post_id bigint(20) unsigned not null, 
primary key (id), 
foreign key (comment_post_id) references card(post_id));

However it gives me the following error message:

ERROR 1005 (HY000) at line 9: Can't create table 'test.user_comment' (errno: 150)

If I execute the command for showing innodb status:

show engine innodb status;

It shows this message:

LATEST FOREIGN KEY ERROR Error in foreign key constraint of table test/user_comment: foreign key (comment_post_id) references card(post_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.

But I still can't figure out how to fix the problem.

Any ideas?

Upvotes: 0

Views: 107

Answers (2)

Akshay Damle
Akshay Damle

Reputation: 1250

Just use this command once before creating the table:

SET FOREIGN_KEY_CHECKS=0;

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425863

From the docs:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

Create an index on card (post_id).

Upvotes: 1

Related Questions