If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with Refrential Integrity
Hello,
Hoping someone can help me out with the following: I have a the following table structu 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 |
#2
|
|||
|
|||
Problem with Refrential Integrity
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" wrote in message ... Hello, Hoping someone can help me out with the following: I have a the following table structu 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 |
#3
|
|||
|
|||
Problem with Refrential Integrity
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 "Jeff Boyce" wrote: 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" wrote in message ... Hello, Hoping someone can help me out with the following: I have a the following table structu 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 . |
#4
|
|||
|
|||
Problem with Refrential Integrity
On Mon, 5 Apr 2010 16:12:46 -0700, JD wrote:
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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Problem with Refrential Integrity
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" wrote: On Mon, 5 Apr 2010 16:12:46 -0700, JD wrote: 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. -- John W. Vinson [MVP] . |
#6
|
|||
|
|||
Problem with Refrential Integrity
On Wed, 7 Apr 2010 11:50:02 -0700, JD wrote:
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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|