MySQL - 无法创建表(errno : 150) - Foreign key constraints

Question

我想在我的 MySQL 数据库中创建这 3 个表:

CREATE TABLE categories (
    category_id SMALLINT NOT NULL AUTO_INCREMENT,
    category VARCHAR(255) NOT NULL UNIQUE,
    description TINYTEXT NOT NULL,
    PRIMARY KEY (category_id)
);


CREATE TABLE product (
    product_id SMALLINT NOT NULL AUTO_INCREMENT,
    product VARCHAR(255) NOT NULL UNIQUE,
    description TINYTEXT NOT NULL,
    price DECIMAL(6,2) NOT NULL,
    PRIMARY KEY (product_id)
);

CREATE TABLE categories_products (
    category_id SMALLINT NOT NULL,
    product_id SMALLINT NOT NULL,
    PRIMARY KEY (category_id, product_id),
    FOREIGN KEY (category_id) REFERENCES categories (category_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (product_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

问题出在最后一张表上。我得到一个错误:

1005 - Can't create table 'test1.categories_products' (errno: 150)

但是当我尝试在没有限制的情况下创建它时，它会起作用。

CREATE TABLE categories_products (
    category_id SMALLINT NOT NULL,
    product_id SMALLINT NOT NULL,
    PRIMARY KEY (category_id, product_id)
);

我需要约束以防产品或类别被删除。我做错了什么？

Answer 1

好像有错字

CREATE TABLE product

(单数)

然后引用

FOREIGN KEY (product_id) REFERENCES products (product_id)

(复数)。