Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 check constraint  [new]
Lasa
Member

Откуда: Canada
Сообщений: 81
Никак не соображу как сделать check constraint :(

Есть таблица с полями:
ContactID - primary key (контакт)
AccountID - foreighn key (компания)
IsPrimary - bit (главный контакт?)

Должно выполняться условие, что в компании (AccountID) может быть только один главный контакт (IsPrimary=1) все остальные IsPrimary=0

Понятно, что можно с помощью тригера, но хотелось бы без него обойтись
14 фев 12, 02:48    [12086045]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
ChA
Member

Откуда: Москва
Сообщений: 11319
Например так
create table t (
ContactID int primary key
, AccountID int -- foreighn key (компания)
, IsPrimary bit -- (главный контакт?)
, OnlyOne AS (CASE IsPrimary WHEN 0 THEN ContactID END)
, UNIQUE(AccountID, OnlyOne)
)
insert into t (ContactID, AccountID, IsPrimary) VALUES(1, 1, 1)
insert into t (ContactID, AccountID, IsPrimary) VALUES(2, 1, 1)
insert into t (ContactID, AccountID, IsPrimary) VALUES(3, 1, 0)
insert into t (ContactID, AccountID, IsPrimary) VALUES(4, 1, 0)
SELECT * FROM t
GO
drop table t
Но лучше так не делать.
14 фев 12, 04:12    [12086084]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
CREATE UNIQUE INDEX uiTAccountID ON T(AccountID) WHERE IsPrimary=1;
14 фев 12, 09:11    [12086315]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
Lasa
Member

Откуда: Canada
Сообщений: 81
Спасибо, переклинило меня на этой constraint, нет чтобы про индексы подумать
14 фев 12, 21:16    [12092010]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
Crimean
Member

Откуда:
Сообщений: 13147
ChA
Но лучше так не делать.


а если вичсляемое поле PERSISTED объявить? :)
14 фев 12, 21:28    [12092063]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
ChA
Member

Откуда: Москва
Сообщений: 11319
Crimean
ChA
Но лучше так не делать.
а если вичсляемое поле PERSISTED объявить?
Зависит от версии. Она не была озвучена, предложенный вариант будет работать на всех, начиная с SQL2K. На последних версиях, вариант от iap выглядит лучше. Но, вообще, речь шла о другом. Добавление поля IsPrimary создаёт межстрочную зависимость. В подобных случаях было бы лучше вынести информацию об основном контакте в отдельную таблицу MainContact с полями (ContactID, AccountID) с PK по AccountID.
14 фев 12, 23:09    [12092364]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
Lasa
Member

Откуда: Canada
Сообщений: 81
MS SQL 2008 R2 - 10.50.1600.1
автор
Добавление поля IsPrimary создаёт межстрочную зависимость. В подобных случаях было бы лучше вынести информацию об основном контакте в отдельную таблицу MainContact с полями (ContactID, AccountID) с PK по AccountID

Главный контакт часто меняется, в смысле сегодня он главный, а завтра может другой контакт получить этот признак. Хотя часто, это по-человеческим меркам, не для базы данных конечно, это небольшое внутрикорпоративное приложение, пользователей не больше 50.
А чем тяжела такая "межстрочная зависимость"? Это хуже, чем при изменении статуса, вместо двух Update, делать Delete из одной таблицы, а потом Insert в другую (два раза)? И запросы на Select всех контактов надо будет с Union делать.
15 фев 12, 00:12    [12092576]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Lasa, сделайте индексированное представление
create view dbo.vPrimaryContacts
with schemabinding
as
select
 AccountID,
 ContactID
from
 dbo.t
where
 IsPrimary = 1;
go
create unique clustered index IX_t__AccountID on dbo.vPrimaryContacts(AccountID);
go

Убьете сразу много зайцев.
15 фев 12, 00:26    [12092621]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
invm,

А почему не фильтрованный индекс?
15 фев 12, 00:31    [12092639]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Гавриленко Сергей Алексеевич,
На мой взгляд, с точки зрения дальнейшего использования, представление удобнее.
15 фев 12, 00:50    [12092696]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
ChA
Member

Откуда: Москва
Сообщений: 11319
Lasa
А чем тяжела такая "межстрочная зависимость"? Это хуже, чем при изменении статуса, вместо двух Update, делать Delete из одной таблицы, а потом Insert в другую (два раза)? И запросы на Select всех контактов надо будет с Union делать.
Mea culpa. Полагал очевидным, что переносится не сама запись контакта в таблицу MainContact, а только сама связь, определяющая главный контакт. Т.е., сам факт наличия этой связи. Вся остальная информация по-прежнему остается в таблице контактов. Т.е., при изменении главного контакта происходит либо один update, если таковой уже был ранее, либо один insert, если не было, либо один delete, если они все равноправны. Вкратце, смысл заключается в том, чтобы один факт хранился в одном месте. Чисто семантически, есть контакт, который мы выделяем по какому-то соображению, но зачем всем остальным контактам приписывать то, что мы их не выделяем по тому же самому соображению ?
Что ещё важно, если мы вдруг, опять же, по каким-то практическим соображением откажемся от такого выделения, в таблице контактов ничего не изменится, просто удалим таблицу MainContact и всё. В общем, можно было бы ещё много аргументов понаписать, в том числе теоретических, но смысла, извините, не вижу. Вы уже приняли решение и вам с ним жить.
15 фев 12, 01:50    [12092827]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
Lasa
Member

Откуда: Canada
Сообщений: 81
Спасибо за подробный ответ. В том то и дело, что я только начала эту базу и никаких окончательных решений еще нет, и сейчас есть возможность сделать все как надо/лучше/оптимальней и т.п.
+
Раньше было Windows приложение, но так как писалось оно на коленке, без документации, то и база соответсвенно так же "развивалась". А сейчас решили "перенести его на web", т.е фактически переписать, но базу я не стала автоматически брать ту же самую, а делаю ее ну не совсем с нуля,а основываясь на уже работающей. Хочется избежать хотя бы уже известных сложностей и предупредить как можно больше еще не выявленных.
С этим главным контактом дурацкое требование. У компании должен быть хотя бы один контакт, среди контактов компании обязательно должен быть главный контакт, главный контакт в компании должен быть только один. Если ввести компанию без главного контакта - она не появляется в списке компаний, если сделать несколько главных контактов - в списке появится несколько дублей этой компании с разными главными. Я все это отслеживала на уровне клиента (C#), но базой стали пользоваться и другие приложения (написанные не мной) и хотя документация есть, но .. В общем, надоели разбирательства, хочу максимально все сделать на стороне сервера, пусть получают ошибки и обрабатывают как надо.
15 фев 12, 02:49    [12092911]     Ответить | Цитировать Сообщить модератору
 Re: check constraint  [new]
ChA
Member

Откуда: Москва
Сообщений: 11319
Lasa
В том то и дело, что я только начала эту базу и никаких окончательных решений еще нет, и сейчас есть возможность сделать все как надо/лучше/оптимальней и т.п.
Ну тогда старайтесь делать сразу всё правильно, "...чтобы потом не было мучительно...". При построении модели желательно максимально абстрагироваться от СУБД, на которой будет реализована БД. Сначала правильность. Об оптимизации на начальном этапе лучше вообще не думать. Только сущности и связи, потом логическая модель, в терминах абстрактной реляционной СУБД: таблицы, поля, ограничения. И только на этапе физической модели, на базе конкретной СУБД, уже можно подумать об оптимизации в рамках возможностей сервера.

P.S. Вопросы проектирования БД обычно обсуждаются в другом форуме.
15 фев 12, 04:01    [12092938]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить