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
1
vote
4 answers

Alternatives to Identity Column for Table With Frequent Inserts & Deletes?

Lets say I have a session table like this: [Session] ------- Id: int UserId: int Imagine that is used in an extremely high traffic site and Sessions are very frequently added and deleted. If I were to make the Id column of each table an Identity…
Ocelot20
  • 10,510
  • 11
  • 55
  • 96
1
vote
4 answers

Identity Column not working

i'm having problem insert record to a particular table regarding identity. But SQL always tell to me that I need to turn on identity_insert but I already set identity column for that table so that i'm sure it will not produce multiple id. By the way…
Rob
  • 638
  • 3
  • 14
  • 34
1
vote
1 answer

Unit testing with identity columns

I am using IDENTITY columns in my SQL Server tables. I have a unit test where I delete a data record with a certain id. When I insert the testdata I get this exception: Cannot insert explicit value for identity column in table 'MyTablename' when…
Pascal
  • 12,265
  • 25
  • 103
  • 195
1
vote
2 answers

Does the entity framework preserve ordering when it does inserts into the database?

We plan on using identity columns in our sql server database. Currently we are using guids to generate unique ids, but it turns out that the ordering is relevant so we consider switching to identity columsn. Since ordering is relevant we want to…
Jeroen Huinink
  • 1,947
  • 3
  • 17
  • 31
1
vote
1 answer

Teradata: How can you add an identity column to an existing table?

I need to add an identity column to an existing table with this SQL: alter table app.employee add ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100000000 NO CYCLE) I can create new tables with an…
oscilatingcretin
  • 10,457
  • 39
  • 119
  • 206
1
vote
2 answers

Output multiple identity columns from temp table

I'm using SQL Server 2K8 and have a table used solely to generate ids so that the primary key is unique across multiple tables -- the uniqueness across multiple tables is for an element hiearchy tree that requires each nodes to have a unique id…
soundslike
  • 393
  • 3
  • 12
1
vote
2 answers

ExecuteStoreCommand returns -1 , EntityFramework , C#?

I wanna get the next automatically incrementing primary key, Here I understood to use T-SQL for doing that. So I wrote the following method : public int GetLastNewsID() { const string command = @"SELECT IDENT_CURRENT ('{0}') AS…
Mohammad Dayyan
  • 21,578
  • 41
  • 164
  • 232
1
vote
1 answer

how do I go about ensuring that the recently generated id is returned when we insert a record via a PostgreSQL View's trigger's function?

Here is info about our technical development environment : PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit Here are the various code snippets associated with the various database objects in my PostgreSQL database: CREATE TABLE IF NOT…
crazyTech
  • 1,379
  • 3
  • 32
  • 67
1
vote
1 answer

Sybase JDBC get generated keys

In Postgres, I can write INSERT .. RETURNING * To retrieve all values that had been generated during the insert. In Oracle, HSQLDB, I can use String[] columnNames = ... PreparedStatement stmt = connection.prepareStatement(sql, columnNames); //…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
1
vote
2 answers

Azure Synapse IDENTITY COLUMN; Wrong Id values based on seed

The goal is to create a table, insert some (3) dummy rows for technical reasons, then for any valid data, start using Ids above 100. Script for creating (drop-create) the table: IF OBJECT_ID(N'dbo.IdentityInsertTest') IS NOT NULL DROP TABLE…
Arklur
  • 173
  • 1
  • 11
1
vote
2 answers

Identify of Currently Inserting Row

I'm working on a project and the question came up: Can an insert statement insert it's own identity in to another field? The idea is that there would be a hierarchical arrangement of records and that the 'parent' field would point to the ID of the…
cjbarth
  • 4,189
  • 6
  • 43
  • 62
1
vote
1 answer

Define column values to be selected / disselected as default

I would like to automate selecting of values in one column - Step_ID. Insted of defining which Step_ID i would like to filter (shown in the code below) i would like to define, that the first Step_ID and the last Step_ID are being to excluded. df =…
Parsyk
  • 321
  • 1
  • 3
  • 11
1
vote
2 answers

Inserting Entity into SQL Compact 4 Table with Identity column using LINQPad

I'm trying to insert new records into a SQL CE 4 database with LINQPad and having problems with the identity problem of a table. Let's say I have this simple table for instance: PEOPLE Id int IDENTITY(1,1) NOT NULL, Name nvarchar(100) NOT NULL I…
Jeff LaFay
  • 12,882
  • 13
  • 71
  • 101
1
vote
0 answers

Fehler: Can't subset columns that don't exist. x The column `plot_id` doesn't exist

Hi Guys I am having problems with my RStudio. My orders areas followed: library(tidyverse) library(asdreader) read_spectra <- function(dir_to_data = "I:/Master Arbeit/ASD Data to work/Data_Dok/20200315_DOK", meas_protocol =…
TJ Rilko
  • 11
  • 1
  • 2
1
vote
1 answer

PK Insert in Oracle (similar to --- SET IDENTITY_INSERT table on, in SQL Server)

In SQL Server it requires certain option to be ON to insert value in ID column – SET IDENTITY_INSERT table ON. Could someone help me with the Oracle equivalent? Or will Oracle allow ID value to be inserted by default? What should we replace SET…
Dothertechie
  • 27
  • 6
1 2 3
…
24 25