Autonumbers or not?

[Ngan Bui]

I was just thinking if all of my tables really need
autonumbers for PKs or not.

For instance, do the following really need autonumbers?
Could I just have them be long integers and I manually
give each record a number (incremently too)?

tblCity - ID and CityName
tblCompType - ID and ComplaintType
tblProvider - ID and ProviderName
tblReport - ID and ReportName

These tables are mostly lookup tables (where other tables
just store the IDs for reference), and there aren't alot
of records. The most is the tblCity which has about 200
records.

The reason I ask is if I ever had to move the table or
whatever, I don't want the cities to receive a different
ID because the autonumbers are redone.

Whatcha think? If it's ok to just have long integer and
not autonumbers, can I change the existing autonumbers to
long integers?

Thanks.
Ngan

 

[Rebecca Riordan]

Your autonumbers won't be overridden if you move the records, so you needn't
worry about that. If that's your only concern, I can't imagine that it
would be worth the effort to re-create the autonumber functionality.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Ngan Bui]

What I meant is if I move over the records, they may get a
new autonumber and therefore the other tables will be
looking up a different city.

For instance, tblCity has 2 records. LA has ID of 4 and
Anaheim has ID of 9.

If I move the records over to a new tblCity, the
autonumbers are reset and therefore LA has ID 1 and
Anaheim has 2. Any tables having CityID field will be
looking at a different city or get nothing back.

That's the issue I have with autonumbers is if you need to
move/copy/append the records, autonumbers are reset and
you have to do a lil more to keep the numbers in sync.

Ngan

 

[Rebecca Riordan]

Ngan,

"Move over" doesn't have a technical meaning. If you _recreate_ the record,
yes, you will get a new autonumber. But why would you ever want to do that?
If you _copy_ the record from one table to another, the autonumber will
_not_ be changed. (I'm not clear why you'd want to do that either...)

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Tim Ferguson]

That's the issue I have with autonumbers is if you need to
move/copy/append the records, autonumbers are reset and
you have to do a lil more to keep the numbers in sync.

No they don't. If you append records into a table with an autonumber
column, and include the autonumber in the query, then the numbers will not
be changed. Of course, if there are duplicate numbers then you may get a
Key Violation error. The new autonumber seed will be set to one higher than
the highest number appended.

Hope that helps


Tim F