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
6
votes
4 answers

How do I easily find IDENTITY columns in danger of overflowing?

My database is getting old, and one of my biggest INT IDENTITY columns has a value around 1.3 billion. This will overflow around 2.1 billion. I plan on increasing it's size, but I don't want to do it too soon because of the number of records in the…
Keith Walton
  • 5,211
  • 6
  • 39
  • 53
6
votes
1 answer

Generate identity for an Oracle database through Entity Framework using an exisiting stored procedure

How to automatically generate identity for an Oracle database through Entity Framework? I have a function that I could call and generate the column which is not in the context how do I explicitly call the stored procedure through Entity Framework? I…
user1056085
  • 61
  • 2
6
votes
7 answers

"There can only be one IDENTITY column per table" - Why?

"There can only be one IDENTITY column per table" Why is it so? Take a scenario of a vehicle, there exists a chasis number which is unique as well as the registration number which turns out to be unique. To depict this scenario in sql server we need…
Chaitanya
  • 1,698
  • 5
  • 21
  • 41
6
votes
3 answers

How new Identity Jump feature of Microsoft SQL Server 2012 handles the range of data type?

I thought it was a bug but after reading this article http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is, I found that it's a new feature of SQL Server 2012. This feature increments your last identity column…
Aishwarya Shiva
  • 3,460
  • 15
  • 58
  • 107
6
votes
4 answers

Sql Server Legacy Database To Clustered index or not

We have a legacy database which is a sql server db (2005, and 2008). All of the primary keys in the tables are UniqueIdentifiers. The tables currently have no clustered index created on them and we are running into performance issues on tables…
Peter
  • 1,776
  • 13
  • 20
6
votes
6 answers

SQL Reset Identity ID in already populated table

hey all. I have a table in my DB that has about a thousand records in it. I would like to reset the identity column so that all of the ID's are sequential again. I was looking at this but I'm ASSuming that it only works on an empty table Current…
Chase Florell
  • 46,378
  • 57
  • 186
  • 376
6
votes
5 answers

Can I make an identity field span multiple tables in SQL Server?

Can I have an "identity" (unique, non-repeating) column span multiple tables? For example, let's say I have two tables: Books and Authors. Authors AuthorID AuthorName Books BookID BookTitle The BookID column and the AuthorID column are…
johnnycakes
  • 2,440
  • 2
  • 28
  • 36
6
votes
6 answers

Is a guid as identity field better in domain-driven design?

Is it easier to implement domain-driven design when using guids as identity fields instead of auto incrementing integers? With guids you don't have to jump to the database to get the actual value.
Lieven Cardoen
  • 25,140
  • 52
  • 153
  • 244
6
votes
3 answers

SQL Server continue identity count after truncating table

I seem to remember in MySQL when truncating a table the auto incremented index field would continue where it left off. So if said table was truncated with the highest id was 100, the next id after truncation would be 101. Is there a way to do this…
ckpepper02
  • 3,297
  • 5
  • 29
  • 43
5
votes
1 answer

Can't use "GENERATED ALWAYS AS IDENTITY" when creating Postgres tables on Dbeaver?

I am using Dbeaver to create a Postgres database table but am getting a syntax error when using "GENERATED ALWAYS AS IDENTITY" for my incremented id value. It is strange because I used the exact same syntax when creating the table on my localhost…
ronhoward
  • 61
  • 2
  • 6
5
votes
2 answers

Turning IDENTITY_INSERT ON on a table to load it with DB Unit

I try to load a table, that have an identity column, with DB Unit. I want to be able to set the id value myself (I don't want the database generate it for me). Here is a minimal definition of my table create table X ( id numeric(10,0) IDENTITY…
Octave
  • 351
  • 2
  • 11
5
votes
3 answers

How to use %ROWTYPE when inserting into Oracle table with identity column?

I have an Oracle 12c database with a table containing an identity column: CREATE TABLE foo ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, bar NUMBER ) Now I want to insert into the table using PL/SQL. Since in practice the table has…
Anders
  • 8,307
  • 9
  • 56
  • 88
5
votes
1 answer

Returning the value of identity column after insertion in Oracle

How do I return the value of an identity column (id) in Oracle 12c after insertion? Seems like most of the approaches out there uses sequence to get back the id of the inserted item.
kernn
  • 59
  • 2
  • 5
5
votes
2 answers

Remove [NOT FOR REPLICATION] from all Identity columns of Database tables

I have a Database which is containing lot of tables with Identity columns set to [NOT FOR REPLICATION]. in SQL Server 2008 R2 Is there any way that I can remove this constraint from all tables from Management Studio or any Query thanks. Create Table…
Zeeshanef
  • 639
  • 3
  • 14
  • 23
5
votes
3 answers

SQL Server -- Any way to add a column and make it first column in table?

I'm altering an existing table to add an Identity column. That I can do, no problem. But I'm wanting to be sure that people who look at it in the future will see that it has the identity column added, so I really want to make it column 1. I know…
Joe Baker
  • 186
  • 1
  • 1
  • 11
1 2
3
…
24 25