Questions tagged [identity]
Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
192
questions
0
votes
1
answer
84
views
Auto-increment [id] field in a table in an SQL-Server database
I have different tables, containing an id field. That field is typically defined as id (PK, int, not null).
Normally, when adding an entry to a table, I add the value of the id field myself, but I'm ...
- 523
1
vote
1
answer
30
views
PostgreSQL: How to update a identity column to fix the error "duplicate key value violates unique constraint"
I have a table with the following structure:
CREATE TABLE categories (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name varchar NOT NULL
);
When I inserted some records with an explicit ...
- 111
1
vote
1
answer
48
views
PosgtreSQL — Is it possible to specify a sequence name in a GENERATED BY DEFAULT AS IDENTITY column?
When using PHP’s lastInsertId() method with PostgreSQL, it’s necessary to know the name of the sequence.
If I defined the column using GENERATED BY DEFAULT AS IDENTITY, I know I can get the sequence ...
- 2,759
0
votes
2
answers
129
views
The fastest and most memory efficient way to reset PK column values for a table with over 5 mililon records
I have the following table schema:
CREATE TABLE [inputs].[source](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NULL,
[ImportedOn] [datetime] NULL,
[Identifier_Value] [nvarchar](25) NULL,
...
- 49
6
votes
3
answers
1k
views
Regretting an identity: Is there a way to force inserts to specify the identity column?
To prevent an X-Y problem here's the actual problem we're trying to solve:
The Problem:
We have a bunch of lookup tables that were unfortunately created with an identity column on the Primary Key, ...
- 245
0
votes
1
answer
143
views
When removing entries in SQL Server, reset the identity seed
I've added records to a SQL Server database table. The table had a primary key established and the auto-increment identity seed is set to "Yes". This is done mostly because, with SQL Azure, ...
user269997
1
vote
1
answer
489
views
PostgreSQL use upsert with ON CONFLICT, or with separate INSERT and UPDATE statements?
We have a PostgreSQL 14 database where we store products fetched from online platforms. We have scrapers that are run on schedule which fetch products, and this data is then either inserted if the ...
- 13
1
vote
1
answer
1k
views
How can I insert multiple rows using a value returned from the first row?
I have a table, chat_rooms that has the following schema:
CREATE TABLE chat_rooms (
id integer GENERATED BY DEFAULT AS IDENTITY,
user_id uuid REFERENCES users(user_id) ON DELETE CASCADE,
...
- 145
-3
votes
2
answers
111
views
What could cause an unexpected gap of 9,907 in an IDENTITY column with the cache size of 1,000?
In our production server (SQL Server 2017) my user reported a large gap of the sequential number (IDENTITY column) used to reference claims. The sequential was 4251290.
After researching this known ...
- 1
2
votes
1
answer
78
views
What's the rationale for ALTER TABLE permission being required for SET IDENTITY_INSERT ON?
SQL Server requires the user to have ALTER TABLE permissions in order to insert specific values into an IDENTITY column.
That seems a bit strange, given that inserts are clearly a DML operation.
To ...
- 755
0
votes
3
answers
68
views
Store First and Lastname (Fullname) for Oracle Users?
Is it possible to store first-/last-/fullnames and possibly additional information of/for oracle user accounts in oracle?
I look at the table dba_users but it does not contain such information.
I need ...
- 4,757
1
vote
1
answer
274
views
Can one dictate the order of generated values when adding a new BIGSERIAL column to a table?
I need to add a new BIGSERIAL column to a huge table (~3 billion records). This question is similar to what I need to do and the accepted answer has helped me somewhat. But I'm still wondering about ...
- 25
1
vote
0
answers
1k
views
Postgres: with identity column as unique key, need help in upsert
Follow the below script to create two tables source_t and dest_t.
drop table if exists source_t;
create table source_t as
(select 1 id, 'dummy1' varcol union
select 3,'dummy3');
drop table if exists ...
- 154
0
votes
1
answer
176
views
XE_SERVICES_RWLOCK in top waits
We start facing some uncommon waits after the last release. XE_SERVICES_RWLOCK waits starts to appear in our top waits. Investigation shows that this waits happen during insert to our main OLTP tables....
- 1,451
2
votes
2
answers
1k
views
Primary keys due to inheritance
I am trying to design a database for postgrad office and we have a parent class "User" that has a username as a primary key for authentication and we have corresponding subclasses "...
0
votes
0
answers
47
views
MSSQL 2016: How do I Populate an Existing 2+ Billion Row Table with a Unique Primary key?
Is there a way to leverage the identity feature to backfill a primary key for an existing very large table? What I have done in the past is script a new table from the old table adding a new ...
- 157
0
votes
1
answer
167
views
Determine Available Blocks of Contiguous Ids
We have a SQL Server table that has an int autoincrement primary key column on it.
The table's primary key space is fragmented. For example, ids 1 through 10 are in use but then ids 11 through 100,000 ...
0
votes
3
answers
746
views
Identify all tables with an identity column
Is there a query I can run that will show me all tables with an identity column? Trying to figure out if there are any identity based tables whose current identity is nearing upper bound of int32 or ...
- 1,087
0
votes
1
answer
459
views
Why can't PostgreSQL identity columns be used in CREATE TYPE?
My impression is that each PostgreSQL table has a corresponding composite type associated with it. But it seems that the PostgreSQL identity column cannot be used in a CREATE TYPE statement:
=> ...
- 3,425
0
votes
1
answer
93
views
Must tuples of a relation and their referents be in a one-to-one relationship?
Figures 3.a and 3.b, section 1.4, page 381 of Edgar Codd’s 1970 landmark paper ‘A Relational Model of Data for Large Shared Data Banks’ presents a set of relation schemas (unnormalized in figure 3.a ...
- 71
12
votes
1
answer
15k
views
Restarting identity columns in Postgresql
For serial columns used in Postgresql < 10, we manage the sequence by its name. We were able to reset a sequence with:
SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));
From version 10, ...
- 355
4
votes
3
answers
5k
views
Copy tables to another SQL Server keeping the identity property
Objective
I want to make a exact copy of 10 tables from a database on a SQL Server into my local SQL Server. The local copy is used for testing.
Problem
The copied tables all have the identity ...
- 143
0
votes
1
answer
947
views
Is IDENTITY continuous in new versions of PostgreSQL? [duplicate]
Now I am use serial to generate table primary key in PostgreSQL 13, but today I found the ID jumps and has big gaps in the ID space. It will waste many IDs. I read the docs and found that PostgreSQL ...
- 755
1
vote
5
answers
2k
views
Why can't you add an Identity to an existing colum in SQL Server?
What is so special about an Identity column?
I am pretty sure there is a valid reason, but I'm trying to get a better understanding of SQL.
So what is so special about an Identity column that this can'...
- 355
8
votes
2
answers
2k
views
As there is no unsigned int in SQL Server doesn't an Identity Seed of -2,147,483,648 make more sense for large tables?
I just had a showerthought that the default Identity Seed is 1. I have some tables that I know will grow to the billions at a certain point. Wouldn't it make more sense to start on int.Min ( -2,147,...
- 355
1
vote
1
answer
210
views
What is MS Access ODBC SQL syntax for a table with AutoIncrement and Memo columns?
I am migrating a full framework .NET app to core. It used ADOX to create an MS Access database and tables. It used OLE DB to populate data. In ADOX, the data type was ADOX.DataTypeEnum.adInteger and ...
- 37
0
votes
3
answers
185
views
Load data from a Table that has the same structure as Table1 with the only diferrence of an ID not null column?
So, I know that if you want to load data into a table that does not have the same structure as the data, well you have to do it in the way of column by column, the thing that in this situation, I have ...
- 1
2
votes
1
answer
880
views
MASSIVE Identity column value jump in SQL Server 2014
So in the middle of basic coding and testing we saw a huge non-patterned jump in Identity values for multiple tables. We are unaware of any server blips or attempted bulk operations, but DBAs are ...
- 123
2
votes
0
answers
123
views
Is a three-way associative relationship correct in this case
So I have started modelling some entity relationships.
It goes as follows:
A Mandate represent a client. A row for a client (Mandate) will only appear once in the Mandate table.
Each Mandate can ...
- 31
1
vote
1
answer
3k
views
Grant rights to create a table with an identity column on another schema
I have a user in charge of deploying DDL on other schemas than its own.
This user can create a table on those schemas, it can also create sequences on those schemas.
But when I try to create a table ...
- 300
0
votes
2
answers
689
views
What's the easiest way to move tables with identity key columns while keeping the referential integrity between them?
I have four tables in database A that have identity key columns and have foreign key references to each other on those columns (without actual foreign key objects created, just the columns themselves)....
- 35.1k
3
votes
2
answers
5k
views
GUID vs Identity on a table performance [duplicate]
My question is using a guid(data type unique identifier) versus using identity specification(data type int). Are there any articles where it talks about why you would use one versus another? I'm at a ...
- 153
7
votes
1
answer
3k
views
Insert multiple rows into a table with only an IDENTITY column [duplicate]
I have a table called dbo.Groups defined like this:
CREATE TABLE dbo.Groups
(
GroupID int NOT NULL IDENTITY (1,1) PRIMARY KEY
);
The table really consists of just the one IDENTITY column.
...
- 22.8k
1
vote
2
answers
1k
views
What can cause what appears to be missing data in a single table [duplicate]
I have a situation where it appears as if I've lost data in a single table.
I have a .NET Core API, with a logging middleware. This middleware takes the entire request and response and stores in the ...
- 11
4
votes
2
answers
5k
views
Can SET IDENTITY_INSERT be allowed with less privileges than db_ddladmin?
I've inherited a system where the application is running under sysadmin account.
I limited this account to db_datareader + db_datawriter + EXECUTE on all database and set extended events session to ...
- 10.6k
0
votes
1
answer
698
views
Pentaho Data Integration - Kettle- Update Identity Column in Microsoft SQL Server
For migration purposes I need to migrate existing ID's from my old database to my new table in MS SQL Server.
I can truncate my table in MS SQL Server and then adjust my database connection in ...
- 17
0
votes
1
answer
84
views
Can I fix this insertion anomaly without using a composite key?
Suppose I have a schema with the following basic elements:
Client : id (PK)
Pet : id (PK)
owner_id (FK to Client)
Contract : id (PK)
client_id (FK to Client)
PetInContract : ...
- 101
9
votes
1
answer
2k
views
What is the idiomatic solution in SQL Server for reserving a block of ids for use in a bulk insert?
I have a table with an identity column and I want to reserve a block of ids which I can use for bulk inserting, whilst allowing inserts to still happen into that table.
Note this is part of a bulk ...
1
vote
2
answers
726
views
pg_restore fails with drop default on identity column
Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase. See below:
pg_restore --verbose -...
1
vote
0
answers
2k
views
Transactional Replication : Issues with Identity Column in Subscriber
I have an Transactional replication set up form Server A to Server B.
I have a table which has an Identity column as a primary key.
In my publisher ,for the table tb1,the the identity column,Id1 was ...
- 1,295
1
vote
1
answer
4k
views
Insert into table with column same values as auto increment column ( computed column )
Say I have a table with "column1" (Primary Key, Auto Increment) and "column2".
How can insert into the table with column2 will have the same values as column1 which is auto increment column?
- 13
0
votes
1
answer
85
views
SSMS: Restart the values on a IDENTITY field [duplicate]
I am starting to use SSMS to manage a database and I am having a problem I would appreciate some help with.
I have a table that was created with
CREATE TABLE [dbo].[Machine](
[Machine_id] [...
- 123
2
votes
1
answer
4k
views
How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?
How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly.
...
- 1,853
2
votes
2
answers
1k
views
Stored Procedure - Reseed Identity
I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another ...
- 53
2
votes
1
answer
5k
views
Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list? [closed]
My code:
SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON
INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
SELECT ColumnWithIdentity, ...
- 35.1k
0
votes
0
answers
39
views
Converting SalesForce IDs for Performance Reasons
I'm writing code for a data warehouse that extracts information from SalesForce. The format of the SalesForce IDs is like so:
001n000000UELLJAA5
Where each digit is a base-62 value denoted by:
a-z ...
- 121
2
votes
3
answers
2k
views
Is "SET IDENTITY_INSERT" required to insert arbitrary GUID into [uniqueidentifier] column?
SQL Server 2016 and 2017, Standard Edition
I need to insert raw data (from another table) into a table that has two uniqueidentifier columns.
Does this require that I use SET IDENTITY_INSERT Carrier ...
2
votes
0
answers
47
views
Windowed IDENTITY column definition? [duplicate]
Is it possible to create a windowed identity column that automatically initializes and increments in SQL Server 2017 or later?
I would like the conceptual functionality of SelectedSubsetId below.
...
- 703
6
votes
2
answers
2k
views
Serializable range deadlocks
Help required on an inherited deadlock problem!
The code given below appears to have been written some time back, in an attempt to address contention issues around the assigning of new ID values on ...
- 555
2
votes
1
answer
2k
views
Function SCOPE_IDENTITY not found
When I call SCOPE_IDENTITY in Sybase, I get
[Error] Script lines: 1-6 -------------------------- Function 'SCOPE_IDENTITY' not found. If this is a SQLJ function or SQL
function, use sp_help to ...
- 170