Stack Overflow
Stack Overflow

Questions tagged [identity-column]

An Identity column is a column in a database table that is made up of values managed by the server and cannot be modified, to be used as a primary key for the table. It is usually an auto-incrementing number.

Several Database products provide means to guarantee unique sequences of numeric values. Manually coding values for a primary key can cause problems.

Common database support: - Oracle: SEQUENCE (stand-alone number generator) and IDENTITY column clause that uses a system-generated SEQUENCE - mySQL: AUTO_INCREMENT column clause - Microsoft SQL Server, IBM DB2: SEQUENCE (stand-alone number generator)

See also for questions not related specifically to a primary key.

368 questions
557
votes
19 answers

Adding an identity to an existing column

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database. What's the SQL…
Kirschstein
  • 14,570
  • 14
  • 61
  • 79
149
votes
6 answers

Identity increment is jumping in SQL Server database

In one of my tables Fee in column "ReceiptNo" in SQL Server 2012 database identity increment suddenly started jumping to 100s instead of 1 depending on the following two things. if it is 1205446 it is jumps to 1206306, if it is 1206321, it jumps to…
kashif
  • 3,713
  • 8
  • 32
  • 47
112
votes
14 answers

How do you determine what SQL Tables have an identity column programmatically

I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL. Results would be something like: TableName, ColumnName
Gabe
85
votes
8 answers

SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

I have a .net transaction with a SQL insert to a SQL Server 2005 database. The table has an identity primary key. When an error occurs within the transaction, Rollback() is called. The row inserts are rolled back correctly, however the next time I…
muhan
  • 2,537
  • 3
  • 29
  • 33
82
votes
9 answers

BULK INSERT with identity (auto-increment) column

I am trying to add bulk data in database from CSV file. Employee table has a column ID (PK) auto-incremented. CREATE TABLE [dbo].[Employee]( [id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [Address] [varchar](50) NULL ) ON…
Abhi
  • 1,963
  • 7
  • 27
  • 33
38
votes
6 answers

Inserting into Oracle and retrieving the generated sequence ID

I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution… INSERT into Batch( BatchName, BatchType, Source, Area ) Values…
Allbite
  • 2,367
  • 1
  • 24
  • 22
35
votes
9 answers

Can a sql server table have two identity columns?

I need to have one column as the primary key and another to auto increment an order number field. Is this possible? EDIT: I think I'll just use a composite number as the order number. Thanks anyways.
William Hurst
  • 2,231
  • 5
  • 33
  • 54
35
votes
2 answers

Reset autoincrement in Microsoft SQL Server 2008 R2

I created a primary key to be autoincrement. I added two rows: ID=1, ID=2 I deleted these two rows. I added a new row, but the new row's ID was: ID=3 How can I reset or restart the autoincrement to 1?
victorio
  • 6,224
  • 24
  • 77
  • 113
31
votes
6 answers

How to get list of all tables that has identity columns

I would like to learn how to fetch list of all tables that has identity columns from a MS SQL database.
Allan Chua
  • 9,305
  • 9
  • 41
  • 61
24
votes
5 answers

Auto-increment on Azure Table Storage

I am currently developing an application for Azure Table Storage. In that application I have table which will have relatively few inserts (a couple of thousand/day) and the primary key of these entities will be used in another table, which will have…
Yrlec
  • 3,401
  • 6
  • 39
  • 75
20
votes
3 answers

Column to be modified is not an identity column

I have created a table with column S_ROLL NUMBER(3) NOT NULL Now I want to make this colum to as identity column. I used this command alter table students modify ( S_ROLL NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY ); Then I'm getting this…
Asif Mushtaq
  • 3,658
  • 4
  • 44
  • 80
18
votes
1 answer

Linq to SQL - How to find the value of the IDENTITY column after InsertOnSubmit()

I am using LINQ to SQL to insert simple data into a table WITHOUT a stored procedure. The table has a Primary Key ID column, which is set as an IDENTITY column in both SQL Server and in my DBML. First I call InsertOnSubmit(); with data for a single…
Ash Machine
  • 9,601
  • 11
  • 45
  • 52
16
votes
1 answer

Oracle identity column and insert into select

Oracle 12 introduced nice feature (which should have been there long ago btw!) - identity columns. So here's a script: CREATE TABLE test ( a INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, b VARCHAR2(10) ); -- Ok INSERT INTO test (b)…
Kombajn zbożowy
  • 8,755
  • 3
  • 28
  • 60
15
votes
6 answers

Reset Identity column to zero in SQL Server?

How can I reset the Identity column of a table to zero in SQL Server? Edit: How can we do it with LINQ to SQL ?
Mohammad Dayyan
  • 21,578
  • 41
  • 164
  • 232
15
votes
3 answers

ORA-32795: cannot insert into a generated always identity column

Guys I am trying to execute below insert statement and I keep getting the error: cannot insert into a generated always identity column the statement is : INSERT INTO leaves_approval SELECT * FROM requests_temp r WHERE r.civil_number = 33322…
ghalib
  • 203
  • 1
  • 2
  • 9
1
2 3
…
24 25