Many-to-many relation - automatically delete orphans

Question

Assume a many-to-many relation between team and player. This is modelled by the following tables:

create table team
(
    identifier integer primary key
);

create table player
(
    identifier integer primary key
);

create table member
(
    team_identifier integer,
    player_identifier integer,

    primary key(team_identifier, player_identifier),
    foreign key(team_identifier) references team on update cascade on delete cascade,
    foreign key(player_identifier) references player on update cascade on delete cascade
);

Assume the following data:

insert into team values(1);
insert into team values(2);

insert into player values(1);

insert into member values(1, 1);
insert into member values(2, 1);

Let's delete the teams:

delete from team where identifier = 1;
delete from team where identifier = 2;

Now we have a player without a team. Is there a way to automatically delete this player? That is, when the deletion of a team results in an orphan player, this player should be removed as well (but not the other way around).

Answer 1

ideal solution to settle your problem create after-delete trigger or update in membre:

CREATE FUNCTION delete_player_not_in_member() RETURNS trigger AS $delete_player_not_in_member$
BEGIN
    DELETE FROM player WHERE Identifier NOT IN (SELECT player_identifier FROM membre);
    RETURN OLD;
END;
$delete_player_not_in_member$ LANGUAGE plpgsql;

CREATE TRIGGER delete_player_not_in_member AFTER DELETE OR UPDATE ON member
FOR EACH ROW EXECUTE PROCEDURE delete_player_not_in_member();