SQL error when adding reference

Question

In my MySQL database I have two tables:

CREATE TABLE orders (
id BIGINT AUTO_INCREMENT, 
user_id BIGINT DEFAULT 1 NOT NULL, 
price DECIMAL(18, 2) NOT NULL, 
name VARCHAR(255) NOT NULL, 
surname VARCHAR(255) NOT NULL, 
[...]
is_fulfilled TINYINT(1) DEFAULT '0' NOT NULL,
INDEX user_id_idx (user_id), 
PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = INNODB;

and

CREATE TABLE user (
id INT AUTO_INCREMENT, 
username VARCHAR(128) NOT NULL UNIQUE, 
email VARCHAR(128) NOT NULL UNIQUE, 
[...]
INDEX name_idx_idx (username), 
PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = INNODB;

When I'm trying to set relationship

ALTER TABLE orders ADD CONSTRAINT orders_user_id_user_id FOREIGN KEY (user_id) REFERENCES user(id);

I get this error

#1005 - Can't create table 'druk.#sql-b38_173' (errno: 150)

Is there something wrong in that tables or what?

This question has been answered previously here; [link for answer to errno 150](http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150) and here; (http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150) — Intern87, Sep 21 '16 at 11:53

Gordon Linoff · Accepted Answer · 2016-09-21T12:02:27.123

0

That is a really weird error that does not seem related to your tables in their current form. Hence, you need to recreate the tables correctly.

However, you do have a problem. The column orders.user_id has a type that does not match the type of user.id. You should change it to:

user_id INT DEFAULT 1 NOT NULL,

Here is a SQL Fidde.

I should add that defaulting the value to "1" seems strange.

edited Sep 21 '16 at 12:02

answered Sep 21 '16 at 11:57

Gordon Linoff

1,242,037
58
646
786

The error is related to the tables, error 1005 says; If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the correct column names and **types**, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed. The foreign key types must be the same for a table replacing it. – Intern87 Sep 21 '16 at 12:01
@Intern87 . . . I wasn't clear in the answer. I wanted to point out that the code has another error. – Gordon Linoff Sep 21 '16 at 12:03
Thx, the problem was in different types. – Rodgard Sep 21 '16 at 12:09
fair enough, and you are right to point out default 1 is very odd. – Intern87 Sep 21 '16 at 12:09

score 0 · Answer 2 · answered Sep 21 '16 at 11:58

0

Just ensure that both fields match the type. Either change orders.user_id to INT or user.id to BIGINT. It seem more reasonable to replace orders.user_id to INT unless you expect millions of users ...

answered Sep 21 '16 at 11:58

salih0vicX

1,363
1
8
9

SQL error when adding reference

2 Answers2