Problem with Refrential Integrity

[JD]

Hello,

Hoping someone can help me out with the following:

I have a the following table structure:

Table A (Individuals)
Table B (Keys)

Table B has a Primary Key named Keys.
Keys has been added to Table A as a Foreign Key using the name fkKeys.

There is a One-To-Many relationship between Keys and fkKeys. Table B is
currently the One side and Table A is the Many side. Referential Integrity
is enabled with both Update Cascading and Delete Cascading enabled.

I created a form using these two tables. There is a text box for fkKeys so
a user can enter the Key number into the text field in order to update Table
B. Unfortunately, I now found out that Access does not allow entering data
in a Foreign Key in order to update the Primary Key of another table because
it breaks Referential Integrity rules.

Since only the One side can be updated with data, how can I create form that
uses Referential Integrity to update records in both Table A and Table B ?

Regards,

Jason

 

[Jeff Boyce]

If you can/have to change the value of the so-called "Primary Key", some
will argue that it isn't a very good candidate for a Primary Key!<g>

Is there a chance you could use an unchanging value as a primary key (and
foreign key), and use another field to hold this value that seems to need to
change?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[JD]

Thanks for the response Jeff. haha ... Yes ... you are very correct. Don't
know what I was thinking. However, I am somewhat confused in regards to the
following:

Firstly, I'll explain my architecture.

Back-End Access 2007 database that holds all the tables and data.
Front-End Access 2007 databases with Linked-Tables to the Back-End database.

The Front-End databases includes Forms to enter/edit/delete data.

After reading through the documentation I've learned that Referential
Integrity will not work via the Linked-Table method if the tables are not
part of the same Access Database, however Referential Integrity can be
configured on the Back-End database.

My tables are part of the same Back-End database, so should Referential
Integrity work ?

Additionally, will the following work:

I change the field named Keys in Table B to not be a Primary Key and it is
set to Index (No Duplicates).
I have a 1-to-Many relationship to the fkKeys field in Table A.
I enable Referential Integrity on this relationship.
I create a form in the Front-End DB that has a text field associated to the
fkKeys field in Table A.
When I enter data into this text field, the field named Keys in Table B
should be updated with the data.

Look forward to your feedback,

Jason

 

[John W. Vinson]

Since only the One side can be updated with data, how can I create form that
uses Referential Integrity to update records in both Table A and Table B ?

Typically one would use a Form based on the "one" side table, with a Subform
based on the "many"; the subform's Master Link Field would be the one side
table's primary key, and the Child Link Field the related foreign key. You
would be able to add a new record on the mainform, and then add one or more
child records on the subform.

Do note that referential integrity only PREVENTS the addition of invalid
recrods. It will not automagically create any new records for you, if that's
what you're expecting.

 

[JD]

Hi John,

Yes, I was hoping when a user inputs data into the text box, it woukd
update the parent table with the data. But, sounds like a Foreign Key cannot
be updated with data. Therefore I will need to make a different relationship
amongst the tables.

Thanks.

 

[John W. Vinson]

Hi John,

Yes, I was hoping when a user inputs data into the text box, it woukd
update the parent table with the data. But, sounds like a Foreign Key cannot
be updated with data. Therefore I will need to make a different relationship
amongst the tables.

Of COURSE a foreign key can be updated with data!!!!

It would not be of much use if it couldn't be edited.

What you can't do is expect to enter a value in a child record which does not
already exist in the parent table. It's a chicken or egg problem - you're
trying to create an egg, and then say "oh yes, now that I have an egg I need a
chicken to lay it".

Perhaps you could explain the real-life situation that you're trying to model,
and why you feel that you need to create a child record without having a
parent record defined.