在数据库内强制执行表间约束的最佳方法

Question

我正在寻找检查表间约束的最佳方法，比外键向前迈出了一步。 例如，检查日期子记录值是否位于两个父行列上的范围日期之间。 例如：

Parent table  
ID    DATE_MIN   DATE_MAX
----- ---------- ----------
1     01/01/2009 01/03/2009
...

Child table
PARENT_ID  DATE
---------- ----------
1          01/02/2009
1          01/12/2009   <--- HAVE TO FAIL!
...

我看到两种方法：

• 在提交时创建物化视图，如本文< /a> （或其他 RDBMS 上的其他等效项）。
• 使用存储过程和触发器。

还有其他方法吗？ 哪个是最好的选择？

更新：这个问题的动机不是关于“对数据库或应用程序施加约束”。 我认为这是一个令人厌倦的问题，每个人都按照自己喜欢的方式去做。 而且，我对批评者感到抱歉，我正在开发时受到数据库的限制。 从这里，问题是“管理数据库表间约束的最佳选择是什么？”。 我在问题标题上添加了“数据库内部”。

更新2：有人添加了“oracle”标签。 当然，物化视图是 Oracle 工具，但我对任何选项感兴趣，无论它是在 Oracle 还是其他 RDBMS 上。

Answer 1

编辑：Dana the Sane 删除了他的帖子，该帖子本来是不顾 DBA 的反对将其放入数据层的。

---

DBA 对像 Dana 这样的开发人员大喊大叫的原因是他们认为应用程序和数据库之间的比例是 1:1。 他们看到这一点是因为他们看到数据是为了支持应用程序而他们的应用程序只需要将数据存储在一个地方。

DBA 将数据视为最重要的事情，并不关心应用程序的出现或消失。

如果您不再使用 MS Word，您会关心是否仍然可以访问您的文档吗？ 不，数据对您来说很重要，应用程序则不重要。

如果您让任何东西绕过您的应用程序来获取您的数据，那么您就失去了数据层中的约束。 如果您的约束在数据库层，那么几十个应用程序都可以使用它。

理想情况下，您永远不会向任何人授予 INSERT、UPDATE 或 DELETE 权限。 相反，您将授予 EXECUTE On 将为您执行 CRUD 的包。 如果您从一开始就这样做，那么向 CHILD 的 INSERT 添加规则的能力（例如检查出生日期是否在父母日期之间）实际上是无限的。

Answer 2

数据库约束

实施数据库约束的最佳方式（跨越两个或多个关系的约束 - 其中引用完整性约束是具有句法简写的特殊情况，<代码>外键/引用语句）将是声明式，通过标准SQL语句：

create assertion <name> check (<condition>)

在您的情况下，某些东西就像：

create assertion Child_DATE_between_MIN_MAX check (
  not exists (
    select DATE_MIN, DATE, DATE_MAX
      from Parent, Child
     where ID = PARENT_ID
       and DATE < DATE_MIN
       and DATE > DATE_MAX
  )
)

更新：我忘记严格来说是一个布尔值，因此旧代码不正确。

不幸的是（这里有适度的讽刺）大多数 SQL-DBMS 不实现 ASSERTION。

因此，需要使用存储过程和触发器或检查约束（如果可用）按程序实现此检查。 在这种情况下，需要调用相同的存储过程来更新父级和子级关系； 所以一个过程和两个触发器或检查约束。

Lurker Indeed 的回答展示了这样的解决方案，但它需要对子关系进行类似的检查。

对性能的担忧

Damien_The_Unknowner，在评论 同样的答案，认为：

1) 您可能会遭受全额损失
每次插入/更新都会进行表扫描

在这里，我将解决这个反对意见，因为它很常见，甚至对于 ASSERTION 来说似乎也有效（这很可能是一个流行的误解，说服用户不要向 SQL-DBMS 实现者询问它们，即使当他们知道它在标准中时）。

嗯，是的，他是对的……如果一个人使用 DBMS，那就太糟糕了！

有一个有趣的理论可以应用于完整性维护： 微分关系微积分（以 .pdf 形式提供 在这里；您还可以在每本有关 DB 理论的不错的书中找到对该主题的充分处理。

核心思想是可以强制执行完整性约束，通常只检查更新涉及的关系子集。 更严格地说，引用链接论文的摘要：

...一阶的形式微分
句子对于维持
数据库完整性，因为一旦
数据库约束表示为
一阶句子及其导数
关于交易规定
充分必要条件
为了保持完整性。 的
导数通常更简单
测试比原始约束
因为它保持完整性
通过假设完整性来区分
交易前和测试
仅针对新的违规行为。 ...

