MYSQL: "Cannot add foreign key constraint"

Question

I try to create table that linked to two tables.
The linked table:

CREATE TABLE `cartsitems` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `cart_id` INT NOT NULL,
    `product_id` INT NOT NULL,
    `price` DOUBLE(6,2) NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`cart_id`) REFERENCES `carts`(`id`),
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
)

The products table:

CREATE TABLE `products` (
  `id` INT AUTO_INCREMENT,
  `category_id` INT,
  `description` VARCHAR(255),
  `price` DOUBLE(6,2),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`id`) REFERENCES `categories`(`id`)
)

The carts table:

 CREATE TABLE `carts` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `users_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`users_id`) REFERENCES `users`(`id`)
 )

I search and I found this answer: MySQL Cannot Add Foreign Key Constraint
I check & I the engine is the same (cause I didnt set something else) , Also the collection The type is the same (both int)
And its uniqe (primary key)
What is the problem?

In the `products` table, the foreign key should be `category_id`. — Barmar, Jan 04 '17 at 21:00
What does `SHOW ENGINE INNODB STATUS` say after you get the error? — Barmar, Jan 04 '17 at 21:02
@Barmar: Do consider posting your suggested answer here as such so as it's marked as such. — nyedidikeke, Jan 04 '17 at 21:27
@nyedidikeke I thought the question was about creating the `cartsitems` table. My comment applies to the `products` table. — Barmar, Jan 04 '17 at 21:29

score 0 · Answer 1 · answered Jan 04 '17 at 21:00

You are missing id field in products table.

CREATE TABLE `products` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `category_id` INT,
    `description` VARCHAR(255),
    `price` DOUBLE(6,2),
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id`) REFERENCES `categories`(`id`)
);

MYSQL: "Cannot add foreign key constraint"

1 Answers1