Применить максимальное количество дочерних строк

Если в базе данных есть пара таблиц в типичной «родительской и дочерней» манере, есть ли способ обеспечить (без использования триггеров), что у каждого родителя может быть максимум, скажем, четыре дочерних?

Итак, у нас есть таблица Parents:

create table dbo.Parents (
    ParentID char(2) not null primary key
    /* Yada Yada Yada */
)

И Детская таблица:

create table dbo.Children (
    ChildID char(2) not null primary key,
    ParentID char(2) not null references dbo.Parents (ParentID)
    /* usw */
)

И мы заполняем таблицу Parents:

insert into Parents (ParentID) values ('aa'),('bb')

Я хочу, чтобы эта вставка прошла успешно:

insert into Children (ChildID,ParentID) values
('a1','aa'),('a2','aa'),('a3','aa')

Но чтобы эта вставка не удалась:

insert into Children (ChildID,ParentID) values
('b1','bb'),('b2','bb'),('b3','bb'),('b4','bb'),('b5','bb')

sql-server referential-integrity
person Damien_The_Unbeliever    schedule 07.05.2014    source источник

Ответы (1)


arrow_upward
4
arrow_downward

Есть способ сделать это без использования триггеров, но это уродливый код, и я не уверен, что буду рекомендовать его использовать в гневе.

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

Но вот, для вашего удовольствия, вид:

create view dbo.DRI_Parent_Child_Maximum4
with schemabinding
as
    select ParentID,COUNT_BIG(*) as Cnt,
           SUM(536870911) as Meaningless
    from dbo.Children
    group by ParentID
go
create unique clustered index IX_DRI_Parent_Child_Maximum4
on dbo.DRI_Parent_Child_Maximum4(ParentID)

И сообщение об ошибке, которое он выдает для второй вставки:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.

В случае, если это не очевидно, это работает, устраивая 4 * некоторое магическое число меньше, чем максимальное число, которое может быть представлено int, но 5 * то же самое магическое число больше, чем максимум.

Итак, если есть 4 или меньше строк, SUM() работает, но если вы попытаетесь иметь 5 или более строк, мы получим ошибку.

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

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

person Damien_The_Unbeliever    schedule 07.05.2014