Как применить ограничение CHECK для нескольких таблиц

У меня есть база данных, которая записывает информацию о разведении коров в Microsoft SQL Server 2012 Express. Очевидно, что корову нельзя разводить до тех пор, пока она не родится, и ее можно разводить несколько раз в течение жизни; и мне нужно применить эти ограничения в моей базе данных. В настоящее время я организовал схему в соответствии со следующей диаграммой:

Схема базы данных Cow

DataID — первичный ключ для всех животных. Я попытался реализовать наследование Table-Per-Type, следовательно, отношение 1-к-1 между [Animals].[Master] и [Animals].[Females]. Поскольку каждую самку можно скрещивать несколько раз, я установил отношение 1 ко многим между [Animals].[Females] и [Breedings].[Breedings].

Мой вопрос: как я могу применить правило, согласно которому для всех женщин BirthDate ‹ Breedings.Date?

По сути, мне нужно что-то вроде следующего псевдокода (который я фактически поместил в поле «выражение» ограничения CHECK и получил ошибку проверки):

[Animals].[Master].[BirthDate] < [Breedings].[Breedings].[Date]
INNER JOIN [Animals].[Master] ON
[Breedings].[Breedings].[DataID] = [Animals].[Master].[DataID]

Я также попытался создать представление с правильным соединением, но обнаружил, что ограничения CHECK нельзя использовать в представлениях.

Итак, кто-нибудь знает, как я могу обеспечить соблюдение этих ограничений?

EDIT. Я попробовал использовать триггеры, но не смог правильно сформулировать синтаксис триггера. Вот мой код:

USE [CowInventory];
GO
CREATE TRIGGER [Breedings].[iCheckBreedingDateAfterBirthDate]
ON [Breedings].[Breedings]
FOR INSERT
AS
BEGIN
    DECLARE @CowID UniqueIdentifier
    SELECT @CowID = DataID FROM inserted;

    DECLARE @CowBirthDate Date
    SELECT @CowBirthDate = BirthDate FROM [Animals].[Master] WHERE [Master].[DataID] = @CowID

    DECLARE @BreedingDate Date
    SELECT @BreedingDate = Date FROM inserted;

    IF(@CowBirthDate > @BreedingDate)
        BEGIN
            THROW;
        END
END

Согласно моей книге (SQL Server 2012 Step by Step), этот синтаксис должен работать идеально. Но вместо этого SQL Server дает мне розовые строки под THROW и последними END, указывающими Incorrect syntax near 'THROW'. Expecting CONVERSATION, DIALOG, DISTRIBUTED, or TRANSACTION. и Incorrect syntax near 'END'. Expecting CONVERSATION.. Я вставил эти ключевые слова, но они ничего не меняют.


database sql-server tsql check-constraints
person Milliron X    schedule 27.01.2015    source источник
comment
Эти вопросы и ответы дадут вам несколько идей; stackoverflow.com/questions/16314372/   -  person Rhys Jones    schedule 27.01.2015
comment
@Tanner Primary-to-Primary предназначен для отношений типа 1::1 или 1::0/1.   -  person Rhys Jones    schedule 27.01.2015
comment
НЕТ. Ваш триггер не правильный. В вашей таблице есть скалярные значения, что означает, что ваш триггер не может обрабатывать несколько операций со строками. См. пример, который Алсин опубликовал в своем ответе. Он написан таким образом, чтобы иметь дело с несколькими строками.   -  person Sean Lange    schedule 06.02.2015
comment
+20 это отличный вопрос, и он показывает некоторые ограничения реляционных баз данных. Многие блестящие умы задавались тем же вопросом. См. следующие научные статьи (не ответ, а то, почему это хороший вопрос): dl.acm.org/citation.cfm?id=320571 и dl.acm.org/citation.cfm?id=1366103   -  person Reversed Engineer    schedule 24.11.2015

Ответы (2)


arrow_upward
4
arrow_downward

Вы можете создать триггеры в таблице Breedings для проверки этого правила. Триггер — это специальная хранимая процедура, которая автоматически выполняется при выполнении INSERT\UPDATE\DELETE в некоторой таблице. Таким образом, вы можете написать триггер, который проверяет все новые строки, вставленные в разведение, и если есть строка, в которой дата меньше соответствующей даты рождения, выдает ошибку. То же самое для ОБНОВЛЕНИЯ, если столбец «Дата» изменен, проверьте дату рождения соответствующего животного и выдайте ошибку соответственно. DELETE безопасны в этом вопросе.

ПРОВЕРКИ не так хороши для правил, которые включают другие таблицы. Общее предложение состоит в том, чтобы использовать их только для базовых проверок внутри одной таблицы.

ПОЗЖЕ РЕДАКТИРОВАТЬ

Попробуйте это тело триггера

...
BEGIN
 if exists
 (
  SELECT 1
  FROM inserted i
    join Animals.Females f
      on i.DataID = f.DataID
    join Animals.Master m
      on f.DataID = m.DataID
  WHERE
    m.BirthDate > i.Date

 )
 RAISERROR("Trigger iCheckBreedingDateAfterBirthDate - Breedings.Date is wrong", 18, 0)
END
GO
person Alsin    schedule 27.01.2015
comment
Спасибо за совет. Я пытался добавить триггер, но не могу написать его правильно. Не могли бы вы взглянуть на мой код в моем отредактированном посте? - person Milliron X; 06.02.2015
comment
Я отредактировал свой ответ и добавил пример триггера. Я не могу проверить это на реальной БД, поэтому возможны ошибки. - person Alsin; 06.02.2015
comment
Спасибо, это сработало отлично. Если вы не возражаете, я просто хотел бы убедиться, что знаю, что вы сделали, чтобы я мог повторить это. SELECT 1 FROM inserted выбрал первую (и, вероятно, единственную) строку, которая была вставлена, если и только если объединенная дата рождения более поздняя, ​​чем дата размножения. Если такая строка exists, выдается ошибка. Имею ли я это право? - person Milliron X; 08.02.2015
comment
EXISTS работает так. Если подзапрос возвращает любое количество строк, одну или несколько, он возвращает TRUE. Если подзапрос не возвращает строк, EXISTS имеет значение false. Неважно, какие столбцы возвращаются в SELECT, SQL Server игнорирует вывод. Таким образом, если среди всех вставленных строк есть одна, нарушающая правило, весь оператор будет откатываться, - person Alsin; 09.02.2015

arrow_upward
6
arrow_downward

Я бы не стал использовать триггер. Используйте проверочное ограничение. Гораздо чище. Помните, что контрольное ограничение может вызывать функцию. Напишите функцию, которая выполняет проверку в другой таблице. Затем вызовите функцию из контрольного ограничения. Это лучший способ добиться ограничений проверки нескольких таблиц.

person Phillip    schedule 10.07.2015
comment
Будь осторожен; кажется, что CHECK CONSTRAINT с UDF ненадежны: обход ограничений сервера sql">dba.stackexchange.com/questions/12779/ - person DharmaTurtle; 13.02.2020