CODEWITHSUNDEEP
sqlms-accessconstraints
profimedica
profimedica

Reputation: 2830

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

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]

I need to restore this original relation

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

I dont need Enforce Referential Integrity

Upvotes: 12

Views: 2063

Answers (2)

Bohemian
Bohemian

Reputation: 425033

Firstly, your "Cihld" (did someone really misspell Child that badly and leave it in the schema???) table is actually the parent table, and the Main table is the child table, according to the relationship as defined: The child table has the foreign key column constrained to have primary key values from the parent table. This mixup, plus the misspelling strongly suggest a total mess.

Nevertheless, it is permissible for foreign key column to be defined as nullable (ie do not define them with the NOT NULL modifier). Do this, and just set the foreign key column to NULL whichever rows you want to not constrain back to the parent table.

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123549

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

EditRelationships.png

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

Upvotes: 9

Related Questions