January 9, 2009 at 8:14 am
I've inherited a poorly designed database where instead of autonumbers for primary keys, the database uses words for the primary key in many tables. Unfortunately, that's caused me some trouble in terms of database development. We use Access as a front-end database because more people in my department know how to use it, but the database is actually a SQL Server database. How can I change the primary key in a table from the word to an autonumber? Do I need to delete the primary key it has now and create a new column? There are a number of SAS scripts that we use that need to use the current primary key (PSID), so I'll need to recreate that column as a text column.
Also, the table I'm using has relationships with four other tables based on the current primary key. How do I recreate those relationships to maintain referential integrity?
January 9, 2009 at 9:07 am
1. Create New PK on parent table.
2. Drop FKs to child tables.
3. Add new parent PK column to child tables.
4. Update Child table with new PK value from parent - use old PK values to join together.
5. Re-create FK relationships between child/parent.
6. Drop parent PK column from Child tables.
7. Drop PK from parent table.
I would recommend running a couple JOIN queries between the Parent and Child tables once step 3 is done. JOIN the tables on the old keys and compare the query results to a JOIN based on the new keys. Make sure that the relationships are as you expect prior to dropping columns.
January 9, 2009 at 9:08 am
Words aren't necessarily bad and plenty of people prefer using those to autonumbers since they might better represent a real key. An identity is a surrogate key.
If you want to change this, add the column to the database, then Access (relink) as an identity. Populate it to get existing values, seed to the next value, and then you can drop the PK designation from the existing column and add it back to your new one.
January 9, 2009 at 9:48 am
I agree that words aren't necessarily that bad, but it's caused some problems for data entry. The data entry forms in the Access side of the database used a very inefficient process that and that introduced a greater possibility of errors. I created drop down lists for genus and then the values in the species field were limited to those species within a specific genus. However, the PSID field still needed to be populated, but I can't get it to populate if the field is a primary key. If anyone has advice on how to accomplish that, I would appreciate your feedback.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply