I have a table ("Personnel") that currently uses a user-created primary key ("SSN"). I would like to switch this to a different unique field ("Employee ID"). The data I have can easily be set up to allow for this to work (no nulls, no duplicates). I'm worried, though, that changing this might cause some problems with relationships. Currently, there are 30-something tables in the database that all use the "SSN" field as a reference field. I would like to get these reference fields switched over to "Employee ID" as well. How will changing the primary key affect these relationships? I'm hoping that it will automatically switch these reference fields as well, but I sort of doubt that will happen. Thanks in advance.
Question
Safely change primary key
This topic has been closed for replies.
+12It would but... have you considered copying your SSN field, then re-populating your SSN field with your new Employee ID - this would be the simplest way to change your data.
I am going through a similar scenario and created a copy of my app to test the change and consequences it will have. What I found is that it did not adapt and change the related field to the new Key field. Instead, it disabled the relationship and the information that was being transferred over was no longer there.
In conclusion, I have to make this change so at this point with the research I have done and the inquiries I have made, it appears that I am going to have to recreate the relationships.
If anyone else can contribute to my findings... It would be appreciated as well!
In conclusion, I have to make this change so at this point with the research I have done and the inquiries I have made, it appears that I am going to have to recreate the relationships.
If anyone else can contribute to my findings... It would be appreciated as well!
+12That would explain why the Key Field would not "auto update". In-Application relationships will auto-fix themselves.
Confirmed: Cross Application relationships require the primary key to be manually updated via VLOOKUP/bulk import.
Also we cannot really copy the app as a backup since the copy will reference the same parent app. Copying the parent app does not re-produce the relationship, must copy the child. So to get a proper copy would need to copy the child app and parent app, then rebuild the relationship from scratch in the child app copy with the parent app copy. Basically what happens in primary key change is the lookups no longer work until the records are updated with the proper key IDs. So not really losing information, just temporarily doesn�t display. A primary key change does not delete the relationship, lookup fields and their settings, etc.
The general MOP is as follows:
1. Identify relationships that need key update - any arrow pointing *away* from the table you are changing the key in will need a manual update via bulk import/VLOOKUP
2. Create lookup reports for each table with the table�s primary key (Record ID#) and the related field. May have to enable the field�s �The field may be used in reports� option to create lookup reports. Turn it on quickly, as soon as save report, turn it off. This just changes the fields availability in new reports, not existing ones.
3. Create bulk import spreadsheet with VLOOKUP translation for each target table.
4. Make sure no users are making changes to system
5. Move primary key � WARNING � This orphans the relationship but the data will re-appear after we re-import the correct primary key
6. Import VLOOKUP bulk imports to target tables
7. Test a few records to confirm the change
8. Update any bulk import templates with the new primary key data. Send any users doing bulk imports the new primary key to old primary key translation table.
9. (if applicable) Delete old primary key after a few weeks of no issues.
Also we cannot really copy the app as a backup since the copy will reference the same parent app. Copying the parent app does not re-produce the relationship, must copy the child. So to get a proper copy would need to copy the child app and parent app, then rebuild the relationship from scratch in the child app copy with the parent app copy. Basically what happens in primary key change is the lookups no longer work until the records are updated with the proper key IDs. So not really losing information, just temporarily doesn�t display. A primary key change does not delete the relationship, lookup fields and their settings, etc.
The general MOP is as follows:
1. Identify relationships that need key update - any arrow pointing *away* from the table you are changing the key in will need a manual update via bulk import/VLOOKUP
2. Create lookup reports for each table with the table�s primary key (Record ID#) and the related field. May have to enable the field�s �The field may be used in reports� option to create lookup reports. Turn it on quickly, as soon as save report, turn it off. This just changes the fields availability in new reports, not existing ones.
3. Create bulk import spreadsheet with VLOOKUP translation for each target table.
4. Make sure no users are making changes to system
5. Move primary key � WARNING � This orphans the relationship but the data will re-appear after we re-import the correct primary key
6. Import VLOOKUP bulk imports to target tables
7. Test a few records to confirm the change
8. Update any bulk import templates with the new primary key data. Send any users doing bulk imports the new primary key to old primary key translation table.
9. (if applicable) Delete old primary key after a few weeks of no issues.
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.