SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE teams (
    id INTEGER PRIMARY KEY,
    member_count INTEGER NOT NULL
);
✓
INSERT INTO teams (member_count) VALUES (2);
✓
SELECT * FROM teams;


id
member_count


1
2


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)
);
✓
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;
✓
INSERT INTO members(team_id, member_number) VALUES
  (1, 1), (1, 2)
✓
SELECT * FROM members


id
team_id
member_number


1
1
1

2
1
2


INSERT INTO members(team_id, member_number) VALUES
  (1, 3)

{
  "code": "SQLITE_CONSTRAINT_TRIGGER"
}


UPDATE members 
SET member_number = 3
WHERE team_id = 1

{
  "code": "SQLITE_CONSTRAINT_TRIGGER"
}