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
14
votes
3 answers

How to reseed an an auto increment column in a SQLite database?

Is it possible to reseed an auto increment column in a SQLite database, and if so, how is this done? ie. The equivalent of DBCC CHECKIDENT ('MyTable', RESEED, 1) in SQL Server.
Mun
  • 14,098
  • 11
  • 59
  • 83
14
votes
5 answers

Does SQL Server guarantee sequential inserting of an identity column?

In other words, is the following "cursoring" approach guaranteed to work: retrieve rows from DB save the largest ID from the returned records for later, e.g. in LastMax later, "SELECT * FROM MyTable WHERE Id > {0}", LastMax In order for that to…
balpha
  • 50,022
  • 18
  • 110
  • 131
13
votes
4 answers

Changing Identity Seed in SQL Server (Permanently!)

Is there any way of changing the identity seed for an identity column permanently? Using DBCC CHECKIDENT just seems to set the last_value. If the table is truncated all values are reset. dbcc checkident ('__Test_SeedIdent', reseed, 1000) select…
avenmore
  • 2,809
  • 3
  • 33
  • 34
12
votes
3 answers

Cannot insert explicit value because IDENTITY_INSERT is OFF, but cannot SET IDENTITY_INSERT to ON because it is already ON

I have a table in database Foo named Bar, that has a column named ID, which is the primary key, and this database is living on the development SQL Server. I'm trying to copy data from our production server into the development server so I can play…
CurtisHx
  • 746
  • 3
  • 11
  • 30
10
votes
2 answers

Why is the Hibernate default generator for PostgreSql "SequenceGenerator", not "IdentityGenerator"?

The default identifier generator for Postgresql in Hibernate is SequenceGenerator [1]. i.e. Hibernate will do SELECT nextval('hibernate_sequence') to generate an ID before doing an INSERT foo (id, ...) VALUES (123, ...) on session commit. However,…
Rich
  • 15,048
  • 2
  • 66
  • 119
10
votes
1 answer

Better to use SERIAL PRIMARY KEY or GENERATED ALWAYS AS IDENTITY for primary key in PostgreSQL

Not sure which option is latest best practice? I read on on this tutorial that: https://www.postgresqltutorial.com/postgresql-identity-column/ PostgreSQL version 10 introduced a new constraint GENERATED AS IDENTITY that allows you to automatically…
Zaffer
  • 1,290
  • 13
  • 32
10
votes
7 answers

In Entity Framework, getting the value of an identity column after inserting

I'm using EF4. I want to insert a new MyObject into the database. MyObject has two fields: Id: int (Identity) and Name: string As I've seen in documentation Entity Framework is supposed to set MyObject.Id to the value generated by database after…
nima
  • 6,566
  • 4
  • 45
  • 57
10
votes
3 answers

Oracle Alter command to rename existing Column errorring

alter table tablename rename column zl_divn_nbr to div_loc_nbr; Error while executing the above statement. Please help. SQL Error: ORA-54032: column to be renamed is used in a virtual column expression 54032. 0000 - "column to be renamed is used…
Prashanth
  • 109
  • 1
  • 5
8
votes
3 answers

ERROR: more than one owned sequence found in Postgres

I'm setting up a identity column to my existing columns for the Patient table. Here I would like to use GENERATED ALWAYS AS IDENTITY. So I setup the identity column by using the following statement (previously it was serial): ALTER TABLE Patient…
Nayan Rudani
  • 1,029
  • 3
  • 12
  • 21
8
votes
3 answers

RESEED identity columns on the database

Can I use the DBCC CHECKIDENT(, RESEED, value) command to reset an identity column current value to the original one in SQL Server 2008? If yes, is this the correct way of doing this operation without having any drawback? If not, is…
Lorenzo
  • 29,081
  • 49
  • 125
  • 222
8
votes
5 answers

How do you merge tables with autonumber primary keys?

I suppose everyone runs into this problem once in a while: you have two tables that have autonumber primary keys that need to be merged. There are many good reasons why autonumber primary keys are used in favour of say application-generated keys,…
Carvellis
  • 3,992
  • 2
  • 34
  • 66
8
votes
6 answers

Clustered indexes on non-identity columns to speed up bulk inserts?

My two questions are: Can I use clustered indexes to speed up bulk inserts in big tables? Can I then still efficiently use foreign key relationships if my IDENTITY column is not the clustered index anymore? To elaborate, I have a database with a…
littlegreen
  • 7,290
  • 9
  • 45
  • 51
8
votes
7 answers

SQL Identity with leading padded zeros

I have marked a column as Identity in my table create table Identitytest( number int identity(1,001) not null, value varchar(500) ) I need the identity column to be incremented as 001,002,003, etc. The database shows that it is inserting…
Innova
  • 4,831
  • 21
  • 76
  • 107
8
votes
1 answer

Get the vector of values from different columns of a matrix

I have a matrix 10x4, and I have a vector that has 10 elements. Each element is an column index of that matrix that should be retrieved. Here is the example: > M.mat [,1] [,2] [,3] [,4] [1,] -0.4236174 0.2228897 …
Vahid Mirjalili
  • 6,211
  • 15
  • 57
  • 80
7
votes
4 answers

SQL Set IDENTITY Field Using Variable

All, I want to start the numbering of an IDENTITY field based on the current maximum obtained from another table. So I have tried something like the following DECLARE @CurrentES INT; SET @CurrentES = (SELECT MaxES FROM…
MoonKnight
  • 23,214
  • 40
  • 145
  • 277
1
2
3
…
24 25