Howto create simple SQL table with check condition?
<p>I am new to sql coding and trying to create a table.
I got this table as example table from our teacher:</p>

<pre><code>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 &gt; 25000),
Chef CHAR(3)
CHECK (Chef IN (SELECT Kuerzel FROM Lektor)),
PRIMARY KEY (Kuerzel),
FOREIGN KEY (Kuerzel) REFERENCES Person)
</code></pre>

<p>I tried to create the table with that code but i cannot because of:</p>

<pre><code>CHECK (Chef IN (SELECT Kuerzel FROM Lektor))
</code></pre>

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

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

<pre><code>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 &gt; 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;
</code></pre>

<p>Anyone who knows how to solve this?</p>