I've got a table that looks like this:
Table: Relations
Fields:
id: primary key
RelationNumber: the exposed unique number for each relation
Using the primary key quite a few tables are connected to the table Relations.
How do I:
- remove the current primary key
- make the field RelationNumber the primary key (wich also auto-increments)
- keep the relations between tables correct
?
Because that would be the ideal situation.
A more simple solution would be a way to "auto-increment" the field "RelationNumber" when a new relation is added.
Is there a simple way to do that?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
During development it was decided to use a hidden primary key to connect tables; and use a displayed field wich contains the relationnumber for each record. That number is used by end-users for reference.
So, the id is a hidden field and is not really needed; as the relationnumber is also unique!
The best solution would be to drop the hidden id-field and use the relationnumber as the primary key; but it is a nightmare to make that switch!
What you are decribing appears to me to be quite normal. There is an everlasting debate in Access circles- or maybe in database development circles - about the use of natural keys versus artificial keys but I am firmly in the artificial key camp. I use autonumber Ids an all my tables whether or not there is another unique key available and all my relationships use the autonumber fields. If I need uniqueness on another field then I just define that in the indexes.
If you are part of the 'opposition' (:-) then I should probably withdraw from this Q because we are not going to see eye to eye on the need for change.
The problem of 2 auto-increment fields was solved by dropping the hidden key. The visible identifier is now also used to connect to other tables.
Was quite some work to update all the related tables, forms and reports. But the system now works way more simple.
Major advantage of this method is that it is very easy to import data that is related. The primary key is also used in public, you don't need to look it up first. This saves a lot of time!
Business Accounts
Answer for Membership
by: peter57rPosted on 2007-11-23 at 04:08:19ID: 20338410
Given that you say your alternative solution would be ok, I'm not quite clear on what problem is being addressed here.
You can have RelationNumber set as a unique index, without making it a PK.
Or if you want to change the PK just open the table in design view and click the PK button when you are on the ID field (to unset it) and click it again on the RelationNumber (to set it).