Changing Primary Key

[gary.newport]

I have a relational database with a list of students within one table. I now realise that I need to use a student number that pre-exists and it would make maximum sense to use this as the primary key.

Currently I have the primary key on an autonumber field.

I have over 1200 students and over 500 records related to their names.

Is there a way of shifting the primary key over to a new field or changing the existing primary key field to match this new unique identifier.

I know the answer is probably no.

 

[Rabbie]

It would not be difficult to change your primary key but why? It is my personal opinion backed up by many posters on this forum that an Autonumber primary key is the best solution. It might be helpful if you could explain why you want/need to change your PK.

 

[gemma-the-husky]

the autonumber need only be used to link records between tables. you can still have the true id in the student table - the benefit of doing it this way, is that if you ever need to change a studentid, you can just do this straight off.

 

[georgedwilkinson]

You can do it. If you have to ask how, it is probably a little more complex than you should take on. There is no good reason to do this.

 

[gary.newport]

Trouble is George; saying that makes me think 'damn you, I'll find out and do it!' simply to spite the implication that I can't do it (I have a touch of SQL coming on).

However, I'm going to agree with Gemma (and in turn you George) and re-consider if I need to do this.

Considering other issues and the relatively small scale behind this system currently I may rebuild the structure as a new system and be damned!

 

[Rabbie]

Trouble is George; saying that makes me think 'damn you, I'll find out and do it!' simply to spite the implication that I can't do it (I have a touch of SQL coming on).

However, I'm going to agree with Gemma (and in turn you George) and re-consider if I need to do this.

Considering other issues and the relatively small scale behind this system currently I may rebuild the structure as a new system and be damned!

Are you presently using the Autonumber field in relationships with other tables at present. If so you will need to modify the design of those tables to use the StudentNumber as a foreign key. Then write an Update Query to populate that field in each table with the correct StudentNumber. Then you can change the design to make StudentNumber the primary Key

 

[georgedwilkinson]

Trouble is George; saying that makes me think 'damn you, I'll find out and do it!' simply to spite the implication that I can't do it (I have a touch of SQL coming on).

I certainly didn't mean to cause offense. I just thought it through when I read your question and thought to myself, "there's no way I can explain something that complex in a forum." So, I was really indicting myself for my poor communications skills.

 

[gary.newport]

Sorry Rabbie

I started answering the post and then realised that you had posted a response and a question first. However, my laptop went funny and I wanted to go home; not realising that I had clicked on the post button first (makes George's statement look more viable now - doh!).

The issue (not really an issue; simply a thought) is that I originally pulled the student data from a spreadsheet I had been sent but now want to use the data extracted from the main school database. Since I will want to update the records on a regular basis I need to use the unique identifier from the main system to connect the imported data with confidence. Since this data is unique it makes sense not to have two unique fields for the single recordset.

Thanks for your second post; this makes sense (George ; ha ha ha). I'll now look into this.

Thanks again Rabbie and sorry for ignoring you the first time.

 

[boblarson]

I would keep the autonumber because - what if, down the line they change the rules on you and the student number changes. Maybe it isn't that it isn't unique but that the definition changes - instead of a number it now has letters. Or instead of being 5 numbers and 2 letters it is 2 letters, 5 numbers and an additional 2 letters.

Better to let Access manage the keys and you let it do so behind the scenes. You can include your data but it doesn't have to be the primary key.

 

[gary.newport]

I can see that Bob. I can't see them changing the structure to that extent since this is a primary key within a system used throughout all schools.

Yet, I do agree. There is no need to make the changes since I could include the field as an additional. The issue with changing the data structure, however, may not be such an issue if I allowed the primary key to be a unique text field?

 

[gary.newport]

I can see that Bob. I can't see them changing the structure to that extent since this is a primary key within a system used throughout all schools.

Yet, I do agree. There is no need to make the changes since I could include the field as an additional. The issue with changing the data structure, however, may not be such an issue if I allowed the primary key to be a unique text field?

In response to Rabbie, the only reason is redundancy of data. To have two unique fields identifying a single entity seems unnecessary data storage. Since I would not be indexing the imported key and the data is small scale then the speed issue is negligable but I hate having wastage and redundancy when unnecessary.

But, as I say, I'll now go lie down and then ignore my previous desire (except on my dummy system, where I will not play the system to death).

 

[neileg]

I can see that Bob. I can't see them changing the structure to that extent since this is a primary key within a system used throughout all schools.

Don't be so sure! Most people on here will have been suckered by that one. I remember building a system using data from our payroll system where the Pay ID was guaranteed to be unique. There was absolutely no problem right up to the point where they changed to a new payroll system with a completely different format for the Pay ID.

Don't let other people or systems determine your primary keys. Sooner or later you will regret it.

 

[dbDamo]

I came across this when trying to decide which type of Primary Key is best to use - http://www.broomscloset.com/closet/dbms/autonumber01.html

I ended up going with Autonumber...

 

[Rabbie]

In response to Rabbie, the only reason is redundancy of data. To have two unique fields identifying a single entity seems unnecessary data storage. Since I would not be indexing the imported key and the data is small scale then the speed issue is negligable but I hate having wastage and redundancy when unnecessary.

But, as I say, I'll now go lie down and then ignore my previous desire (except on my dummy system, where I will not play the system to death).

I suspect an index on a text key takes up more space than having the textfield and an indexed autonumber field. Text indexes are notoriously resource consuming.

 

[Atomic Shrimp]

Don't be so sure! Most people on here will have been suckered by that one.

This. I was victim to it with the switch from ISBN 10 to ISBN 13, on a system someone else had developed, and (along with a bunch of horrendous normalization and other issues) it was so deep and serious a problem, there was no way to fix it.

Don't let other people or systems determine your primary keys. Sooner or later you will regret it.

Quoted for truth. When people say "this numbering scheme will never change", they're usually naive and wrong.

Even a numbering system designed with future capacity in mind may be revised by new management or when it merges with something else, or a hundred other reasons.

There is no good reason to do this, and plenty of good reasons to run away screaming from the very suggestion.

 

[gemma-the-husky]

personally , i feel that the simplicity, and efficiency of a single field longint key (autonumber), that can never become outdated, or require changing to a different key type - more than outweighs the drawback of maintaining this as an extra key.

 

[boblarson]

personally , i feel that the simplicity, and efficiency of a single field longint key (autonumber), that can never become outdated, or require changing to a different key type - more than outweighs the drawback of maintaining this as an extra key.

Here, Here!

 

[gemma-the-husky]

and i didnt really mean drawback - just slight system overhead

 

[wiklendt]

Here, Here!

sorry to correct you, bob, but it's supposed to be "hear, hear!"....

http://en.wikipedia.org/wiki/Hear,_hear

 

[Galaxiom]

I had to look. This thread is building quite a following. Most have commented on the the philosophy of an existing autonumbered artifical key versus the student number natural key. I shall refrain from even commenting with my opinion.

However nobody has suggested how it might be done except to say it is a complex task. In fact it is incredibly easily done. I stumbled across it testing the basis for a smart@rse comment a while back.

Simply enable Referential Integrity on the primary key field in all the Relationships involving the original key. Run an Update query on the key field in the Student table to convert it to the student number and all the other tables will automatically follow.

Turn off the Access automatic cascading of renamed fields to forms and queries. Delete the student number field and rename the autonumber field to the student number field. Done.

Now you just need to decide if it is worth doing.