主页 / dba / 问题 / 205152
Accepted
l0veisreal
Asked: 2018-04-28 02:10:55 +0800 CST

为什么无法强制执行预期的完整性约束？

  • 772

以下完整性约束试图强制执行什么？（完整性约束是主键并在这种情况下进行检查，但我不明白我应该真正写什么）。

解释为什么无法强制执行预期的完整性约束？

CREATE TABLE Sailors
( sid INTEGER,
  sname CHAR(10),
  rating INTEGER,
  age REAL,
  PRIMARY KEY (sid),
  CHECK
    ( (SELECT COUNT (S.sid) FROM Sailors S)
    + (SELECT COUNT (B.bid) FROM Boats B) < 100 )
) ;

我们在学校学习SQL。

Sabancı bilgisayara selamlar @yguney

database-agnostic primary-key

3 个回答

  1. Best Answer

    （注意：你在这个表上声明了2个约束，一个PRIMARY KEY约束和一个CHECK约束。答案是关于第二个。）

    该CHECK约束试图强制执行：

    两个表中的行数应小于 100

    所以，它应该 - 如果它有效 - 允许一个表有 50 行和其他 49 行，或 1 和 98 或 5 和 10 或......但没有一个应该有 100 或更多。如果一个有 90 个，另一个应该不超过 9 个，任何试图插入更多行的事务都应该被拒绝（引发约束错误）。

    至少这就是它看起来正在尝试做的事情。现在的问题或者更确切地说是约束的两个问题：

    • 在CHECK几乎所有实现中，约束是行约束。因此，它会检查插入或更新的每一行。这是一个实现问题，当前的 DBMS 还没有实现涉及子查询的约束（Firebird 除外，它声称这样做，但我还没有测试过）。

      有一些 DBMS 允许在CHECK约束中使用函数（并且函数可以包含子查询），因此可以“绕过”限制，但这会导致各种并发问题并且问题并没有真正解决。建议，即使在那些 DBMS 中，也不要在CHECK约束中使用子查询。

      在我看来，涉及表的许多行（在本例中为所有行）的约束应该是ASSERTION，而不是CHECK约束。

    • 其次，更严重的是，CHECK约束是对一张表的约束。它不是ASSERTION（那些是可以跨越多个表的约束）。

      因此，即使有一个允许CHECK子查询约束的实现，结果也将是人们所期望的。Sailors理解原因的一种方法是，只有在插入或更新行时才会检查约束。这将导致这些有问题的情况：

      Boats我们在表中插入 200 行。不会检查约束，因此允许这样做。然后我们尝试在 中插入一行Sailors。检查约束并禁止该行。但是我们是否设法实现了我们的意图？不是，因为两张表加起来是200行，也就是100多条。

      我们在 中插入 5 行Sailors。到目前为止没问题。然后表中有 200 行Boats。与以前一样，约束将再次不被检查，因此将被允许。然后我们尝试更新 中的一行Sailors。约束被检查，我们得到一个错误。UPDATE失败了。但是我们总共还有 205 行，那么为什么更新会失败呢？

    作为结论，这个约束有两个问题。首先，当前的实现不允许在CHECK约束中使用子查询，其次它应该是一个约束ASSERTION而不是一个CHECK约束，因为它涉及多个表。

    请注意，ASSERTION到目前为止还没有实现任何 DBMS（至少那些基于 SQL 的，有一些基于教程 D 的）。

    • 6

  2. 不确定我是否理解您的问题，但无论如何我都会尝试一下。CHECK包含的约束SELECT根据标准是有效的（参见 F671，例如http://jtc1sc32.org/doc/N1201-1250/32N1208-TC9075-P02-foundation.pdf），但很少有 DBMS:s 支持这一点。Firebird 声称具有此功能

    http://www.firebirdsql.org/en/sql-conformance/。

    SQL-server 不直接支持，但是可以把select 隐藏在函数中，在constraint 中使用函数。

    除此之外，我不知道。

    正如其他人在此线程中指出的那样，从实现的角度来看，该构造存在问题，但并非没有优点。RDBM:s 中的“子类型化”通常实现为：

    CREATE TABLE Parent
( key ... not null primary key
, type ... not null
,    constraint ak1 unique (key, type)
,    check (type in ...));

CREATE TABLE child1
( key not null primary key
, type ... not null
,    constraint fk1 foreign key (key, type) references parent (key, type)
,    constraint c1 check (type = ...));

    ak1 ^ fk1 ^ c1是类型控制，保证parent.type与插入child1的任何行一致。然而，可以争辩说必须在 child1 中声明类型是不必要的和多余的，就像必须声明冗余唯一约束 ak1 一样。允许在约束中进行子选择可以提供更优雅的解决方案：

    CREATE TABLE Parent
( key ... not null primary key
, type ... not null
,    check (type in ...));

CREATE TABLE child1
( key not null primary key
,    constraint fk1 foreign key (key) references parent (key, type)
,    constraint c1 check 
         ((select type from parent p where p.key = key...) = ...);

    无需键入 child1 和父级中的冗余 ak1 密钥。从理论关系的角度来看，ak1 是可还原的（它是主键的超键），因此作为替代键是有问题的。

    • 3

  3. Sailors此约束检查表中的记录总数Boats小于 100。但它仅针对其中一个表进行检查（CHECK CONSTRAINT是表约束。它具有表范围，因此对另一个表数据执行的任何操作都不会触发该检查。），那里无法阻止向 中添加记录Boats，导致该约束失败。

    如果是这样，您可以对Sailors表执行的唯一操作是SELECTorDELETE和CREATE/UPDATE操作将失败并出现CHECK CONSTRAINT错误。

    要执行CREATE/UPDATE操作，Sailors您将从一个或两个表中删除一些记录。为了防止它，您也向Boats表添加了相同的约束。

    • 2

相关问题