Уникальное ограничение на два столбца независимо от порядка

У меня есть следующее определение таблицы:

CREATE TABLE [Car] 
(
   CarID int NOT NULL PRIMARY KEY IDENTITY(1,1),
   FirstColorID int FOREIGN KEY REFERENCES Colors(ColorID),
   SecondColorID int FOREIGN KEY REFERENCES Colors(ColorID),

   UNIQUE(FirstColorID, SecondColorID)
)

Я хочу, чтобы два столбца Color были уникальными, независимо от комбинации, в которой они появляются.

Например. попытка:

INSERT INTO Car (FirstColorID, SecondColorID) VALUES (1, 2); --should succeed

но затем попытка сделать то же самое после того, как эта первая запись существует с обратными цветами, должна завершиться неудачей:

INSERT INTO Car (FirstColorID, SecondColorID) VALUES (2, 1); --should violate constraint/check

Я решил эту проблему с помощью кода высокого уровня, но я бы предпочел, чтобы ограничение применялось непосредственно в базе данных, желательно без использования таких вещей, как триггеры.

Спасибо.


sql-server unique-constraint
person Ryan Weir    schedule 10.07.2012    source источник

Ответы (4)


arrow_upward
2
arrow_downward

Возможно, вы могли бы создать вычисляемый столбец, например:

CREATE TABLE [Car] 
(
   CarID int NOT NULL PRIMARY KEY IDENTITY(1,1),
   FirstColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
   SecondColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
   xColor As Cast(Case When FirstColorID > SecondColorID Then FirstColorID Else SecondColorID End as varChar) + ',' + 
        Cast(Case When FirstColorID <= SecondColorID Then SecondColorID Else FirstColorID End as varChar),
   UNIQUE(xColor)
)

ОБНОВЛЕНИЕ (вы должны протестировать его раньше, я провел очень быстрое тестирование)

Идея:

Целое число 4 байта. Если я сложу 2 целых числа вместе, я получу 8 байт. Если я закажу их, я получу уникальное значение BigInt (8 байт).

Итак, что я делаю:

  1. Я удостоверяюсь, что они в правильном порядке
  2. Я сдвигаю байты на 32 бита влево (так что, просто умножая 4294967296, я получаю то, что хочу)
  3. Я делаю логическое ИЛИ, поэтому я получаю 8-байтовое значение BigInt, которое всегда должно быть уникальным!

So:

CREATE TABLE [Car] 
(
   CarID int NOT NULL PRIMARY KEY IDENTITY(1,1),
   FirstColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
   SecondColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
   xColor As 
       Case When FirstColorID > SecondColorID Then 
            Cast(FirstColorID as BigInt) * Cast(4294967296 as BigInt) | Cast(SecondColorID as BigInt)
        Else 
            Cast(SecondColorID as BigInt) * Cast(4294967296 as BigInt) |  Cast(FirstColorID as BigInt)
        End
  UNIQUE(xColor)
)
person Jānis    schedule 10.07.2012
comment
Мне нравится этот подход, но я не уверен в использовании вычисляемых строковых столбцов на основе производительности. Может быть, вместо этого можно использовать какую-то числовую операцию? Я предполагаю, что это не может быть просто умножение чисел, будут всевозможные коллизии. - person Ryan Weir; 10.07.2012
comment
Спасибо, это то, что я собираюсь с. Мне нравится этот подход, потому что он полностью автономен в определении таблицы и должен быть достаточно производительным. - person Ryan Weir; 10.07.2012

arrow_upward
2
arrow_downward

Полностью избегайте этой проблемы, введя ограничение, требующее, чтобы SecondColorID >= FirstColorID. Это создает ограничение на то, что может войти в базу данных, которое немного выходит за рамки того, что вы ищете. Но это дополнительное требование будет полезно в будущем, если вы хотите запросить определенный цветовой шаблон, потому что вы можете просто искать SecondColorID = 1 AND FirstColorID = 2 вместо того, чтобы писать дополнительную логику для учета двух идентификаторов цветов, имеющих непредсказуемый порядок.

CREATE TABLE [Car] 
(
   CarID int NOT NULL PRIMARY KEY IDENTITY(1,1),
   FirstColorID int FOREIGN KEY REFERENCES Colors(ColorID),
   SecondColorID int FOREIGN KEY REFERENCES Colors(ColorID),

   UNIQUE(FirstColorID, SecondColorID)
)

ALTER TABLE [Car] WITH CHECK
    ADD CHECK (SecondColorID >= FirstColorID);
person Sean U    schedule 10.07.2012
comment
Это творческий способ сделать это. Но теперь мне понадобится мой код высокого уровня, чтобы выбрать порядок внешних ключей только для вставки/обновления. Это лучше, чем то, что у меня было, но все еще имеет ту же основную проблему. - person Ryan Weir; 10.07.2012
comment
Лучшим вариантом может быть позаботиться о любых подтасовках параметров в ваших хранимых процедурах. - person Sean U; 10.07.2012

arrow_upward
1
arrow_downward

Создайте вычисляемые столбцы, чтобы ComputedFirstColorID был меньшим из FirstColorID и SecondColorID, а ComputedSecondColorID был большим из FirstColorID и SecondColorID. Теперь индекс

CREATE UNIQUE NONCLUSTERED INDEX index_name ON Car (ComputedFirstColorID, ComputedSecondColorID)

Все должно быть в порядке.

person Meff    schedule 10.07.2012

arrow_upward
0
arrow_downward 

CREATE UNIQUE NONCLUSTERED INDEX index_name ON Car (FirstColorID, SecondColorID)

http://msdn.microsoft.com/en-us/library/ms188783.aspx

person Nicole Castle    schedule 10.07.2012
comment
Это предотвратит две записи (1,2), но когда (1,2) уже есть в таблице, это ограничение не останавливает от ввода (2,1)..... - person marc_s; 10.07.2012