CREATE TABLE Angestellte (
persid VARCHAR(32) NOT NULL PRIMARY KEY, INDEX (persid),
name VARCHAR(255) NOT NULL,
abtid VARCHAR(32) NOT NULL,
INDEX abtid_index (abtid)
) TYPE = InnoDB;
CREATE TABLE Abteilungen (
abtid VARCHAR(32) NOT NULL PRIMARY KEY, INDEX (abtid),
name VARCHAR(255) NOT NULL,
chef VARCHAR(32),
INDEX chef_index (chef),
FOREIGN KEY (chef) REFERENCES Angestellte(persid)
ON DELETE SET NULL
ON UPDATE SET NULL
) TYPE = InnoDB;
ALTER TABLE Angestellte ADD
FOREIGN KEY (abtid) REFERENCES Abteilungen(abtid)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
|