Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Autonumber as Primary Key

Autonumber as Primary Key

Autonumber as Primary Key

Hi, I don't know if this is the correct forum or not. This is my situation: I use SSIS to bring in data from a separate SQL database to populate a table. It is during this process that I populate an ID field in the new table that is an integer, is auto number and acts as a primary key. Using SSIS I update the table every night. There is someone else in my office who then uses Access as a front end to create forms etc. to link to that table to populate other tables. She uses the ID field as a primary key in order to link to other tables; this ID field becomes development permit number. My question is when data is added or deleted in the original database, what happens to the auto numbering when data is added or deleted? Once an ID number has been assigned to a record, I don't want a different number assigned when I run the update module.
Basically I have a database in SQL for our GIS that brings in data from a proprietary database. I have created a database for development permits that combines financial data from the proprietary database with GIS data so that the data can be mapped.
Michael Kohler

RE: Autonumber as Primary Key

by autonumber I am going to assume a identity field.

These will not be reused except on the following situations.

1 - table is truncated - number restarts
2 - table is reseeded - value starts at whatever value you use.
3 - you set identity insert on that table and manually add a record with a previously used value

If you delete a record its number is not reused except on the above conditions.

If on the other hand you are using a sequence (2012 or higher) then rules are different... buy hey that is what manuals are for!!


Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Autonumber as Primary Key

Hi thanks for the reply. The table is truncated; is there an alternative? I don't know anyway of updating the data in the table without dropping the old data. And yes, it's an identity field. If the table is populated once, is there a way of populating the id field without having it as an identity field? I'm using SQL 2012, is there a topic I can research that would help this project.

RE: Autonumber as Primary Key

depending on the volumes you may delete instead of truncate. it is slower but depending on volume not necessarily an issue.

otherwise with 2012 you can go and read about sequences at https://msdn.microsoft.com/en-us/library/ff878091(...

you may also considering using a uniqueidentifier for the purpose of marking a record (and letting that be used by other systems).


Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close