Make two columns unique both ways

Question

I'm creating a database which stores football matches.

Each round, any given football club may only play once (home or away).

For example, after the insert of

INSERT INTO Match (Round, Home, Away) VALUES (1, Barcelona, Real);

inserts such as

INSERT INTO Match (Round, Home, Away) VALUES (1, Bayern, Barcelona);

INSERT INTO Match (Round, Home, Away) VALUES (1, Barcelona, Bayern);

must be forbidden since Barcelona has already played one match against Real in Round 1. They shouldn't be able to play against Bayern in the same round (doesn't matter if they're playing home or away).

How can I ensure such inserts don't happen?

Do I use a trigger? Some sort of unique index combination? I've tried both but got lost and didn't actually get the outcome expected.

Thanks

Answer 1

With 2 unique constraints you can achieve the desired restriction :

create unique index on match (round, least(home, away));
create unique index on match (round, greatest(home, away));

Check the fiddle here

Answer 2

You can use a trigger to enforce this constraint. The trigger should check if the club has already played in the same round, and if so, prevent the insert from happening.

Here's an example of how the trigger might look:

CREATE TRIGGER check_club_played
BEFORE INSERT ON Match
FOR EACH ROW
BEGIN
-- check if club has already played in this round
IF EXISTS (SELECT 1 FROM Match
WHERE Round = NEW.Round AND (Home = NEW.Home OR Away = NEW.Away)) THEN
-- prevent insert by raising an error
RAISE_APPLICATION_ERROR(-20000, 'Club has already played in this round');
END IF;
END;

This trigger checks if the club has already played in the same round, and if so, raises an error to prevent the insert from happening.

Alternatively, you can use a unique index to enforce this constraint. You can create a unique index on the combination of Round, Home, and Away, which will ensure that no two matches have the same round, home, and away club.

Here's an example of how to create this unique index:

CREATE UNIQUE INDEX unique_match_round_home_away ON Match (Round, Home, Away);

This will prevent any inserts that would violate the constraint, by raising a unique constraint violation error.

Both approaches will work to enforce this constraint, so you can choose the one that makes more sense for your application.