Change Primary keys VBA 2

[tobynegus]

Hi
I am trying to change the primary keys on a table
I have two fileds set as primary Keys, I want to drop thease keys (keep the fields) and set a new single primary Key (auto Increment)
I have looked on the web but without any success, this must have been done by someone somewhere, any help much appreciated.

Toby

 

[Golom]

To remove the current Primary Key

ALTER TABLE myTable DROP CONSTRAINT PrimaryKey

To Create the Autonumber Column

ALTER TABLE myTable ADD COLUMN ANum Counter(1,1)

and to make that the Primary Key

ALTER TABLE myTable ADD CONSTRAINT  PrimaryKey PRIMARY KEY  (ANum)

 

[MikeC14081972]

I hate the practice of using AutoNumbers as primary keys.

Autonumbers increment anyway so you won't get dupes.

Alos don't forget if you delete the data from the table and then compact the database the autonumber will reset itself.

 

[dhookom]

Mike...,
I love the use of autonumbers as primary keys. I use them all the time and find them great for creating relationships and in every other way. I have been doing this for many years and hundreds of tables (Access and SQL Server) without ever wishing I done something different.

I will often set another field in the table as unique so there won't be duplicates but the Autonumber is my primary key.

There have been lots of debates regarding the use of autonumbers or natural keys but I just wanted to add my 2 cents from years of experience.

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

I hate the practice of using AutoNumbers as primary keys.

Autonumbers increment anyway so you won't get dupes.

????

AutoNumbers were pretty much invented for use as primary keys - and for that purpose they work great. They are the easiest way to create a unique value for every record - which is really the only requirement of a primary key.

 

[MikeC14081972]

dhookom - I'm not saying it's right or wrong but just hate the practice and of course your opinion is appreciated.

At least you added the below it highlight the importance of not soley relying on autonumber.

I will often set another field in the table as unique so there won't be duplicates but the Autonumber is my primary key.

I have seen to many apps where the autonumber is the primary key and no other unique field.

Like you said over the years there has been much debate on this subject, and thats the side of the fence that I sit, but it's only my opinion.

 

[JoeAtWork]

I have seen to many apps where the autonumber is the primary key and no other unique field.

What's wrong with that - it's a good design principle in my opinion. I can give reasons:
1. Primary Keys are best if they have no meaning to the user. That ensures that changing business rules in the application do not require schema changes in the database (e.g. some "meaningful" field that used to be unique no longer is).
2. If you don't use an autonumber, you will have to invent some way to create your own unique numbers. This is a little more involved than the simple "Max(ID) + 1" functions that many novice programmers often implement. Why reinvent when a solution already exists?

The only argument I've ever seen against autonumbers is that there used to be a bug where sometimes an autonumber would repeat (I believe its the "Compacting" one you mentioned). My understanding is this bug was fixed quite a while back. I just did a quick test in Access 2003 SP2 and compacted a 2000 Format database and the Autonumber did not reset itself.

 

[dhookom]

I think MikeC14081972 is critical of tables that should have a unique index placed on a field that should always be unique. For instance, the title of a class. Typically this should be a unique value.

Duane
Hook'D on Access
MS Access MVP