访问SQL以创建一对多关系，而不强制引用完整性 [英] Access SQL to create one-to-many relation without Enforce Referential Integrity

问题描述

我有这种关系。我必须暂时销毁它，只是使用SQL命令更改salID字段的大小：

I have this relation. And I have to temporarily destroy it just to change the size of "salID" field using SQL command:

ALTER TABLE Adressen DROP CONSTRAINT [ChildTableMainTable]

如何使用SQL命令重新创建相同的关系类型？
如果我使用下一个SQL，我得到一对多的关系。这不是我需要的：

How can I recreate the same relation type using SQL commands? If I use the next SQL I get a one to many relation. This is not what I need:

ALTER TABLE MainTable ADD CONSTRAINT [ChildTableMainTable] FOREIGN KEY (salID) REFERENCES [ChildTable] (ChildPK);

推荐答案

据我所知，Access DDL根本不支持创建没有强制参照完整性的访问关系。 CREATE CONSTRAINT 将与强制引用完整性创建一个关系，因为这正是这样一个关系：a引用完整性。

To the best of my knowledge, Access DDL simply does not support the creation of an Access "Relationship" without "Enforce Referential Integrity". CREATE CONSTRAINT will create a Relationship with "Enforce Referential Integrity" because that's exactly what such a Relationship is: a Referential Integrity constraint.

（ ON UPDATE 和 ON DELETE CREATE CONSTRAINT 在编辑关系对话框中控制级联更新相关字段和级联删除相关记录复选框的值，但不会控制强制引用完整性复选框本身。）

(The ON UPDATE and ON DELETE clauses of CREATE CONSTRAINT control the values of the "Cascade Update Related Fields" and "Cascade Delete Related Records" checkboxes in the Edit Relationships dialog, but they do not control the value of the "Enforce Referential Integrity" checkbox itself.)

换句话说，没有强制引用完整性的关系根本不是约束。它只是一个提示，表通过指定的字段相关联，例如，以便查询生成器可以自动连接表，如果他们被添加到查询设计。

In other words, a Relationship without "Enforce Referential Integrity" is not a constraint at all. It is merely a "hint" that the tables are related via the specified fields, e.g., so that the Query Builder can automatically join the tables if they are added to the query design.

要创建没有强制引用完整性的关系，您需要使用Access DAO。对于像这样的关系

To create a Relationship without "Enforce Referential Integrity" you need to use Access DAO. For a Relationship like this

所需的代码VBA将是

Option Compare Database
Option Explicit

Public Sub CreateRelationship(relationshipName As String, _
        parentTableName As String, childTableName As String, _
        parentTablePkName As String, childTableFkName As String)
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Dim rel As DAO.Relation
    Set rel = cdb.CreateRelation(relationshipName, parentTableName, _
            childTableName, dbRelationDontEnforce)
    rel.Fields.Append rel.CreateField(parentTablePkName)  ' parent PK
    rel.Fields(parentTablePkName).ForeignName = childTableFkName  ' child FK
    cdb.Relations.Append rel
    Set rel = Nothing
    Set cdb = Nothing
End Sub

这篇关于访问SQL以创建一对多关系，而不强制引用完整性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！