[AccessD] Referential Integrity -- Fact or Fiction?

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 Enforced.  Cascade Update
> Related Records is applied, but Cascade Delete Related Records is NOT. 
> The Join Type is #2, "Include ALL records from 'Fee Book' and only those
> records from 'Fee Book Ledger' where the joined fields are equal.
> 
> Obviously, in the Fee Book table, where [Fee Book #], the primary key, is
> required, it cannot be blank.
> 
> Why am I ending up with orphans in the Fee Book Ledger table?  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.  And it has NO related record in the primary table
> since that table cannot have blank fields.
> 
> And, to test it further, I went into the Fee Book Ledger table, entered a
> record, but purposly left the [Fee Book #] field blank, and it ACCEPTED
> it.
> 
> Now I want to go home.
> 
> I can make the [Fee Book #] field in the Fee Book Ledger table required,
> since it currently is not, but that may have some unintended consequences
> down the road that I'm not aware of at this moment.
> 
> I'm going to test that in a few moments, but I wanted to see if anyone in
> this group had seen this type of oddball behavior before.
> 
> TIA
> Greg Smith
> GregSmith at Starband.net
> 
> 
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com