Rodgard Rodgard - 2 months ago 5
SQL Question

SQL error when adding reference

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?

Answer

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.

Comments