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
5
votes
5 answers

SQL Server inserting huge number of rows to a table with default values and identity column in it

I need to insert about 6400000 rows a table with 2 columns CREATE TABLE [DBName].[DBO].[BigList] ( [All_ID] [int] identity(1,1) NOT NULL, [Is_It_Occupied] [int] default(0) not null ) I am using the following code today, which takes very long…
Naresh
  • 658
  • 1
  • 7
  • 22
5
votes
1 answer

MySQL join 2 tables but rename columns because they have the same name

I have 2 tables, admin, pricing admin contains columns (id, date_created, type, value) pricing contains columns (id, date_created, relation, value) I want to do a select that joins the two tables where pricing.relation = admin.id How do I rename…
Jimmyt1988
  • 20,466
  • 41
  • 133
  • 233
4
votes
8 answers

Sql Server 2005 Primary Key violation on an Identity column

I’m running into an odd problem, and I need some help trying to figure it out. I have a database which has an ID column (defined as int not null, Identity, starts at 1, increments by 1) in addition to all the application data columns. The primary…
Nigel
  • 41
  • 1
  • 3
4
votes
6 answers

Get IDENTITY value in the same T-SQL statement it is created in?

I was asked if you could have an insert statement, which had an ID field that was an "identity" column, and if the value that was assigned could also be inserted into another field in the same record, in the same insert statement. Is this possible…
Sako73
  • 9,957
  • 13
  • 57
  • 75
4
votes
1 answer

Maintaining a foreign key relationship when inserting into tables with autoincrementing Id's

I have two tables: Defect and DefectData. Each Defect may or may not have one or many DefectData. As such DefectData has a DefectId column as a foreign-key. The Id in both tables is an autoincrementing identity. The problem I am having is that when…
TheGwa
  • 1,919
  • 27
  • 44
4
votes
2 answers

EF returns 0000-0000-0000-xxx as Guid when I try save a new record or update existing record?

I am using EF4 in my C# project. The problem I'm faced with is that, when I try to save a record, I get a Primary Key Violation and the PK value is "0000-0000-0000-xxx". From my guess, EF does not recognize the IsIdentity flag and generate a guid…
Donald N. Mafa
  • 5,131
  • 10
  • 39
  • 56
4
votes
2 answers

Insert multiple fields with identity column doesn't work

I might be overworked today, but I am not getting this situation. It has to be something silly that I am simply overlooking. The table structure is as follows: CREATE TABLE sample_table ( id int8 NOT NULL GENERATED ALWAYS AS IDENTITY, name…
Mr.P
  • 1,182
  • 3
  • 20
  • 44
4
votes
1 answer

SQL Best Practices for Identity value hard coding

First, I know this is a rather subjective question but I need some kind of formal documentation to help me educate my client. Background - a large enterprise application with hundreds of tables and SP's, all neatly designed with normalized tables…
n4esa
  • 145
  • 10
4
votes
2 answers

Multiple insert SQL oracle

How do you do multiple insert with SQL in Oracle 12c when you have an identity column? INSERT ALL INTO Table1 (Column2) Values (1) INTO Table1 (Column2) Values (2) SELECT * FROM dual; where Table1 has column1 as an identity, will set the identity…
Gary In
  • 687
  • 3
  • 10
  • 19
4
votes
1 answer

Alter exisitng int column to identity in sybase

Sybase 12.5 I have an existing table in production that needs it's PK int column to be altered such that it is auto populated - when the table was created it would ideally have had the ID column created as an Identity. This ID column is a foreign…
The Ghost
  • 681
  • 1
  • 6
  • 15
4
votes
2 answers

Reseed identity column to 0 fails - current identity value is NULL

I restore database and delete records in my_table using delete from my_table; Then I reseed table identity column using this query: DBCC CHECKIDENT('dbo.my_table', reseed, 0); and the message I got is: Checking identity information: current…
davor
  • 939
  • 2
  • 14
  • 31
4
votes
2 answers

BCP not copying all rows

I am using BCP to import rows into SQL server. All seemed to work fine when I realized that BCP is not importing all rows on a random basis. Some data is skipped (sometimes 50%). The BCP procedure is not returning any error. It says successfully…
DataRiver
  • 188
  • 1
  • 8
  • 21
4
votes
1 answer

Linq to Sql: Can I return the Identity_Scope after an insert?

After I do an insert using linq to sql, can I get the Identity_scope value back if my table has an identity column?
Jeremy
  • 44,950
  • 68
  • 206
  • 332
4
votes
1 answer

Exponentially deteriorating performance on inserts in SQL Server Compact 4.0 tables with an Identity column

EDIT: the issue below has been fixed in the Entity Framework 6. Running the code below takes a disappointing 2 minutes and 10 seconds. Running it a second time takes 6.5 minutes. This question is related to this one Private Sub RunTest() Dim sw…
Dabblernl
  • 15,831
  • 18
  • 96
  • 148
4
votes
2 answers

T-SQL: CTE with identity columns

I'm building a tree (bill of materials style), and transforming some data. Consider the following table: BillOfMaterials BomId ParentId Now I'm using a CTE to fill it up: with BOM as ( select @@identity as BomId, null as ParentId
Bas
  • 26,772
  • 8
  • 53
  • 86
1 2 3
…
24 25