0

I am new to sql coding and trying to create a table. I got this table as example table from our teacher:

CREATE TABLE Lektor
(Kuerzel CHAR(3) NOT NULL,
Fachgebiet VARCHAR(35) NOT NULL,
PersNr INTEGER NOT NULL UNIQUE,
Eintritt DATE NOT NULL,
Gehalt INTEGER CHECK (Gehalt > 25000),
Chef CHAR(3)
CHECK (Chef IN (SELECT Kuerzel FROM Lektor)),
PRIMARY KEY (Kuerzel),
FOREIGN KEY (Kuerzel) REFERENCES Person)

I tried to create the table with that code but i cannot because of:

CHECK (Chef IN (SELECT Kuerzel FROM Lektor))

It keeps telling me that "Lektor" could not be found, but I try to create it...

I was able to create the table without that Check with the following Mysql workbench syntax:

CREATE TABLE IF NOT EXISTS `buchverlag`.`Lektor` (
  `Kuerzel` CHAR(3) NOT NULL,
  `Fachgebiet` VARCHAR(35) NOT NULL,
  `PersNr` INT(11) NOT NULL,
  `Eintritt` DATE NOT NULL,
  `Gehalt` INT(11) NULL CHECK (Gehalt > 25000),
  `Chef` CHAR(3) NULL,
  PRIMARY KEY (`Kuerzel`),
  UNIQUE INDEX `PersNr_UNIQUE` (`PersNr` ASC),
  CONSTRAINT `Kuerzel`
    FOREIGN KEY (`Kuerzel`)
    REFERENCES `buchverlag`.`Person` (`Kuerzel`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Anyone who knows how to solve this?

JPA
  • 1
  • 2
    MySQL does not support `check`. Use a trigger that cancels the insertion on your conditions. You can cancel a trigger with this command `SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insert not allowed. kuerzel invalid';` – juergen d Apr 26 '16 at 16:17
  • Look here [CHECK constraint in MySQL is not working](http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working) – Thomas G Apr 26 '16 at 16:20
  • Thanks! I thought that CHECK is something basic like UPDATE or INSERT and available in all SQL variants. I will checkout your information. – JPA Apr 26 '16 at 16:23

0 Answers0