Table Relationship Advice

[dftjsn]

Hi,

I have a table of literature citations/references. This table is linked to four other tables by the primary/foreign key relationship. The relationship between the citation/reference table and each of the other tables is 1:1. If a user deletes a reference, is there an easy way for Access to simply null the primary key in the other tables if appropriate? I don't want to delete the linked records in the other tables, just null the primary key fields.

Any advice would be appreciated.

Thanks!

dftjsn

 

[KenReay]

Hi

No you cannot have a null primary key.

If relationship between the tables is 1:1 why have seperate tables? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dawnd3]

It doesn't make since that you would want to delete the record but leave the related record. If it is truely related, then you can't have one without the other. (in Access and in real life).So I wonder if the relationships are set correctly. And I also agree with Ken, why have separate tables? There is rarely a need for 1:1 relationships.

Dawn

http://www.calliopeconsulting.net

 

[dftjsn]

Hi Dawn/Ken,

Thanks for your responses. Perhaps my terminology is confusing. There is a primary key in the citations/references table that links to the foreign key in the other tables. The relationship that exists is more of a "look-up" relationship. Each record in the other tables can be supported by, at most, one reference/citation. So ... a record in a procedure table, tblProcedure, may link to one citation for that procedure; a record in a methods table, tblMethods, may link to one citation which describes the methods. That's why the tables are kept separate. I don't want to duplicate the citation fields in the other tables ... I just want to look up the info.

At present, when a citation is deleted, I have code that finds all the tables that the particular citation was referenced in and nulls the foreign key link. The procedure or method tables records that linked to the deleted citation are still good/valid, I just want to null the fields that link to the deleted citation. I thought perhaps there was a more elegant approach than what I was doing.

Thanks!

dftjsn

 

[pelegs]

Use an intermediate table that maps references to citations. So, it would have rows that look like this:

Ref_R1_ID, Cit_C1_ID
Ref_R1_ID, Cit_C2_ID
Ref_R2_ID, Cit_C3_ID
Ref_R2_ID, Cit_C1_ID

and so on.

Now, when you delete the reference Ref_R1_ID, just delete all of the Ref_R1_ID rows. There is a little more work implied here handling this table, and then using it to display or print the references and citations, but simple Joins will accomplish most of what you need.

I had to read your post a few times, and I'm still a bit unclear about what is going on, some I'm guessing at this whole thing. I know it is sometimes hard to describe complex situations. Hope it was a good guess. Peleg
PelegNOSPAM@PStrauss.net

 

[dftjsn]

Peleg,

Thanks for your patience in trying to figure out what I was doing. Basically, it appears I am trying to circumvent referential integrity by allowing the deletion of a record in a primary (citations) table, but not deleting the related record in a foreign (procedures)table. Instead of deleting the related record, I was seeking to just null the foreign key field since the related record in the procedure table is still valid even if its supporting citation has been deleted. I've implemented this behavior in code, but it seems kludgy as I've got several foreign tables.

Thanks for your consideration.

dftjsn

 

[RickSpr]

I think Pelegs is right. I suspect your citations/references table is actually relating several of the other tables. That is, in the natural world it's a relationship and not an entity itself. In such a case, it would be best to make citations/references the child in the relationships--let it house the foreign keys of the other tables, and remove the foreign key to citations/references from the other tables.

If you do that, you won't have to "get around relational integrity". You can just delete the citation/reference, and you're done.

However, there's a down side. If you do this, the relationship implied by your keys will be 1:many from Methods, Procedures, etc. to Citations/References. To get around that, make each foreign key in C/R uniquely indexed.

If your tables are already populated, making this change might seem like it would take a lot of work, but in fact you can let Access do most of the work. First, add the foreign key columns to C/R (make sure they're not Required) and set up the new relationships. Then run an update query for each foreign key, extracting the key from the parent table and using it to update the foreign key in C/R. Finally, delete the relationships in which C/R is the parent table, and remove the foreign keys from the other tables. You can also make the foreign keys in C/R required at this point, if you want. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dftjsn]

Rick,

Thanks for the advice. Reversing the parent/child relationship is a good idea. Unfortunately, I need to maintain compatibility with another application. After further reflection, I decided to not fight Access' referential integrity rules and have instead embraced them. What I had implemented in code before was a way to nullify dependent foreign key values upon deletion (an option Access doesn't have - you can either disallow or cascade deletions), but in retrospect this required more coding than it was probably worth.

Thanks again for your help!

dftjsn