CODEWITHSUNDEEP
sqlpostgresqlmany-to-many
Jade Dezo
Jade Dezo

Reputation: 429

Many-to-many relation - automatically delete orphans

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).

Upvotes: 3

Views: 1754

Answers (3)

cFreed
cFreed

Reputation: 4484

The most simple is to add a trigger which tracks team deletions, like this:

CREATE TRIGGER drop_orphan_player
  AFTER DELETE ON team FOR EACH ROW
  DELETE FROM player 
  WHERE id NOT IN (
    SELECT DISTINCT player_id FROM member
  );

See it working at SQLfiddle.

Upvotes: 0

Mesbah Gueffaf
Mesbah Gueffaf

Reputation: 558

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();

Upvotes: 1

Santiago
Santiago

Reputation: 714

Well not necessarily. The player could still be considered a player who needs a team if you plan on adding him to a team at a later time. Or, if you want a pull of available players.

However, you could try using

DELETE FROM member WHERE team_identifier = 1
DELETE FROM member WHERE team_identifier = 2

DELETE FROM player
WHERE Identifier NOT IN (SELECT player_identifier FROM member)

Upvotes: 0

Related Questions