Subject: RE: [accessd] Referential Integrity
I am confused by your statement that "For the others, I cannot force RI." You can easily force RI on any non-null value of an FK that permits nulls. I just ran a test to verify this.
Using the following table definitions in Access97:
CREATE TABLE ReferenceData
([ReferenceKey] integer,
[ReferenceData] text,
CONSTRAINT [Index1] PRIMARY KEY ([ReferenceKey]));
CREATE TABLE ReferencingData
([ID] integer,
[ReferenceKey] integer,
[ReferencingData] text,
CONSTRAINT [FK1] FOREIGN KEY (ReferenceKey) REFERENCES ReferenceData (ReferenceKey),
CONSTRAINT [Index1] PRIMARY KEY ([ID]));
Load data as follows:
ReferenceData Table
ReferenceKey ReferenceData
1 Reference A
2 Reference B
3 Reference C
ReferencingData
ID ReferenceKey ReferencingData
1
Reference Null
2 1
Reference A
3 2
Reference B
4 3
Reference C
6 Null Reference 2
Note that the ReferenceKey for ID 1 and 6 are Null.
Referential Integrety is in force for rows in ReferencingData where a value is entered in ReferenceKey. Rows without a value in ReferenceKey can be added at will. To verify this, try adding a row with ReferenceKey equal to 4. Then try adding a row with ReferenceKey left null.
You can also create the table and relationships using the normal Access GUI. I did it with Data-Definition SQL to make it easire to put in the message.
The whole thing is posted on my web site under Useful Software.
Steve Goodhall
mailto:SGoodhall@home.com
http:/members.home.net/sgoodhall/
Sooner or later, all this planning has to degenerate into work. - Peter Drucker
-----Original Message-----
From: accessd-owner@mtgroup.com [mailto:accessd-owner@mtgroup.com]On
Behalf Of Arthur Fuller
Sent: Saturday, December 09, 2000 10:36 AM
To: accessd@mtgroup.com
Subject: RE: [accessd] Referential Integrity
IMO, the use of foreign keys comes down to a single question: are null FKs
allowed? I have an app in which some FKs are allowed to be null, while
others are not. For all those not allowed to be null, I force RI. For the
others, I cannot force RI. This has led to a couple of problem-situations,
which I have got around by including a "None" in the foreign table, the
first entry in the table. This technique allows forced RI while preserving
the illusion that nothing has been selected yet.
For example, a new sale (detail) must include a hotel booking. It is
possible that at time of sale, we don't have a confirmation from the hotel.
So we choose "None" at data-entry time, then complete the order, then one of
the travel people gets on the phone, calls a few hotels and finally changes
the FK to whatever when confirmation is obtained.
In other cases, say selling parts from a precisely defined parts inventory,
it makes no sense to have either no RI or the "None" workaround.
In summary, I say use forced RI wherever you can. This puts a lot of your
work onto the database, where it belongs.
Arthur
-----Original Message-----
From: accessd-owner@mtgroup.com [mailto:accessd-owner@mtgroup.com]On
Behalf Of Nixon Gill
Sent: December 9, 2000 5:05 PM
To: accessd@mtgroup.com
Subject: [accessd] Referential Integrity
Q. Is there any major problem with not forcing referential integrity?
Particularly if the database works beautifully without it being forced?
I know that last bit seems to answer my question but does it happen in
industry often?
I seam to recall my Access teacher saying it was ok once but i can't be
certain!
Cheers
Nixon Gill