Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Autonumber as Primary Key

Status
Not open for further replies.

mekohler

Technical User
May 4, 2007
64
CA
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.
Thanks,
Michael Kohler
 
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!!


Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
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.
Cheers,
Michael
 
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
you may also considering using a uniqueidentifier for the purpose of marking a record (and letting that be used by other systems).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top