How to enforce this constraint in sql server

Question

I have a table called city, and a table called city_city. city_city correlates two city records, so it has a fromcity_id and a tocity_id. I can enforce uniqueness on fromcity_id and and tocity_id through a unique key, but how do I enforce uniqueness so that I cant insert a record if fromcity_id and tocity_id are reversed.

For example, the following records are conceptually the same:

id    fromcity_id  tocity_id
1     100          200
2     200          100

Answer 1

This will require a trigger, because you need to examine other rows. Alernately, you could do this at the application layer.

Answer 2

Another option is to create an indexed view with a unique constraint on the view (horrid, but would also do the job). The view would be something like:

CREATE VIEW vwSomeView WITH SCHEMABINDING
AS
    SELECT fromcity_id AS 'C1', tocity_id AS 'C2' FROM city_city
    UNION ALL
    SELECT tocity_id AS 'C1', fromcity_id AS 'C2' FROM city_city

Then create a unique index on the view:

CREATE UNIQUE INDEX UIX_vwSomeView ON vwSomeView (C1, C2)

The question is whether this has more, less, or about the same overhead as a trigger that selects the table on each insert. The answer to this is to profile and test for your workload, data volumes and throughput.

Answer 3

Add a check constraint that enforces (fromcity_id < tocity_id) and you are all set