mysql - 无法创建 SQL 表 - 已定义错误 1005

Question

我不被允许创建下表。是因为复合主键的原因吗？我还添加了用户和标题表的信息，以防代码出现问题。

CREATE TABLE store
(
    short_name  VARCHAR(5)                      NOT NULL,
    name        VARCHAR(30)                     NOT NULL,
    address1    VARCHAR(50)                     NOT NULL,
    address2    VARCHAR(20),
    city        VARCHAR(30)                     NOT NULL,
    state       VARCHAR(30)                     NOT NULL,
    zip         VARCHAR(10)                     NOT NULL,
    phone       VARCHAR(15)                     NOT NULL,
    CONSTRAINT store_pk PRIMARY KEY (short_name)
);

CREATE TABLE publishers
(
    name        VARCHAR(50)                         NOT NULL,
    status      TINYINT         DEFAULT 1           NOT NULL,
    CONSTRAINT publishers_pk PRIMARY KEY (name)
);
CREATE TABLE users
(
    id          INT                     NOT NULL    AUTO_INCREMENT,
    name        VARCHAR(70)                     NOT NULL,
    password    VARCHAR(60),        
    address1    VARCHAR(50)                     NOT NULL,
    address2    VARCHAR(20),
    city        VARCHAR(30)                     NOT NULL,
    state       VARCHAR(30)                     NOT NULL,
    zip         VARCHAR(10)                     NOT NULL,
    phone       VARCHAR(15),
    email       VARCHAR(90),
    store       VARCHAR(5),
    admin       TINYINT             DEFAULT 0   NOT NULL,
    mail_list   TINYINT             DEFAULT 0   NOT NULL,
    active      TINYINT             DEFAULT 0   NOT NULL,
    CONSTRAINT users_pk PRIMARY KEY (id),
    CONSTRAINT users_fk FOREIGN KEY (store)
        REFERENCES store (short_name)
        ON DELETE   SET NULL
        ON UPDATE   CASCADE
);

CREATE TABLE titles
(
    id          INT                 NOT NULL    AUTO_INCREMENT,
    publisher   VARCHAR(50),
    title       VARCHAR(50)         NOT NULL,
    status      ENUM('active', 'announced', 'inactive'),
    discount    TINYINT             NOT NULL,
    CONSTRAINT title_pk PRIMARY KEY (id),
    CONSTRAINT title_fk FOREIGN KEY (publisher)
        REFERENCES publishers (name)
        ON DELETE   SET NULL
        ON UPDATE   CASCADE
);

CREATE TABLE subscriptions
(
    user        INT,
    title       INT,
    quantity    TINYINT             DEFAULT 1           NOT NULL,
    CONSTRAINT subscription_pk PRIMARY KEY (user, title),
    CONSTRAINT subscription_user_fk FOREIGN KEY (user)
        REFERENCES users (id)
        ON DELETE   SET NULL
        ON UPDATE   CASCADE,
    CONSTRAINT subscription_title_fk FOREIGN KEY (title)
        REFERENCES titles (id)
        ON DELETE   SET NULL
        ON UPDATE   CASCADE
);

Answer 1

使用表定义，只有 subscriptions 表出现问题，这是由于 ON DELETE SET NULL 子句造成的。 subscriptions 表中的 user 和 title 列均定义为 NOT NULL!