Добавьте ограничение SQL XOR между двумя FK, допускающими значение NULL.

Я хотел бы определить ограничение между двумя обнуляемыми FK в таблице, где, если один имеет значение null, другому требуется значение, но оба не могут быть нулевыми, и оба не могут иметь значений. Логика заключается в том, что производная таблица наследует данные от любой из таблиц FK, чтобы определить ее тип. Кроме того, для забавных бонусных очков, это плохая идея?


sql database-design sql-server-2005
person proggrock    schedule 20.06.2012    source источник
comment
Вы также можете рассмотреть шаблон супертипа/подтипа, показанный здесь   -  person Martin Smith    schedule 21.06.2012

Ответы (3)


arrow_upward
19
arrow_downward

Один из способов добиться этого — просто записать, что на самом деле означает «исключающее ИЛИ»:

CHECK (
    (FK1 IS NOT NULL AND FK2 IS NULL)
    OR (FK1 IS NULL AND FK2 IS NOT NULL)
)

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

CHECK (
    1 = (
        (CASE WHEN FK1 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK2 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK3 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK4 IS NULL THEN 0 ELSE 1 END)
        ...
    )
)

Кстати, этот шаблон можно использовать в законных целях, например этот (хотя и неприменим к MS SQL Server из-за отсутствия отложенных ограничений). Является ли это законным в вашем конкретном случае, я не могу судить на основании предоставленной вами информации.

person Branko Dimitrijevic    schedule 20.06.2012
comment
проще: CHECK(fk1 IS NULL != fk2 IS NULL) - person Stephen Fuhry; 11.01.2015
comment
@StephenJ.Fuhry К сожалению, MS SQL Server не рассматривает логический тип как гражданин первого класса, поэтому такой синтаксис не будет принят. - person Branko Dimitrijevic; 18.01.2015
comment
но он работает в PostgreSQL, для чего он мне и нужен. спасибо за короткое выражение, @StephenJ.Fuhry. конечно, было бы лучше иметь XOR. учитывая, сколько других вещей было загружено в систему, это кажется странным упущением. - person mARK; 24.10.2015

arrow_upward
1
arrow_downward

Вы можете использовать ограничение проверки:

create table #t (
   a int,
   b int);

alter table #t add constraint c1 
check ( coalesce(a, b) is not null and a*b is null );

insert into #t values ( 1,null);

insert into #t values ( null ,null);

Бег:

The INSERT statement conflicted with the CHECK constraint "c1".
person dani herrera    schedule 20.06.2012
comment
coalesce(a, b) вернет не-NULL, когда оба a и b не-NULL, и ПРОВЕРКА будет пройдена, чего не должно быть (OP явно указал, что оба не могут иметь значений). Другими словами, insert into #t values (1, 1) должен выйти из строя. - person Branko Dimitrijevic; 21.06.2012
comment
единственным ограничением является арифметическое переполнение (от a*b), которое можно убрать, если вместо него использовать a|b - person PLopes; 29.06.2020

arrow_upward
-1
arrow_downward

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

person user98534    schedule 20.06.2012