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?

Answer 1

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.