MySQL 尝试定义外键时“表中不存在键列”

"Key column doesn't exist in table" when trying to define FOREIGN KEY

提问by Valentino Ru

I'm trying to setup a MySQL-Script that creates 5 tables. In three tables, there is a FOREIGN KEY, and for all three of them the same error appears:

我正在尝试设置一个创建 5 个表的 MySQL 脚本。在三个表中，有一个FOREIGN KEY，并且对于所有三个表都出现相同的错误：

Error Code: 1072. Key column ... doesn't exist in table

whereas ... are Gebaeude, Dept_Nameand Mat_Nr

而 ... 是Gebaeude，Dept_Name并且Mat_Nr

Here's the script

这是脚本

use cs261_24;

drop table if exists Professor;
drop table if exists Departement;
drop table if exists Gebaeude;
drop table if exists Student;
drop table if exists Pruefung;


CREATE TABLE Gebaeude (
                        Gebaeude    VARCHAR(20) NOT NULL PRIMARY KEY,
                        Hauswart    VARCHAR(20) NOT NULL,
                        Adresse     VARCHAR(20) NOT NULL
)engine = innodb;

CREATE TABLE Professor  (
                        P_ID        INTEGER PRIMARY KEY CHECK (P_ID > 0),
                        P_Name      VARCHAR(20) NOT NULL,
                        Dept_Name   VARCHAR(20) NOT NULL,
                        Raum        INTEGER UNIQUE CHECK (Raum > 0),
                        Tel         INTEGER(10) UNIQUE CHECK (Tel > 210000000),
                        FOREIGN KEY (Gebaeude) REFERENCES Gebaeude (Gebaeude) 
)engine = innodb;

CREATE TABLE Departement (
                        Dept_Name   VARCHAR(20) NOT NULL PRIMARY KEY,
                        Vorsteher   VARCHAR(20) NOT NULL
)engine = innodb;

CREATE TABLE Student (
                        Mat_Nr      INTEGER(8) PRIMARY KEY CHECK (Mat_Nr > 0),
                        S_Name      VARCHAR(20) NOT NULL,
                        Semester    INTEGER CHECK(Semester > 0),
                        FOREIGN KEY (Dept_Name) REFERENCES Departement (Dept_Name) 
)engine = innodb;

CREATE TABLE Pruefung (
                        Pr_ID       INTEGER PRIMARY KEY CHECK(Pr_ID > 0),
                        Fach        VARCHAR(20) NOT NULL,
                        Pruefer     VARCHAR(20) NOT NULL,
                        Note        FLOAT CHECK (Note >= 1 AND Note <= 6),
                        FOREIGN KEY (Mat_Nr) REFERENCES Student (Mat_Nr)
)engine = innodb;

Why? I work with MySQL Workbench, and I clearly see the created tables, plus the specific columns are marked as primary keys!

为什么？我使用 MySQL Workbench，我清楚地看到创建的表，加上特定的列被标记为主键！

回答by jcho360

You are doing it wrong, take a look to this example

你做错了，看看这个例子

http://www.sqlfiddle.com/#!2/a86cf

http://www.sqlfiddle.com/#!2/a86cf

your FK line should be more like this:

你的 FK 线应该更像这样：

FOREIGN KEY (field_that_will_be_Fk) REFERENCES Table_to_reference (field_to_reference)

FOREIGN KEY (field_that_will_be_Fk) REFERENCES Table_to_reference (field_to_reference)

I.E=

IE=

CREATE TABLE Gebaeude (
                        Gebaeude    VARCHAR(20) NOT NULL PRIMARY KEY,
                        Hauswart    VARCHAR(20) NOT NULL,
                        Adresse     VARCHAR(20) NOT NULL
)engine = innodb;

CREATE TABLE Professor  (
                       Gebaeude_FK varchar(20) NOT NULL,
                        P_ID        INTEGER PRIMARY KEY CHECK (P_ID > 0), 
                        P_Name      VARCHAR(20) NOT NULL,
                        Dept_Name   VARCHAR(20) NOT NULL,
                        Raum        INTEGER UNIQUE CHECK (Raum > 0),
                        Tel         INTEGER(10) UNIQUE CHECK (Tel > 210000000),
                        FOREIGN KEY (Gebaeude_FK) REFERENCES Gebaeude (Gebaeude) 
)engine = innodb;

回答by Jordan Kaye

You have to create the column in each of the tables before you can constrain them as a foreign key.

您必须先在每个表中创建列，然后才能将它们约束为外键。

See this link for reference: Link

请参阅此链接以供参考：链接