Jet/Access DDL：如何在不进行数据完整性检查的情况下创建外键约束？
当您使用 Access 创建外键约束（通过数据库架构编辑器）时，您可以选择启用或禁用以下选项（实际标题可能有所不同，我只有本地化版本的 Access 可用）：
x Enforce data integrity
x Cascade updates
x Cascade deletions
在 DDL 中，后两个可以是表示为：
ADD CONSTRAINT Constraint1
FOREIGN KEY (Field1)
REFERENCES Table2 (Table2Field1)
ON UPDATE CASCADE
ON DELETE CASCADE
但是如何模拟没有任何复选框打开的情况，甚至没有“强制完整性”？
简单地删除“ON UPDATE/ON DELETE”子句仍然会产生等效的“强制完整性”约束（因此要求 Field1 和 Table2Field1 有索引）。
When you create foreign key constraints with Access (through database schema editor), you have a choice to enable or disable the following options (actual captions may differ, I only have localized version of Access available):
x Enforce data integrity
x Cascade updates
x Cascade deletions
In DDL, the latter two can be represented with:
ADD CONSTRAINT Constraint1
FOREIGN KEY (Field1)
REFERENCES Table2 (Table2Field1)
ON UPDATE CASCADE
ON DELETE CASCADE
But how do you simulate the case where none of the checkboxes are on, not even the "enforce integrity"?
Simply dropping "ON UPDATE/ON DELETE" clauses still produces the equivalent of "enforce integrity" constraint (and consequently requires that Field1 and Table2Field1 had indexes).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为您将 Access“关系”与 ACE/Jet
FOREIGN KEY混淆了。一般来说，您不能使用 SQL DDL 创建 Access 对象（即使这些对象保留在 ACE/Jet 表中）。另一个例子是验证规则，但您可以使用 SQL DDL 创建 CHECK 约束（实际上不能以任何其他方式创建），这些约束更强大（例如每个表可以多个，可以引用其中的其他行）相同的表或其他表等），但验证规则显示在 adSchemaTableConstraints 架构视图中，其中 CONSTRAINT_TYPE = 'CHECK'。
I think you are confusing an Access 'Relationship' with a ACE/Jet
FOREIGN KEY.In general, you can't use SQL DDL to create Access objects (even if those objects are persisted in ACE/Jet tables). Another example is Validation Rules but you can create
CHECKconstraints using SQL DDL (in fact cannot be created any other way) which are more powerful (e.g. can be more than one per table, can reference other rows within the same table or in other tables, etc), yet Validation Rules show up in the adSchemaTableConstraints schema VIEW with CONSTRAINT_TYPE = 'CHECK'.如果您不想强制执行完整性，那么您确实不需要该约束，因为外键约束的目的是通过公共字段强制两个表之间的关系。据我所知，如果您不想强制执行完整性、级联更新或级联删除，那么您实际上根本不需要约束。把它关掉吧。
我想在某些数据库（例如 Oracle）中，您可以创建约束但将其禁用 - 但这似乎毫无意义，除了文档上的好处。 AFAIK 约束不能在 Access 中禁用，但我可能是错的。
分享并享受。
If you don't want integrity enforced then you really don't want the constraint as the purpose of a foreign key constraint is to enforce a relationship between two tables via a common field. As best I can see it, if you don't want integrity enforced, updates cascaded, or deletes cascaded, you really don't need the constraint at all. Just leave it off.
I suppose that in some databases (e.g. Oracle) you could create the constraint but leave it disabled - but this seems pointless except for the documentary benefit. AFAIK constraints can't be disabled in Access, but I could be wrong.
Share and enjoy.
添加外键约束时使用 NOCHECK 选项。有关详细信息，请参阅此页面：
http://msdn.microsoft.com/en -us/library/ms177463.aspx
Use the NOCHECK option while adding the foreign key constraint. For more information please see this page:
http://msdn.microsoft.com/en-us/library/ms177463.aspx