CHECK CONSTRAINT в MS SQL — Грабли по которым мы прошлись

[ComodoHacker]

одна дружная команда веб разработчиков, не имея в своём составе опытного SQL разработчика, добавила Check Constraint в таблицу и прошлась по нескольким простым, но не сразу очевидным граблям.

Да вы, похоже, скупили все грабли в ближайшем Оби.

Ваше исходное ограничение «только один мужчина в гареме» правильно реализуется с помощью уникального констрейнта, не CHECK. В данном случае это уникальность для вычисляемого поля, обеспечивающего срабатывание ограничения только для мужчин. Что-то вроде:

create table Persons (
  Id Int Identity primary key,
  FirstName Varchar(50),
  LastName Varchar(50),
  Gender Char(1),
  UKey as case when Gender = 'M' then LastName 
    else cast(Id as varchar(50)) end UNIQUE
);

SQLFiddle

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

P.S. Судя по скриншотам, ваша таблица создавалась не для любых гаремов, а только для одного гарема с фамилией Пирсон. :)

[david_off]

Пример, 1 в 1 повторяющий нашу ситуацию я не стал приводить здесь ибо он бы очень сильно отвлёк от сути. И то что я привел, похоже не самая удачная аналогия. Если же говорить о конкретно нашей проблеме, то нам нужно было проверять уникальность строк, по критерию, который нужно вычислять на основе JOIN-а нескольких таблиц. Поэтому и пришлось использовать скалярную функцию.

[ComodoHacker]

Еще и нескольких таблиц? Это уже говорит о серьезных проблемах в дизайне этих таблиц (IMHO).