SQLite – Create Table with Check Constraint from Another Table

check-constraintssqlitetable

I have two tables:

position

CREATE TABLE IF NOT EXISTS position(
  id_position INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  min REAL NOT NULL CHECK (min > 0),
  max REAL CHECK (max > 0),
);

min, max links to salary

employee

CREATE TABLE IF NOT EXISTS employee (
  id_employee INTEGER PRIMARY KEY AUTOINCREMENT,
  id_position INTEGER NOT NULL,
  name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  data_of_birth TEXT NOT NULL CHECK (DATE(data_of_birth) IS NOT NULL AND data_of_birth < DATE('now','localtime')),
  salary REAL NOT NULL CHECK (salary >= position.min AND salary <= position.max),
  FOREIGN KEY(id_stanowisko) REFERENCES stanowisko(id_stanowisko)
);

But check in salary column doesn't work. I have an error no such column: position.min.

Any solution?

Best Answer

Looking up a value from another table would require a subquery, but the documentation says:

The expression of a CHECK constraint may not contain a subquery.

There is no such restriction on a trigger's WHERE clause:

CREATE TRIGGER employee_salary_check
BEFORE INSERT ON employee
WHEN NEW.salary < (SELECT min FROM position WHERE id_position = NEW.id_position)
  OR NEW.salary > (SELECT max FROM position WHERE id_position = NEW.id_position)
BEGIN
  SELECT RAISE(FAIL, "invalid salary");
END;
Related Question