sql - 键中命名的列 "sn"不存在 LINE 6 : PRIMARY KEY (SN, PN, JN)

Question

我用以下查询创建了 3 个表:

S表:

CREATE TABLE S (
    SN INT,
    SNAME CHAR(10),
    RATING FLOAT,
    CITY CHAR(10),
    PRIMARY KEY (SN)
)

J表:

CREATE TABLE J (
    JN INT,
    JNAME CHAR(10),
    CITY CHAR(10),
    PRIMARY KEY (JN)
)

P表:

CREATE TABLE P (
    PN INT,
    PNAME CHAR(10),
    COLOR CHAR(10),
    CITY CHAR(10),
    WEIGHT FLOAT,
    PRIMARY KEY (PN)
)

他们成功创建，没有任何错误。

问题是，当我尝试运行以下查询时，我遇到了一个奇怪的错误:

 CREATE TABLE SPJ (
    QTY INT,
    FOREIGN KEY (SN) REFERENCES S(SN),
    FOREIGN KEY (PN) REFERENCES P(PN),
    FOREIGN KEY (JN) REFERENCES J(JN),
    PRIMARY KEY (SN, PN, JN)
)

ERROR: column "sn" named in key does not exist LINE 6: PRIMARY KEY (SN, PN, JN)

Answer 1

您需要创建列和外键。它们的外键不创建列，它们实际上只是约束。

CREATE TABLE SPJ (
    QTY INT,
    SN  INT,
    PN  INT,
    JN  INT,
    FOREIGN KEY (SN) REFERENCES S(SN),
    FOREIGN KEY (PN) REFERENCES P(PN),
    FOREIGN KEY (JN) REFERENCES J(JN),
    PRIMARY KEY (SN, PN, JN)
)