Skip to main content

Questions tagged [identity]

Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

Filter by
Sorted by
Tagged with
0 votes
1 answer
55 views

How to Make Queries on a DATETIME Column Efficient If My Primary Query Pattern is an Hour?

Context Here is the DDL that I am intending to use to define the table for a logistics/delivery company. CREATE TABLE scraping_details ( id INT IDENTITY(1,1) PRIMARY KEY, -- Identity insert and ...
Della's user avatar
  • 73
1 vote
1 answer
41 views

Is it possible to change the sequence backing an identity column?

After a machine malfunction and a hurried transfer of data onto another machine, we have somehow ended up with some brand new sequences replacing old sequences as the backing for identity columns (and ...
John Denniston's user avatar
0 votes
1 answer
29 views

Alter all tables in schema to set an existing field as an IDENTITY PRIMARY KEY

Is there an easy and robust way to execute these two commands on all tables in a given schema myschema in a PostgreSQL (13) database: ALTER TABLE myschema.table001 ADD PRIMARY KEY (oid); ALTER TABLE ...
s.k's user avatar
  • 372
0 votes
1 answer
71 views

How to correctly deal with IDENTITY fields on parent/child tables when using inheritance in PostgreSQL

I have a rather simple question when playing with a PG 15.1 database. I've tried to set up a simple inheritance case: DROP TABLE IF EXISTS cities CASCADE; CREATE TABLE IF NOT EXISTS cities ( id INT ...
s.k's user avatar
  • 372
0 votes
1 answer
224 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 ...
Dominique's user avatar
  • 581
1 vote
1 answer
328 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 ...
Jaime Suncin's user avatar
1 vote
2 answers
430 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 ...
Manngo's user avatar
  • 2,849
0 votes
2 answers
154 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, ...
Gleb Lashuk's user avatar
6 votes
3 answers
2k 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, ...
TTT's user avatar
  • 245
0 votes
1 answer
261 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, ...
user avatar
1 vote
1 answer
819 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 ...
Nuxurious's user avatar
1 vote
1 answer
2k 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, ...
DanMossa's user avatar
  • 145
-3 votes
2 answers
133 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 ...
George's user avatar
  • 1
2 votes
1 answer
136 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 ...
John's user avatar
  • 765
0 votes
3 answers
108 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 ...
Magier's user avatar
  • 4,797
1 vote
2 answers
338 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 ...
HeatZync's user avatar
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 ...
Sajith P Shetty's user avatar
0 votes
1 answer
206 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....
Artashes  Khachatryan's user avatar
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 "...
Ibrahim Abou Elenein 's user avatar
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 ...
plditallo's user avatar
  • 157
0 votes
1 answer
175 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 ...
Manny Siddiqui's user avatar
0 votes
3 answers
1k 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 ...
user2368632's user avatar
  • 1,097
0 votes
1 answer
713 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: => ...
tinlyx's user avatar
  • 3,610
0 votes
1 answer
96 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 ...
Géry Ogam's user avatar
13 votes
1 answer
21k 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, ...
jgrocha's user avatar
  • 365
4 votes
3 answers
8k 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 ...
baltermia's user avatar
  • 143
0 votes
1 answer
1k 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 ...
Dolphin's user avatar
  • 773
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'...
Dirk Boer's user avatar
  • 365
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,...
Dirk Boer's user avatar
  • 365
1 vote
1 answer
239 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 ...
Gandon Jank's user avatar
0 votes
3 answers
228 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 ...
Mike's user avatar
  • 1
2 votes
1 answer
1k 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 ...
BikeMrown's user avatar
  • 123
2 votes
0 answers
132 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 ...
George Well's user avatar
2 votes
1 answer
4k 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 ...
Hybris95's user avatar
  • 310
0 votes
2 answers
885 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)....
J.D.'s user avatar
  • 38.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 ...
user1633146's user avatar
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. ...
Andriy M's user avatar
  • 23.1k
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 ...
Dasch's user avatar
  • 11
4 votes
2 answers
6k 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 ...
sepupic's user avatar
  • 11k
0 votes
1 answer
763 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 ...
sql_mind's user avatar
0 votes
1 answer
87 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 : ...
ralbatross's user avatar
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 ...
Daniel James Bryars's user avatar
1 vote
2 answers
831 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 -...
Stuart Charlton's user avatar
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 ...
user9516827's user avatar
  • 1,315
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?
Alan's user avatar
  • 13
0 votes
1 answer
93 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] [...
KansaiRobot's user avatar
2 votes
1 answer
5k 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. ...
Riley Major's user avatar
  • 1,903
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 ...
DBA_Kyle's user avatar
2 votes
1 answer
6k 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, ...
J.D.'s user avatar
  • 38.1k
0 votes
0 answers
41 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 ...
Alex W's user avatar
  • 121