访问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屋!