Access SQL to create one-to-many relation without Enforce Referential Integrity

Question

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]

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);

Answer 1

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.

(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.

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

the required code in VBA would be

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