Sqlite Create Table with Check column from other table

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 = 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?

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 max FROM position WHERE id_position = NEW.id_position)
BEGIN
  SELECT RAISE(FAIL, "invalid salary");
END;

StackExchange Database Administrators Q#159945, answer score: 8