4

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?

5

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.