Questions tagged [mysql-error-1005]

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table.

From the MySQL documentation:

  • If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.
  • If an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table.
152 questions
107
votes
13 answers

Error Code: 1005. Can't create table '...' (errno: 150)

I searched for a solution to this problem on the Internet and checked the Stack Overflow questions, but none of the solutions worked for my case. I want to create a foreign key from table sira_no to metal_kod. ALTER TABLE sira_no ADD CONSTRAINT…
lamostreta
  • 2,359
  • 7
  • 44
  • 61
80
votes
3 answers

sql error errno: 121

CREATE TABLE `users` ( `UID` INT UNSIGNED NOT NULL AUTO_INCREMENT , `username` VARCHAR(45) NOT NULL , `password` VARCHAR(100) NULL , `name` VARCHAR(100) NULL , `gender` BIT NULL , `email` VARCHAR(255) NULL , `phone` VARCHAR(30) NOT…
user396420
68
votes
22 answers

MySQL. Can't create table errno 150

I have to create a database with two tables in MySQL, but the script fails with errno 150 (foreign key problem). I double-checked the foreign key fields to be the same on both tables, and I can't find any error. Here is the script: SET…
David Espart
  • 11,520
  • 7
  • 36
  • 50
49
votes
14 answers

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 …
Ankur Banerjee
  • 768
  • 1
  • 11
  • 24
40
votes
17 answers

MySQL Foreign Key Error 1005 errno 150 primary key as foreign key

I'm making a small DataBase with MySQL Workbench. I have a main table, called "Immobili", which has a Primary Key composed by four columns: (Comune, Via, Civico, Immobile). I also have three other tables, which have the same primary key (Comune,…
IssamTP
  • 2,408
  • 1
  • 25
  • 48
39
votes
4 answers

MySQL errorno 121

I'm getting this error in MySQL create. I'm doing: CREATE TABLE `blogReply` ( `Id` INT(24) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of This Table', `blogId` INT(24) NOT NULL COMMENT 'Blog where this reply was posted', …
Angel.King.47
  • 7,922
  • 14
  • 60
  • 85
25
votes
11 answers

Foreign key not working in MySQL: Why can I INSERT a value that's not in the foreign column?

I've created a table in MySQL: CREATE TABLE actions ( A_id int NOT NULL AUTO_INCREMENT, type ENUM('rate','report','submit','edit','delete') NOT NULL, Q_id int NOT NULL, U_id int NOT NULL, date DATE NOT NULL, time TIME NOT NULL, rate…
stalepretzel
  • 15,543
  • 22
  • 76
  • 91
18
votes
4 answers

MySQL: Creating table with FK error (errno 150)

I've created a model with MySQL Workbench and am now attempting to install it to a mysql server. Using File > Export > Forward Engineer SQL CREATE Script... it outputs a nice big file for me, with all the settings I ask for. I switch over to MySQL…
Peter Bailey
  • 105,256
  • 31
  • 182
  • 206
16
votes
8 answers

Mysql ERROR 1005 (HY000): Can't create table 'tmp' (errno: 13)

I'm Running Mysql on ubuntu 9.10, the process of Mysql is running as root, I'm using root account when logging to Mysql, which I gave all privileges, I'm using my own db(not mysql), I can create a table, but when i try to create Temporary table i…
koby
  • 665
  • 2
  • 6
  • 15
14
votes
3 answers

Foreign Key add fails in MySQL with Error Code 1005, number 150

So I'm attempting to add a new foreign key to one of my tables as such: ALTER TABLE `UserTransactions`.`ExpenseBackTransactions` ADD CONSTRAINT `FK_EBTx_CustomAccountID` FOREIGN KEY (`CustomAccountID` ) REFERENCES…
thaBadDawg
  • 5,160
  • 6
  • 35
  • 44
13
votes
10 answers

can't add foreign key in mysql?

I used MySQL workbench to add a foreign key in a table, but some strange error happened, this is the SQL statement: ALTER TABLE `tansung`.`Declaration` ADD COLUMN `goodsId` INT(11) NOT NULL AFTER `declarationId` , ADD CONSTRAINT `goodsId` …
firefly
  • 183
  • 1
  • 2
  • 7
13
votes
3 answers

SQL - error code 1005 with error number 121

I'm running the following MySQL query (trimmed down), generated automatically by MySQL Workbench and I get the following error: Error Code: 1005 Can't create table 'regula.reservation' (errno: 121) I'm not very proficient with databases and this…
Amir Rachum
  • 76,817
  • 74
  • 166
  • 248
11
votes
2 answers

MySQL Foreign Key, Can't create table (errno: 150)

I am trying to build the database and tables for my system. But I found that if I don't add the foreign key in the codes. There is no error. I've used many method try to make the codes works, but it still have error. I am using MySQL 5.5.31, and the…
Confucius
  • 419
  • 3
  • 7
  • 14
10
votes
2 answers

Adding foreign key on multiple columns

I'm trying to create a foreign key on two columns of a table to point to the same column of another table, but I seem to get an error... Here's what I do: CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT…
nico
  • 50,859
  • 17
  • 87
  • 112
10
votes
1 answer

Foreign Keys and MySQL Errors

I have the following script to create a table in MySQL version 5.1 which is to refer to 3 other tables. All 3 tables have been created using InnoDB, and all 3 tables have the ID column defined as INT. I have created other tables successfully which…
Elie
  • 13,693
  • 23
  • 74
  • 128
1
2 3
10 11