还有其他技术可以处理增量完整性约束维护。 DBMS 开发人员没有充分的理由忽视这一理论。 事实上，业余爱好者对 SQL 中完整性约束和完整性检查的介绍 ( .pdf）在简介中写道：

1 简介

...商业关系型 DBMS 产品
然而，支持 SQL（例如，
Oracle [Ora99] 或 DB2 [IBM99]）不
支持更高级的形式
的限制。 即使在今天，也有超过 8
SQL'92 标准发布多年后
已发行，这些都不是商业性的
系统支持断言，最
SQL 中约束的一般形式！
科学文献，即研究
论文，致力于诚信
另一方面提供丰富的
有希望的结果适用于非常
一般和强大的形式
完整性约束。 ...

所以，请：要求您的 SQL-DBMS 供应商（商业或免费/开源）立即实施 ASSERTION，并且至少具有合理的性能。

Answer 3

我会选择存储过程和触发器路线； 它们的主要目的之一是确保数据库级别的数据完整性。

大多数数据库还具有某种形式的检查约束，其中几乎任何可以放入 WHERE 子句的内容都可以用作对数据的检查：

CREATE FUNCTION CheckFnctn()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = COUNT(*) 
   FROM PARENT
   INNER JOIN CHILD ON PARENT.ID = CHILD.PARENT_ID
   WHERE CHILD.DATE < PARENT.DATE_MIN OR CHILD.DATE > PARENT.DATE_MAX
   RETURN @retval
END;
GO
ALTER TABLE CHILD
ADD CONSTRAINT chkDates CHECK (dbo.CheckFnctn() = 0 );
GO

Answer 4

好吧，在具体的例子中，我会冗余地存储冗余数据。 通过 CHECK 和 FK（以及超级键）的组合，我们确保数据始终正确，然后我们围绕它包装一个视图和触发器以隐藏实现细节：

create table dbo.Parents (
    ParentID int IDENTITY(1,1) not null,
    ValidFrom datetime not null,
    ValidTo datetime not null,
    /* Natural Key column(s) */
    CONSTRAINT PK_dbo_Parents PRIMARY KEY (ParentID),
    CONSTRAINT UQ_dbo_Parents_DRI UNIQUE (ParentID, ValidFrom, ValidTo),
    /* Unique constraint on Natural Key */
    CONSTRAINT CK_dbo_Parents_ValidDates CHECK (ValidFrom <= ValidTo) /* Semi-open interval */
)
go
alter table dbo.Parents add constraint DF_dbo_Parents_ValidFrom DEFAULT (CURRENT_TIMESTAMP) for ValidFrom
go
alter table dbo.Parents add constraint DF_dbo_Parents_ValidTo DEFAULT (CONVERT(datetime,'99991231')) for ValidTo
go
create table dbo._Children (
    ChildID int IDENTITY(1,1) not null, /* We'll need this in the update trigger */
    ParentID int not null,
    ChildDate datetime not null,
    _ParentValidFrom datetime not null,
    _ParentValidTo datetime not null,
    CONSTRAINT PK_dbo__Children PRIMARY KEY (ChildID),
    CONSTRAINT FK_dbo__Children_Parents FOREIGN KEY (ParentID,_ParentValidFrom,_ParentValidTo) REFERENCES dbo.Parents (ParentID,ValidFrom,ValidTo) ON UPDATE CASCADE,
    CONSTRAINT CK_dbo__Children_ValidDate CHECK (_ParentValidFrom <= ChildDate and ChildDate < _ParentValidTo) /* See, semi-open */
)
go
alter table dbo._Children add constraint DF_dbo__Children_ChildDate DEFAULT (CURRENT_TIMESTAMP) for ChildDate
go
create view dbo.Children (ChildID,ParentID,ChildDate)
with schemabinding
as
select ChildID,ParentID,ChildDate from dbo._Children
go
create trigger dbo.T_Children_I on dbo.Children instead of insert
as
begin
    set nocount on

    insert into dbo._Children (ParentID,ChildDate,_ParentValidFrom,_ParentValidTo)
    select i.ParentID,i.ChildDate,p.ValidFrom,p.ValidTo
    from
        inserted i
            inner join
        dbo.Parents p
            on
                i.ParentID = p.ParentID
end
go
create trigger dbo.T_Children_U on dbo.Children instead of update
as
begin
    set nocount on
    if UPDATE(ChildID)
    begin
        RAISERROR('Updates to ChildID are not allowed',16,1)
        return
    end

    update c
    set
        ParentID = i.ParentID,
        ChildDate = i.ChildDate,
        _ParentValidFrom = p.ValidFrom,
        _ParentValidTo = p.ValidTo
    from
        inserted i
            inner join
        dbo._Children c
            on
                i.ChildID = c.ChildID
            inner join
        dbo.Parents p
            on
                i.ParentID = p.ParentID
end
go
insert into dbo.Parents(ValidFrom,ValidTo)
select '20081201','20090101' union all
select '20090201','20090301'
/* (2 row(s) affected) */
go
insert into dbo.Children (ParentID,ChildDate)
select 1,'20081215'
/* (1 row(s) affected) */
go
insert into dbo.Children (ParentID,ChildDate)
select 1,'20090115'
/*
Msg 547, Level 16, State 0, Procedure T_Children_I, Line 6
The INSERT statement conflicted with the CHECK constraint "CK_dbo__Children_ValidDate". The conflict occurred in database "Play", table "dbo._Children".
The statement has been terminated.
*/
go
update dbo.Parents set ValidTo = '20090201' where ParentID = 1
/* (1 row(s) affected) */
go
insert into dbo.Children (ParentID,ChildDate)
select 1,'20090115'
/* (1 row(s) affected) */
go
update dbo.Parents set ValidTo = '20090101' where ParentID = 1
/*
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_dbo__Children_ValidDate". The conflict occurred in database "Play", table "dbo._Children".
The statement has been terminated.
*/
go
insert into dbo.Children (ParentID,ChildDate)
select 2,'20090215'
/* (1 row(s) affected) */
go
update dbo.Children set ChildDate = '20090115' where ParentID=2 and ChildDate = '20090215'
/*
Msg 547, Level 16, State 0, Procedure T_Children_U, Line 11
The UPDATE statement conflicted with the CHECK constraint "CK_dbo__Children_ValidDate". The conflict occurred in database "Play", table "dbo._Children".
The statement has been terminated.
*/
go
delete from dbo.Children
/* (3 row(s) affected) */
go
/* Clean up after testing */
drop view dbo.Children
drop table dbo._Children
drop table dbo.Parents
go

这是针对 SQL Server 的。 在 2005 年进行了测试，但至少也应该适用于 2000 年和 2008 年。 这里的一个好处是，即使触发器被禁用（例如嵌套触发器被关闭），您也不会在基表中得到错误数据