By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,934 Members | 1,476 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,934 IT Pros & Developers. It's quick & easy.

null value in compound foreign key with referential integrity enforced raises error

P: 1
Microsoft Access 2007
No forms are involved.

I am trying to enforce referential integrity between two tables, but only if there is a non-null value in one of the foreign fields of a compound index. I am not having any success.

Tables 1 and 2 are related such that each record in table 2 is related to some record in table 1. Table 3 is the table with the issue. All records in table 3 must be related to a record in table 1 but need not be related to a record in table 2 unless the opCode field is Not Null. The problem seems to be that unless all of fields in the foreign key for table 3 are Null then they are collectively evaluated against table 2.

Is there any way around this?

Table 1
locNum (Primary Key)
locCode (Primary Key)
locDesc

Table 2
locNum (Primary Key)
locCode (Primary Key)
subLocCode (Primary Key)
subLocDesc

Table 3
recId (Primary Key)
locNum (Required)
locCode (Required)
subLocCode (Not Required, no default value)
recDesc

thanks,

Paul
Apr 29 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.