How to enforce a CHECK constraint across multiple tables

Question

I have a database that records breeding information for cows in Microsoft SQL Server 2012 Express. Obviously, a cow cannot be bred until after she is born, and she may be bred multiple times during her life; and I need to enforce these constraints in my database. I currently have arranged a schema according to the following diagram:

Cow Database Schema Diagram

DataID is the primary key for all animals. I have attempted to implement Table-Per-Type inheritance, hence the 1-to-1 relationship between [Animals].[Master] and [Animals].[Females]. Since each female may be bred multiple times, I have set up a 1-to-Many relationship between [Animals].[Females] and [Breedings].[Breedings]

My question is: how can I enforce a rule that for all females BirthDate < Breedings.Date?

I essentially need something like the following psudocode (which I have actually put into the CHECK constraint's "expression" box and received a validation error):

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

I have also tried creating a view with the proper join, but found that CHECK constraints cannot be used in views.

So, does anyone know how I can enforce these constraints?

EDIT - I tried the advice of using triggers, but can't seen to formulate the trigger syntax correctly. Here is my code:

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

According to a book I have (SQL Server 2012 Step by Step) this syntax should work perfectly. But instead, SQL Server gives me pink lines under THROW and the last END, stating Incorrect syntax near 'THROW'. Expecting CONVERSATION, DIALOG, DISTRIBUTED, or TRANSACTION. and Incorrect syntax near 'END'. Expecting CONVERSATION. I have inserted these keywords, but they change nothing.

This Q&A will give you a few ideas; http://stackoverflow.com/questions/16314372/ms-sql-server-cross-table-constraint — Rhys Jones, Jan 27 '15 at 14:32
@Tanner Primary-to-Primary is for 1::1 or 1:: 0/1 type relationships. — Rhys Jones, Jan 27 '15 at 14:33
NO. Your trigger is not correct. You have scalar values in your table which means your trigger is unable to handle multiple row operations. See the example that Alsin posted in his response. It is written in a way to deal with multiple rows. — Sean Lange, Feb 06 '15 at 14:08
+20 that's an excellent question, and shows up some of the limitations of relational databases. Many brilliant minds have asked the same question. See the following academic papers (not for an answer, but as to why it;s a good question): http://dl.acm.org/citation.cfm?id=320571 and http://dl.acm.org/citation.cfm?id=1366103 — Reversed Engineer, Nov 24 '15 at 16:43

score 7 · Answer 1 · answered Jul 10 '15 at 08:36

7

I wouldn't use a trigger. Use a check constraint. Much cleaner. Remember a check constraint can call a function. Write a function that does your checking in the other table. Then call the function from the check constraint. This is the best way to achieve multi-table check constraints.

answered Jul 10 '15 at 08:36

Phillip

71
1
2

2

Be careful; it seems like `CHECK CONSTRAINT`s with UDFs are unreliable: https://dba.stackexchange.com/questions/12779/how-are-my-sql-server-constraints-being-bypassed – DharmaTurtle Feb 13 '20 at 03:08

Alsin · Accepted Answer · 2015-02-06T14:09:21.103

4

You can create triggers on Breedings table to check this rule. Trigger is a special stored procedure which executed automatically on INSERT\UPDATE\DELETE on some table. So you can write a trigger that checks all new rows inserted in Breedings and if there is a row where Date is less then appropriate BirthDate, throw error. Same for UPDATE, if Date column is altered, check appropriate animal's BirthDate and throw error accordingly. DELETEs are safe in this matter.

CHECKs are not that good for rules that involve other tables. General suggestion is to use them only for basic checks inside one table.

LATER EDIT

Try this trigger body

...
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

edited Feb 06 '15 at 14:09

answered Jan 27 '15 at 15:24

Alsin

1,514
1
14
18

thanks for the advice. I tried adding a trigger, but can't seem to write it properly. Would you mind taking at look at my code in my edited post? – Milliron X Feb 06 '15 at 04:44
I've edited my answer and added trigger example. I can't check it on real DB so errors are possible. – Alsin Feb 06 '15 at 14:07
Thanks, that worked perfectly. If you don't mind, I'd just like to make sure I know what you did so that I can duplicate it. `SELECT 1 FROM inserted` selected the first (and probably only) row that was inserted, if and only if the joined birthdate is later than the breeding date. If such a row `exists`, an error is thrown. Do I have that right? – Milliron X Feb 08 '15 at 00:46
2

EXISTS works like this. If subquery returns any amount of rows, one or more, it returns TRUE. If subquery returns no rows, EXISTS is false. It doesn't matter what columns returned in SELECT, SQL Server ignores output. So if among all inserted rows exist one that broke rule, entire statement will be rolled back, – Alsin Feb 09 '15 at 13:25

How to enforce a CHECK constraint across multiple tables

2 Answers2

Linked