SQL Foreign key constraint is incorrectly formed

by

Hope somone can help me.
Session Table is here:

CREATE TABLE User (
  UID int,
  Name varchar(40),
  Vorname varchar(15),
  Titel varchar(40),
  Geschlecht int,
  EMailAdr varchar(40),
  SHID int,
  Guthaben int ,
  PRIMARY KEY (UID),
  FOREIGN KEY (SHID) REFERENCES Schule(SHID), 
  UNIQUE (EMailAdr)
);

CREATE TABLE Schule (
  SHID int,
  Name varchar(40),
  Adr varchar(80),
  PRIMARY KEY (SHID),
  UNIQUE (Adr),
  UNIQUE (Name)
);

CREATE TABLE Lokal (
  LID int,
  Name varchar(40),
  Adr varchar(40),
  KontoNr varchar(40),
  PRIMAYR KEY (LID),
  UNIQUE (Adr),
  UNIQUE (Name)
);

CREATE TABLE Zahlungsarten (
  ZID int,
  Bezeichnung varchar(40),
  PRIMARY KEY (ZID)
);

CREATE TABLE BestellteArtikel (
  ArID int,
  BID int,
  Anzahl int,
  PRIMARY KEY (ArID,BID)
);

CREATE TABLE Status (
  SID int,
  Bezeichnung varchar(40),
  PRIMARY KEY (SID)

);

CREATE TABLE Artikel (
  ArID int,
  Bezeichnuhg varchar(40),
  Beschreibung varchar(120),
  Preis int,
  LID int,
  Liefervolumen int,
  PRIMARY KEY (ArID),
  UNIQUE (Bezeichnung)
);

CREATE TABLE Bestellungen (
  BID int,
  UID int,
  Datum Date,
  ZID int,
  SID int,
  SHID int,
  LID int,
  PRIMARY KEY (BID),
  FOREIGN KEY (UID) REFERENCES User(UID),
  FOREIGN KEY (ZID) REFERENCES Zahkungsarten(ZID),
  FOREIGN KEY (SID) REFERENCES Status(SID),
  FOREIGN KEY (SHID) REFERENCES Schule(SHID),
  FOERIGN KEY (LID) REFERENCES Lokal(LID)
);

  • The syntax for a foreign key is CONSTRAINT name_of_constraint FOREIGN KEY(local_column) REFERENCES foreign_table(foreign_column)

    – 

There are several problems.

In table User:

You’re referencing the table Schule before you have created the latter table. The table must exist before you can declare a foreign key that references it. So this works if you create Schule before you create User (I tested it, and it works).

In table Lokal:

PRIMAYR KEY (LID),

You mistyped PRIMARY.

In table Artikel:

Bezeichnuhg varchar(40),

I think you mistyped Bezeichnung.

In table Bestellungen:

FOERIGN KEY (LID) REFERENCES Lokal(LID)

You mistyped FOREIGN.

With all due respect, you should have been able to correct these mistakes yourself without posting a question to Stack Overflow. Most of them are obvious spelling errors.

Leave a Comment