Use check constraint on a column based on the value of a column in another table

Question

I have one table:

CREATE TABLE teams (
    id INTEGER PRIMARY KEY,
    member_count INTEGER NOT NULL
);

I have another table:

CREATE TABLE members (
    id INTEGER PRIMARY KEY,
    team_id INTEGER,
    member_number INTEGER
    FOREIGN KEY (team_id) REFERENCES teams (id)
);

I want member_number to have a value that is between 1 and member_count (inclusive). I know that I need to use the check constraint here, but I don't know how to do it using a column of another table. How do I do that?

Similar https://dba.stackexchange.com/questions/159945/sqlite-create-table-with-check-column-from-other-table/159990 — snakecharmerb, Dec 20 '20 at 09:00
This seems dangerous. If you update `member_count` then you might have inconsistent data. — Gordon Linoff, Dec 20 '20 at 14:23

score 2 · Accepted Answer · answered Dec 20 '20 at 09:16

A check constraint can't reference other tables, so you can achieve what you want only by using 2 separate triggers for INSERT and UPDATE:

CREATE TRIGGER insert_member_number BEFORE INSERT ON members
BEGIN
  SELECT
    CASE
      WHEN NEW.member_number NOT BETWEEN 1 AND (SELECT member_count FROM teams WHERE id = NEW.team_id) 
        THEN RAISE (ABORT, 'Invalid member number')
    END;
END;

CREATE TRIGGER update_member_number BEFORE UPDATE ON members
BEGIN
  SELECT
    CASE
      WHEN NEW.member_number NOT BETWEEN 1 AND (SELECT member_count FROM teams WHERE id = NEW.team_id) 
        THEN RAISE (ABORT, 'Invalid member number')
    END;
END;

Also, I believe you should change the definition of the table members so that the combination of the columns team_id and member_number is UNIQUE:

CREATE TABLE members (
    id INTEGER PRIMARY KEY,
    team_id INTEGER NOT NULL,
    member_number INTEGER NOT NULL,
    UNIQUE(team_id, member_number),
    FOREIGN KEY (team_id) REFERENCES teams (id)
);

See the demo.

Use check constraint on a column based on the value of a column in another table

1 Answers1