[AccessD] Referential Integrity -- Fact or Fiction?

Ah Hah. Now I see what you mean, and looking at the on-line help, here is
the reason for what you have observed...

"You can't enter a value in the foreign key field of the related table that
doesn't exist in the primary key of the primary table. However, you can
enter a Null value in the foreign key, specifying that the records are
unrelated. For example, you can't have an order that is assigned to a
customer that doesn't exist, but you can have an order that is assigned to
no one by entering a Null value in the CustomerID field."

So nulls are "ok".

This whole thing would be moot however if you always do the data entry in a
form bound to the Fee Book table with a sub-form bound to the Fee Book
Ledger table. With the appropriate Parent / Child links Access will
automatically plug in the Foreign Key value for you and you'll never get any
orphans that way.

Lambert

> -----Original Message-----
> From:	Greg Smith [SMTP:GregSmith at starband.net]
> Sent:	Friday, May 14, 2004 12:58 PM
> To:	accessd at databaseadvisors.com
> Subject:	RE: [AccessD] Referential Integrity -- Fact or Fiction?
> 
> Lambert:
> 
> Well, I guess that wasn't very clear.  In the Fee Book Ledger table
> (secondary), the [Fee Book #] field, by table definition, is not required,
> even though it is the linked field to the Fee Book table (primary) by the
> [Fee Book #] field.  However, referential integrity between the Fee Book
> table and the Fee Book Ledger table using this [Fee Book #] field "should"
> always force an entry in this field in the Fee Book Ledger table, or at
> least cause an Access error to appear.
> 
> But I can enter data into the Fee Book Ledger table, and leave the [Fee
> Book #] field blank and Access accepts it into the table.
> 
> My understanding (correct or incorrect) is that if Referential Integrity
> is enforced, then data in the secondary table cannot be entered if there
> is no related record in the primary table.  Which is NOT what is happening
> here.
> 
> There has to be a reason this is happening...I hope.
> 
> Let me know if this needs further explanation ...
> 
> Greg
> 
> 
> 
> > Greg,
> >
> > You say " No fee book number (in the Fee Book Ledger table...it's
> > blank...but the field is NOT required...yet) but some of the other
> > fields are filled.  Just a blank [Fee Book #] field. "
> >
> > This looks like a direct contradiction to me??? No fee book number filed
> > in blank ,but you have some blank fee book number fields?
> >
> > Lambert
> >
> >> -----Original Message-----
> >> From:	Greg Smith [SMTP:GregSmith at starband.net]
> >> Sent:	Friday, May 14, 2004 12:05 PM
> >> To:	accessd at databaseadvisors.com
> >> Subject:	[AccessD] Referential Integrity -- Fact or Fiction?
> >>
> >> Hi everyone!
> >>
> >> I've got a puzzler here that makes no sense to me.  Access 97. SP
> >> "I've Lost Count"...
> >>
> >> This is a large database with many related tables.  There is one main
> >> table (Fee Book) with the Primary Key is [Fee Book #].  It is manually
> >> entered, but is unique by definition.  In one of the related tables
> >> (Fee Book Ledger), this field is used for the related records in both
> >> tables.  The Fee Book table is the main table while the Fee Book
> >> Ledger table holds related records.  Referential Intregity is
> 
> etc.
> 
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com