Unique column pairs as A,B or B,A

Question

If I have a joining table with two columns, TeamA and TeamB, how can I ensure that each pair is unique?

Obviously I can put a unique composite index on these columns but that will only ensure uniqueness in the order A,B but not B,A correct?

TeamA | TeamB
-------------
 red  | blue
 pink | blue
 blue | red

As you can see, Red vs. Blue has already been specified as the first record and then it is specified again as the last. This should be illegal since they will already be facing each other.

Edit: Also, is there a way to handle the SELECT case as well? Or the UPDATE? DELETE? Etc..

Also, the idea of Home or Away team has been brought up which may be important here. This initial concept came to me while thinking about how to build a bracketing system on the DB side.

Answer 1

If your RDBMS (you didn't specify) supports Triggers, then create a trigger on that table to enforce your constraint.

Create a trigger that fires on INSERT, that checks if a pair already exists with order reversed. If it does ROLLBACK, else allow the insert.

Answer 2

Define a constraint such that, for example, the value in the A column must be (alphabetically or numerically) smaller than the value in the B column: thus you'd be allowed to insert {blue,red} but not {red,blue} because blue is less than red.

Answer 3

Here is some sample code for use with the trigger method that Mitch described.

I have not tested this code, and it's late at night here :-)

CREATE TRIGGER trig_addTeam
ON Teams
FOR INSERT, UPDATE
AS

DECLARE @TeamA VARCHAR(100) 
DECLARE @TeamB VARCHAR(100)
DECLARE @Count INT

SELECT @TeamA = (SELECT TeamA FROM Inserted)
SELECT @TeamB = (SELECT TeamB FROM Inserted)

SELECT @Count = (SELECT COUNT(*) FROM TEAMS WHERE (TeamA = @TeamA AND TeamB = @TeamB)
                 OR (TeamA = @TeamB AND TeamB = @TeamA))

IF @Count > 0 THEN

BEGIN
    ROLLBACK TRANSACTION
END

What this is doing is looking to see if either sequence of A|B or B|A exists in the current table. If it does then the count returned is greater than zero, and the transaction is rolled back and not committed to the database.