CODEWITHSUNDEEP
mysqlsqldatabasephpmyadmin
Baraa
Baraa

Reputation: 697

I am getting an error trying to create table in phpmyadmin

I am trying to run the following:

CREATE TABLE IF NOT EXISTS table_name (
user_id int(11) NOT NULL,
other_id int(11) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (other_id) REFERENCES other_table(id),
PRIMARY KEY (user_id, other_id)
);

and getting the following error:

#1005 - Can't create table 'database_name.table_name' (errno: 150)

am I doing something wrong? This works fine just running it in another environment rather than phpmyadmin sql environment.

Upvotes: 0

Views: 326

Answers (3)

SH-
SH-

Reputation: 1642

Take a look at this SO question.

Note the correct answer. Check column types They need to match. May be your problem.

In general, Here is the authoritative guide to FK in Mysql.

In addition to SHOW ERRORS, in the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), you can obtain a detailed explanation of the most recent InnoDB foreign key error by checking the output of SHOW ENGINE INNODB STATUS.

EDIT: Incorporating comments

Table on PHPMyAdmin were defaulting to MyISAM. On Local they were defaulting to to InnoDB. MyISAM does not support FK. This does not fully explain the difference, as based on MySql Documentation, It should just work, without creating the FK's. ( Perhaps a settings issue or Older Version Issue)

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

DROP TABLE IF EXISTS `table_name`;
CREATE TABLE IF NOT EXISTS table_name (
user_id int(11) NOT NULL,
other_id int(11) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (other_id) REFERENCES other_table(id),
PRIMARY KEY (user_id, other_id)
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270973

Does users and other_table exist?

You can't have the foreign key references to non-existant tables.

You can add the references afterwards with alter table.

Upvotes: 0

Related Questions