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

Cowpoke

i think, both relations are 1-to-many relations as indicated in the edit relations box in the bottom... what you are maybe looking for is appending ON UPDATE SET NULL ON DELETE SET NULL at the end? – Steiner

Enforce Referential Integrity was not checked in my original relation. After different approaches I gave up. – Cowpoke

If you want one to many relationships between these tables without referential integrity ,I couldn't think anything other than join on salID and CliIDPK, – Bravado

@Simoyd: out of curiosity, why do you need this? And why must it be in SQL? – Heredity

I'm updating an old product, which requires some modifications to fields that have relationships. The relationships make it easier for us to go into access and review the data in queries. but the ancient and bad DB design won't work with enforced relationships. Ideally I would prefer to just run a script to update said database instead of having a bunch of manual steps or writing a separate application. I understand DAO can be used to automate this, but it's a bunch of extra work that (paragraph of code per change) that should really just be a keyword in SQL (eg NOCHECK). – Abreast

@Simonyd: What if you exported the data, destroy the tables, re-import with the changes made and rebuild the relationship. assuming this is a one time thing. it could be done in a few minutes instead of writing a paragraph of code. or is this a recurring event? – Predella

@Simoyd, In SQL Server there is a WITH NOCHECK and NOCHECK option in the ALTER TABLE statement. MS Access doesn't have it.The syntax of the statement doesn't have these key words. – Hag

@Simoyd: If you modify Gord's function to take table&field names as parameters, it will be only one line of code per relationship. – Heredity

re: "paragraph of code per change" - Like @Heredity says, just put the code in a Sub, or a method, or whatever. See the updated code in my answer. – Sartre

You are using terms main and child table in a confusing way. The foreign key constraint is always added or removed from the foreign key table. This is the table on the many-side. At the other end of the relation there is always a primary key field. The corresponding table is the primary key table and is on the one-side of the relation. I would call the primary key table the main, and foreign key table the child table. You do it the other way round. – Mayce

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

Sartre

Answer 2

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.

Harlan