SQL Server, самореферентные данные, как добавить ограничение для этого

Представьте, что у меня есть следующая структура:

DECLARE @Products TABLE ( MemberId INT, ProductId INT, GlobalProductId INT, PRIMARY KEY (MemberId, ProductId)); INSERT INTO @Products VALUES (1, 1, NULL);--this is my "global product" INSERT INTO @Products VALUES (2, 1, NULL);--this is okay INSERT INTO @Products VALUES (2, 2, 1);--this is okay INSERT INTO @Products VALUES (2, 3, 2);--this should fail SELECT * FROM @Products; 

Правило, которое я хочу применить, состоит в том, что MemberId = 1 содержит глобальные продукты, а все другие члены имеют нормальные продукты. Набор нормальных продуктов может быть связан с одним глобальным продуктом.

Поэтому я хочу, чтобы способность продукта-члена была привязана к глобальному продукту, т. Е. Было бы ограничение внешнего ключа, если GlobalProductId не является NULL, тогда должен существовать ProductId, который соответствует GlobalProductId, где MemberId = 1.

В моем примере выше у меня есть один глобальный продукт с ProductId = 1. Затем я создаю три обычных продукта:

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

     SELECT * FROM @Products WHERE MemberId = 1 AND ProductId = 2; 

Я вижу, что самым простым решением было бы создать новую таблицу, в которой не было бы ничего, кроме Global Products. Проблема с этим подходом заключается в том, что у меня есть целый набор подпрограмм для загрузки, обновления, удаления данных из таблицы Product и второго набора процедур для выполнения вычислений и т. Д. Из той же таблицы. Если бы я представил новую таблицу «Глобальные продукты», мне пришлось бы дублировать десятки UDF для этого, и мой код стал бы намного сложнее.

Добавьте вычисленный столбец, который фиксирован как 1 а затем добавьте внешний ключ:

 CREATE TABLE Products ( MemberId INT, ProductId INT, GlobalProductId INT, PRIMARY KEY (MemberId, ProductId), GlobalMemberId AS 1 PERSISTED, FOREIGN KEY (GlobalMemberId,GlobalProductID) references Products (MemberId,ProductID) ); INSERT INTO Products VALUES (1, 1, NULL);--this is my "global product" INSERT INTO Products VALUES (2, 1, NULL);--this is okay INSERT INTO Products VALUES (2, 2, 1);--this is okay INSERT INTO Products VALUES (2, 3, 2);--this should fail SELECT * FROM Products; 

Это дает следующие результаты:

Msg 547, уровень 16, состояние 0, строка 1

Заявление INSERT противоречило ограничениям FOREIGN KEY FK__Products__7775B2CE TABLE « FK__Products__7775B2CE ». Конфликт произошел в базе данных « abc », таблица « dbo.Products ».

Заявление было прекращено.

 MemberId ProductId GlobalProductId GlobalMemberId ----------- ----------- --------------- -------------- 1 1 NULL 1 2 1 NULL 1 2 2 1 1 

Почему бы просто не добавить ограничение CHECK :

 ALTER TABLE Products ADD CONSTRAINT CHK_ColumnD_GlobalProductId CHECK (GlobalProductId IS NULL AND MemberId = 1 OR GlobalProductId IS NOT NULL AND MemberId != 1); 

и FOREIGN KEY :

 ALTER TABLE Products ADD CONSTRAINT fk_SelfProducts FOREIGN KEY (GlobalProductId ) REFERENCES Products (ProductId) 
  • Получить список ограничений SQL Server без избыточности
  • Уникальное ограничение внутри группы записей, где какое-то значение одинаково
  • UNIQUE CONSTRAINT в столбце из внешней таблицы в SQL Server 2008
  • SQL для предотвращения несовпадающих внешних ключей
  • Сложная проблема SQL Server
  • Как создать контрольное ограничение, которое обеспечивает одну активную запись подробностей для таблицы?
  • Ограничение внешнего ключа в дочерней таблице позволяет вставить значения, которые не существуют в родительской таблице
  • Проверить ограничение - разрешить только один столбец, если другой столбец верен
  • Проверьте, существует ли запись при вставке, используя SQL-тип таблицы sql-сервера
  • Sql Server - Constraint - разрешить установку столбца A только в том случае, если столбец B является нулевым и наоборот
  • Помощь с ограничением
  • Давайте будем гением компьютера.