Proper Table Setup and Referential Integrity

[fredka]

I am trying to achieve referential integrity and cascade delete related records between two of my tables tblActivity and tblDetailCompletePaperwork) - I get an error "no unique index found for the referenced field of the primary table" I am starting to wonder if this is because my tables are not structured properly.

I have three main tables:

tblGroups: Primary key is GroupNum field

tblActivity: Primary key is GroupNum, EffectiveDate, HealthCC and SubGroupNum

tblDetailCompletePaperwork: Primary key is GroupNum, EffectiveDate and DateReceived

The relationship I have set between tblactivity and tblDetailCompletePaperwork is with groupnum, effectivedate and datereceived

What happens is that there is only ever one company with one groupnum field in tblGroups. We could sell something and have a number of records entered into tblactivy. For example, if we sold two products there would be two records entered into tblActivity (the subgroupnum and HealthCC would differ) However, we would ony have one entry in tblDetailCompletePaperwork for transaction.

Just another note.... this same group we could sell more products to them (for example, next year) then we would add another entry for in tblActivity for each product sold and a single entry in tblDetailCompletePaperwork for the transaction.

Do I need to somehow modify my relationships?

Thanks!!!!

Fred

 

[easyit]

You cannot (to my knowledge) set multiple keys in a table AND relate them to multiple fields in another table.

Usually you create a single field as PK, (Primary Key), and relate this to a single field (FK = Foreign Key) in another table.
Also I would start with naming the foreign keys in tables differently.

EXAMPLE:
Table tblActivity gets a PK ("GroupNum").
Table tblDetailCompletePaperwork gets a PK ("GroupNum"), and a field called "FK_GroupNum".

Now you can relate these two tables on the fields GroupNum (in table tblActivity) and FK_GroupNum (in table tblDetailCompletePaperwork).



EasyIT

"Do you think that’s air you're breathing?

 

[JoeAtWork]

The fact that all your primary keys are called GroupNum is rather confusing. Do all the "GroupNum"s in tblDetailCompletePaperwork and tblActivity relate back to a GroupNum in tblGroups? If so, you shouldn't be using GroupNum as both the primary key and the foreign key to tblGroups.

A more normal setup is to give each table it's own unique primary key, plus a foreign key to each "parent" table it is related to.

tblActivity
ActivityID - make this the primary key
GroupNum - foreign key to tblGroups

tblDetailCompletePaperwork
DetailID - primary key
ActivityID - foreign key to tblActivity
GroupNum - I would take this out, since you can get
the relation to tblGroups via tblActivity

 

[fredka]

Thanks for the replies!!! - The groupnum field is a main number assigned by our company. there will always be only one groupnum in tblgroups.

tblactivity will have several records for one group. But it should only have one unique record for each "GroupNum, EffectiveDate, HealthCC and SubGroupNum" That is why I was using this as the primary key.

Perhaps I am not looking at this properly but should'nt I use the fields that make up a unique record as my primary key? that is the thinking I used when I set up the primary key in tblactivity to "GroupNum, EffectiveDate, HealthCC and SubGroupNum"

Thanks again!!!!

Fred

 

[lespaul]

But it should only have one unique record for each "GroupNum, EffectiveDate, HealthCC and SubGroupNum" That is why I was using this as the primary key.

You don't have to have this combination as the PK in order to restrict the combination to a unique record. Create an INDEX on those fields and set the INDEX to "No Duplicates"

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[fredka]

Now I understand!!!! thanks. Just one more question...

For the primary keys:

tblGroups is an easy one... that can be the actual groupnum

for tblActivity and tblCompletePaperwork should I use an autonumer as the primary key? If so, what the best way to assign this to data that is already in the table. If not, how would I go about assigning the PK to new records?

Thanks again to all - I really appreciate it!!!

 

[fredka]

Actually, one more question... if I change the PK then my cascading deletes would not work - correct?

Thanks!!

 

[pbrodsky]

IMHO, don't rush too quickly to change things. I believe that, it doesn't hurt efficiency too much, using a meaningful compound primary key has advantages over using an autonumber key. If you search these forums, there have been some very long and informative threads about the best way to select primary keys.

 

[KornGeek]

I personally am against using autonumbers.

Check out faq700-184 for an alternative.

 

[fredka]

Thank you very much for all of the help! The main reason for chaning my setup would be to take advantage of cascading updates and cascading deletions - It sounds like I won't be able to do that.

Thanks again!!!!

Fred

 

[JoeAtWork]

If you use my and EasyIt's suggestions you can set up the cascading deletes. You just need a primary key / foreign key relationship set up.

Some programmers don't like autonumbers, personally I've never had problems with them. You can use the FAQ cited above to set up your own autonumbering system, if you so choose.

 

[fredka]

Got it - thanks for all of the help - I really appreciate it - have a great weekend!!!

Fred

 

[misscrf]

I do use autonumbers in all my pks, and I only have 1 pk per table.

Feel free to check this FAQ out. I refer to it as a normalization bible. The links are all worthwhile too.

It is actually a great read.

http://www.utteraccess.com/forums/s...=373096&page=0&view=collapsed&sb=7&o=&fpart=1

misscrf

It is never too late to become what you could have been ~ George Eliot