Unique pairs of columns like A, B or B, A

Question

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

Obviously, I can put a unique unique index in these columns, but this will ensure uniqueness in order A, B, but not B, A right?

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

As you can see, Red vs. Blue was already indicated as the first entry, and then again indicated as the last. This should be illegal as they will already collide with each other.

Edit: Is there also a way to process SELECT code? Or UPDATE? REMOVE? Etc ..

The idea of ​​the “Home or Guests” team, which may be important here, was also raised. This initial concept came to me, thinking about how to create bracketing on the side of the database.

Answer 1

( ) , , .

, INSERT, , . ROLLBACK, .

Answer 2

, , , , A ( ) B: , {, }, { , }, .

Answer 3

, .

, : -)

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 does is see if there is any sequence A | B or B | A. If so, then the returned account is greater than zero, and the transaction is rolled back and not transferred to the database.

Answer 4

If the same pair (reverse) exists, take the one where TeamA> TeamB.

SELECT DISTINCT TeamA, TeamB 
FROM table t1 
WHERE t1.TeamA > t1.TeamB 
    OR NOT EXISTS (
        SELECT * FROM table t2 
            WHERE t2.TeamA = t1.TeamB AND t2.TeamB = t1.TeamA 
    )