In SQL, can you add a foreign key constraint on a set of attributes, so that all of them must be on the same row of the referenced table?

Question

I have the following scenario: there are developers studios, each of which made games and every game has a set of maps. The maps are not unique to a game as there are maps that were remade in different games by different developers studios. Though, a game is unique to a studio as in my scenario no studio ever remade an already existing game from another studio.

Every map then has a set of solvers. Two different maps of the same game can have solvers with the same name, and the same map from different games (a remake, for instance) can have solvers with the same name.

This is my schema as of now:

CREATE TABLE IF NOT EXISTS Studio 
(
    name TEXT NOT NULL,
    position INT UNIQUE NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    PRIMARY KEY (name)
);

CREATE TABLE IF NOT EXISTS Game 
(
    name TEXT NOT NULL,
    position INT NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    studio TEXT NOT NULL,
    PRIMARY KEY (name),
    FOREIGN KEY (studio) REFERENCES Studio(name)
);

CREATE TABLE IF NOT EXISTS Map 
(
    name TEXT NOT NULL,
    position INT NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    game TEXT NOT NULL,
    PRIMARY KEY (name, game),
    FOREIGN KEY (game) REFERENCES Game(name)
);

CREATE TABLE IF NOT EXISTS Solver 
(
    name TEXT NOT NULL,
    position INT NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    map TEXT NOT NULL,
    game TEXT NOT NULL,
    PRIMARY KEY (name, map, game),
    FOREIGN KEY (map) REFERENCES Map(name),
    FOREIGN KEY (game) REFERENCES Game(name)
);

With the given schema, assume you want to insert a new row on table Solver: the DBMS will check whether or not a map with primary key NEW.map exists, and a game with primary key NEW.game exists.

This is useful, but not quite what I need: I would like that before insert on table Solver of a new row, the DBMS checked the existence of exactly one row in Map having name = NEW.map AND game = NEW.game.

I know I could create a trigger for this, but I would like to know if there exists a syntax that allows me to achieve the same result, so I can leverage CASCADE DELETE and UPDATE without creating a custom trigger for it.

Answer 1

Since the primary key of Map is (name, game), you can reference this tuple from the solver in one single foreign key:

CREATE TABLE IF NOT EXISTS Solver (
    name TEXT NOT NULL,
    position INT NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    map TEXT NOT NULL,
    game TEXT NOT NULL,
    PRIMARY KEY (name, map, game),
    FOREIGN KEY (map, game) REFERENCES Map(name, game)
);

This ensures that one single row in Map is referenced.