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:
Error Code: 1072. Key column ... doesn't exist in table
whereas ... are Gebaeude
, Dept_Name
and 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!
Note that foreign keys are not mandatory, and a table may have no foreign keys. Conversely, every column in a table may have a foreign key constraint.
General standard answer is no. It is only possible if foreign key refers any column uniquely in other table. That means foreign key must be the candidate key in other table and primary key is also a candidate key the table.
Foreign key constraints can be defined at the table level. However, if you only want the constraint to apply to a single column, it can be applied at the column level.
ALTER TABLE students ADD FOREIGN KEY (student_id) REFERENCES points(id); To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: ALTER TABLE students ADD CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES points(id);
You are doing it wrong, take a look to this example
http://www.sqlfiddle.com/#!2/a86cf
your FK line should be more like this:
FOREIGN KEY (field_that_will_be_Fk) REFERENCES Table_to_reference (field_to_reference)
I.E=
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With