473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Primary keys: datafield v. autogen

sueb
379 256MB
This database I inherited has an automatically generated primary key, and also a datafield that is really the key to the data: it is unique, and the table is sorted by that field rather than by the primary key.

I'm itching to ditch the primary key and just assign the datafield as the primary key. Are there any pitfalls I should consider first?
Oct 21 '10 #1

✓ answered by Stewart Ross

If the table has any 1-many relationships with other tables you won't have values from your other candidate key in those tables. It is the current autonumber PK which will be carried forward as the secondary key into the many-side tables.

To resolve this you would have to add new fields to each many-side table to allow the proper key to be carried forward, then copy the corresponding key value into each record accordingly (keeping the existing key in place whilst you do so, to identify the correct rows for update).

Similarly, if there are any many-side subforms dependent on the current autonumber key you will need to alter the master-child link in the main form to refer to the new key field.

You won't be able to delete the current autonumber key field if it is participating in any relationships with other tables. If the existing relationship is properly defined in the Relationships window you will have to delete that relationship and set up a new one for the new key.

You will also have to be sure that there are no orphaned records which for one reason or another do not have appropriate values for the new candidate key. At present the new candidate key has no defined relationship with any other table, so Access will not have been able to protect relational integrity on the values concerned.

All in all, unless there are compelliing reasons to do away with the current autonumber version I'd leave this one alone and accept that it was not designed in entire accordance with relational best-practice!

-Stewart

2 1563
Stewart Ross
2,545 Expert Mod 2GB
If the table has any 1-many relationships with other tables you won't have values from your other candidate key in those tables. It is the current autonumber PK which will be carried forward as the secondary key into the many-side tables.

To resolve this you would have to add new fields to each many-side table to allow the proper key to be carried forward, then copy the corresponding key value into each record accordingly (keeping the existing key in place whilst you do so, to identify the correct rows for update).

Similarly, if there are any many-side subforms dependent on the current autonumber key you will need to alter the master-child link in the main form to refer to the new key field.

You won't be able to delete the current autonumber key field if it is participating in any relationships with other tables. If the existing relationship is properly defined in the Relationships window you will have to delete that relationship and set up a new one for the new key.

You will also have to be sure that there are no orphaned records which for one reason or another do not have appropriate values for the new candidate key. At present the new candidate key has no defined relationship with any other table, so Access will not have been able to protect relational integrity on the values concerned.

All in all, unless there are compelliing reasons to do away with the current autonumber version I'd leave this one alone and accept that it was not designed in entire accordance with relational best-practice!

-Stewart
Oct 21 '10 #2
sueb
379 256MB
Stewart, thank you for such a complete response. It gives me much to think about.

This database does not, currently, have any relationships based on the candidate new key. That, too, is one of the things I'm wanting to fix: the database is entirely flat, with fields that belong to the candidate key, but with three "sets" of fields repeated to contain data that really should be in a separate table (as though it were to keep only three Orders per Customer!). So perhaps, before I normalize the database, I might be able to make this change (there are also only three forms that currently access the data, although there should be more, and more functionally specific, ones).

I'll use your points as I analyze how to accomplish this. Thanks, again!
Oct 22 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Lucas Gump | last post by:
Is it possible to add primary keys to a table one-time and there are already multiple primary key defined? I have to change sp_id, so_id as primary keys to the existing primary keys dn_slot_id...
6
by: Andreas | last post by:
Hello list, what about uniqueness of inherited primary keys ? eg you have : create table objects ( id int4, date_created timestamp(0), primary key (id)
7
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am...
7
by: Philip | last post by:
Hey all, (Access 2000) I've been having a horror story with this design problem. My Database is Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each Item in each table...
7
by: Dave | last post by:
Hi, Maybe I'm missing something with the DataKeyField attribute of a datagrid but it seems that it's somewhat limiting since this only allows you to specify one field as the key. I have a...
9
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for...
115
by: LurfysMa | last post by:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can...
1
by: lochmant | last post by:
I have a series of about 10 test databases with about 34 linked table each. I need to create an Access 2003 database front end for each of these. I am currently trying to write a script that will...
2
by: Danny | last post by:
Hello, We imported a bunch of tables from a database and realized that the primary keys weren't copied to the destination db. In order to re- create the keys, we need to know which tables have...
4
by: Peter | last post by:
I am interested in informed feedback on the use of Constraints, Primary Keys and Unique. The following SQL statement creates a Bands tables for a database of bookings Bands into Venues, where the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.