MS Access 中的唯一性约束

Question

在我必须为 MS Access 设计的数据库中，我有一个名为“Measurements”的表，其中存储某些测量参数（测量值、标准偏差等） - 每行都有一个整数 ID 作为其主键。

然后其他表使用外键关系链接到该测量表。 某些表包含两个不同的“measurementID”字段，它们都链接到这一测量表。 但是，每个测量值只能与这些字段中的一个相链接。

如何对多个表中的多个字段强制执行唯一性约束？ 有办法吗？

Answer 1

此类约束确实可以在 ACE/JET 中使用 CHECK 约束来实现。

那些说他们通常会使用触发器来处理此类事情的人并没有意识到 ACE/Jet 和 SQL Server 中的 CHECK 约束之间的区别：在 SQL Server 中，它们不能包含子查询，这意味着它们不能引用同一表中其他行或其他表中的值，而在 ACE/Jet 中可以。

在理想的（但据我所知还不存在）SQL-92 产品中，所描述的唯一性将在模式级别使用 ASSETION 来实现。 由于 CHECK 约束是表级的，并且仅在对其定义的表进行 UPDATE 或 INSERT 操作时才进行检查，因此您需要放置适当的 CHECK 对所有引用表的约束（同样适用于 SQL Server 触发器）。 这是一个简单的例子：

CREATE TABLE Parent 
(
   parent_ID INTEGER NOT NULL IDENTITY UNIQUE, 
   data_col INTEGER NOT NULL
)
;
CREATE TABLE Child1
(
   parent_ID INTEGER NOT NULL
      REFERENCES parent (parent_ID), 
   data_col INTEGER NOT NULL
)
;
CREATE TABLE Child2
(
   parent_ID INTEGER NOT NULL
      REFERENCES parent (parent_ID), 
   data_col INTEGER NOT NULL
)
;
ALTER TABLE Child1 ADD
   CONSTRAINT child1__no_dups_in_child2
   CHECK (NOT EXISTS (
                      SELECT * 
                        FROM Child1 AS C1
                             INNER JOIN Child2 AS C2
                                ON C1.parent_ID = C2.parent_ID
                     ))
;
ALTER TABLE Child2 ADD
   CONSTRAINT child2__no_dups_in_child1
   CHECK (NOT EXISTS (
                      SELECT * 
                        FROM Child1 AS C1
                             INNER JOIN Child2 AS C2
                                ON C1.parent_ID = C2.parent_ID
                     ))
;

但是，我想知道您是否有子类（即可以键入 ID 表示的每个实体），在这种情况下您应该能够使用 FOREIGN KEY 和 row-级别 CHECK 约束（或者验证规则，如果您更熟悉 MS Access 接口而不是 CHECK 约束所需的 SQL DLL）。 该逻辑比表级 CHECK 约束更容易实现，只需注意 CASCADE 引用操作中的循环即可。 这是另一个简单的例子：

CREATE TABLE Parent 
(
   parent_ID INTEGER NOT NULL IDENTITY, 
   child_type VARCHAR(4) NOT NULL, 
   CONSTRAINT child_type__values 
      CHECK (child_type IN ('Boy', 'Girl')), 
   UNIQUE (child_type, parent_ID)
)
;
CREATE TABLE Girls
(
   parent_ID INTEGER NOT NULL, 
   child_type VARCHAR(4) DEFAULT 'girl' NOT NULL, 
   CONSTRAINT girl_child_type__must_be_girl
      CHECK (child_type = 'girl'),
   FOREIGN KEY (child_type, parent_ID)
      REFERENCES Parent (child_type, parent_ID), 
   data_col INTEGER NOT NULL
)
;
CREATE TABLE Boys
(
   parent_ID INTEGER NOT NULL, 
   child_type VARCHAR(4) DEFAULT 'boy' NOT NULL, 
   CONSTRAINT boy_child_type__must_be_boy
      CHECK (child_type = 'boy'),
   FOREIGN KEY (child_type, parent_ID)
      REFERENCES Parent (child_type, parent_ID), 
   data_col INTEGER NOT NULL
)
;

Answer 2

Microsoft ACE/Jet 引擎不支持触发器，而这通常是实现此类功能的方式。

编辑：正如 @onedaywhen 所指出的，JET 4.0 及以上版本确实支持检查约束，但跨两列实现异或类型约束并不简单。

如果您使用的是 Access Forms，您可以实现表单的更新前事件并检查您的约束条件。

Answer 3

关于 Access 的可能性，Mitch 是正确的。 然而，假设您在某处有 BL，这就会逐渐成为合法的业务规则。 这就是我最有可能做的事情。

Answer 4

鉴于您正在设计这个数据库。 您确定您的表结构遵循标准规范化规则吗？

表 A 中的一行能够引用表“测量”中的两行，这听起来是一种不寻常的结构，具体取决于您正在查看的 A.row 字段。
我感觉表 Measurments 中的数据确实需要分成两个或
更多桌子。

Answer 5

我自己对触发器和检查约束持谨慎态度，主要是因为我在没有它们的情况下开发了大部分应用程序（Access/Jet 和 MySQL/MyISAM）。 我同意 BobClegg 的观点，这听起来像是超类型/子类型的情况。 在这种情况下，您将使用一个连接表，该表在外键上具有唯一索引，并有一列指示它的测量类型。 FK 上的唯一索引将阻止添加第二种类型。 这也意味着您的主记录中不会有空字段。 当然，一个空数字字段不是存储问题，但两个互斥字段对我来说总是看起来像一个设计错误。