CODEWITHSUNDEEP
sqlsql-server
Mark Micallef
Mark Micallef

Reputation: 2797

Efficient way to CHECK UNIQUE on two columns?

I have a table in SQL Server 2012 which has columns for two countries, let's call them CountryA and CountryB.

CREATE TABLE dbo.AgreementParticipants
(
    AgreementParticipantsID INT NOT NULL PRIMARY KEY IDENTITY,
    CountryA CHAR(3) NOT NULL FOREIGN KEY REFERENCES dbo.Country (CountryCode),
    CountryB CHAR(3) NOT NULL FOREIGN KEY REFERENCES dbo.Country (CountryCode)
);

Note: the table has been simplified for the example, however the important thing is that is always defines a bilateral relationship between two entities. It just so happens that in this case it's two countries.

Typical data:

1 AUS USA
2 USA NZL

Business rules:

  1. There will always be exactly two counties.
  2. The two countries can be in either order. There is no significance to position A or B.
  3. Each combination of two countries must be unique to the table. Therefore, AUS USA is considered the same as USA AUS.
  4. The two countries will never be the same.

The question:

What is the most efficient way to enforce the uniqueness constraint?

Currently, I've considered using a trigger or a convoluted check constraint, but both solutions feel inelegant so I'd like some input from the community.

Upvotes: 2

Views: 288

Answers (3)

StuartLC
StuartLC

Reputation: 107317

Gordon's great second alternative can be simplified by to one computed column, by projecting a unique 'hash' per combination of CountryA, CountryB:

ALTER TABLE dbo.AgreementParticipants ADD CountryProjection AS
CASE WHEN CountryA < CountryB 
          THEN CountryA + '-' + CountryB 
          ELSE CountryB + '-' + CountryA
        END;

ALTER TABLE dbo.AgreementParticipants ADD CONSTRAINT U_AgreementParticipants 
UNIQUE (CountryProjection);

SqlFiddle here

Upvotes: 1

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

Constraints like:

CHECK ( CountryA < CountryB ) UNIQUE ( CountryA, CountryB )

is one way to do it

Another way is to add computed columns like:

CREATE TABLE t2 (CountryA ..., CountryB ... ,least_country as LEAST( CountryA, CountryB ) ,greatest_country as GREATEST( CountryA, CountryB ) )

and then put a unique constraint on the new columns. Not sure whether SQLServer supports LEAST, but it is easy to create one if it does not.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can do this with two conditions. The first is a unique index (or constraint):

create unique index AgreementParticipants_CountryA_CountryB on AgreementParticipants(CountryA, CountryB)

The second is a condition that CountryA is less than CountryB:

check (CountryA < CountryB)

If the countries could be the same, then the condition would be <=.

One disadvantage of this approach is that, on insert, CountryA has to be less than CountryB (alphabetically first). Otherwise, this generates an error.

An alternative approach uses computed columns and a unique index:

alter table AgreementParticipants
    add Country1 as (case when CountryA < CountryB then CountryA else CountryB end);

alter table AgreementParticipants
    add Country2 as (case when CountryA < CountryB then CountryB else CountryA end);

create unique index AgreementParticipants_Country1_Country2 on AgreementParticipants(Country1, Country2);

The advantage of this approach is that the countries can be inserted into the table in either order.

Upvotes: 4

Related Questions