如何跨多个表强制执行CHECK约束 [英] How to enforce a CHECK constraint across multiple tables
问题描述
我有一个数据库，该数据库记录Microsoft SQL Server 2012 Express中奶牛的繁殖信息.显然，一头母牛要等到出生后才能进行繁殖，一生中可能会多次繁殖.并且我需要在数据库中强制执行这些约束.我目前已经根据下图安排了一个模式:
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:
DataID是所有动物的主键.我尝试实现Table-Per-Type继承，因此实现了[Animals].[Master]和[Animals].[Females]之间的一对一关系.由于每个雌性可能繁殖多次，因此我在[Animals].[Females]和[Breedings].[Breedings]
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]
我的问题是:我该如何强制所有女性使用的规则BirthDate< Breedings.Date?
My question is: how can I enforce a rule that for all females BirthDate < Breedings.Date?
我基本上需要类似以下的伪代码(我已经将其放入CHECK约束的表达式"框中并收到了验证错误):
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]
我也尝试过使用适当的联接创建视图，但是发现CHECK约束不能在视图中使用.
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
根据我所读的书( SQL Server 2012分步操作)，此语法应该可以正常使用.但是，相反，SQL Server在THROW和最后一个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.
推荐答案
您可以在Breedings表上创建触发器以检查此规则.触发器是一个特殊的存储过程，该过程在某些表的INSERT \ UPDATE \ DELETE上自动执行.因此，您可以编写触发器来检查育种"中插入的所有新行，并且如果存在Date小于适当的BirthDate的行，则会引发错误.与UPDATE相同，如果日期"列被更改，请检查相应动物的BirthDate并相应地引发错误.在这件事上删除是安全的.
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.
CHECK对于涉及其他表的规则不是很好.一般建议仅将它们用于一个表中的基本检查.
CHECKs are not that good for rules that involve other tables. General suggestion is to use them only for basic checks inside one table.
最新编辑
尝试使用此触发器主体
...
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
这篇关于如何跨多个表强制执行CHECK约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
