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
2
votes
1
answer
326
views
Identity management in Transaction replication with updatable subscription
I have migrated my publisher DB to new server yesterday. For migration what I did was
removed subscriptions and publication completely
backed up DB on which publication was configured
restored DB to ...
CommunityBot
- 1
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 ...
- 301
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 ...
- 47.8k
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 ...
- 47.8k
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,
...
- 82.9k
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, ...
- 82.9k
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 -...
CommunityBot
- 1
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, ...
- 82.9k
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 ...
- 47.8k
17
votes
4
answers
43k
views
Changing identity column from INT to BIGINT
I have a table with an identity column that is also a primary key. Currently, it has 50 million rows, with the highest value of the identity column sitting at 148,921,803. The table has a lot of ...
- 101
2
votes
2
answers
4k
views
How To Restore A Replicated Database
I am replicating our live database using transaction replication. This is basically to have a backup copy of our database in the case of server failure.
The replicated database is read-only, so I ...
- 123
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,
...
- 172k
12
votes
2
answers
4k
views
How does IDENTITY_INSERT affect concurrency?
I am trying to help a customer with a 3rd party SAP add-on that has a posting glitch and has gone out-of-support.
Under certain circumstances, it archives and incomplete post from the posting queue ...
- 191
-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 ...
- 29k
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 ...
- 83.3k
9
votes
6
answers
18k
views
When is the Identity Column as Clustered Index a Bad Idea?
Its just been a few months of programming in SQL Server for me so my knowledge is not good in many regards. In an already existing project at work I came across many tables with large composite ...
- 2,201
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 ...
- 8,259
6
votes
1
answer
5k
views
PostgreSQL How to DEFAULT Partitioned Identity Column?
PostgreSQL 11
What is the best way to generate default values for identity columns on partition tables.
E.g
CREATE TABLE data.log
(
id BIGINT GENERATED ALWAYS AS IDENTITY
...
- 172k
2
votes
1
answer
5k
views
How do Identity Columns get Replicated?
During Transactional Replication, we have data going from Server A to Server B.
Say we were to reseed an Identity Column on a Table on Server A (for whatever reason), how would this affect the data ...
- 2,201
3
votes
3
answers
3k
views
IDENTITY_INSERT seems stuck
I might be forgetting something, but essentially, I'm struggling with IDENTITY_INSERT.
I'm trying to move data between tables, both of which have an identity column (similar schema).
SET ...
- 437
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
3
votes
4
answers
3k
views
Merge Replication identity field issues
One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of ...
- 103
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 ...
- 172k
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 ...
- 229
49
votes
2
answers
283k
views
Best way to get last identity inserted in a table
Which one is the best option to get the identity value I just generated via an insert? What is the impact of these statements in terms of performance?
SCOPE_IDENTITY()
Aggregate function MAX()
...
1
vote
2
answers
15k
views
How to fix all duplicate key values from sequences?
I have a Postgres database that I created with a Django application. When I transferred it over, it never reindexed anything and it gives me errors trying to do this. It seems as if there are lots of ...
- 172k
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 ...
CommunityBot
- 1
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....
- 38.8k
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 "...
- 11.7k
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
7
votes
2
answers
7k
views
Why can a SQL Server table not have more than one IDENTITY column?
Why can a SQL Server table not have more than one identity column?
CREATE TABLE t(id INT IDENTITY, id2 INT IDENTITY)
Msg 2744, Level 16, State 2, Line 5
Multiple identity columns specified for ...
- 101
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:
=> ...
- 172k
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 ...
- 8,259
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 ...
- 161
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 ...
- 1
7
votes
2
answers
24k
views
How can I change an existing type from "bigint" to "bigserial"?
I have a PostgreSQL table with the following structure:
I simply need to change the TYPE of prove_identity_id from bigint to bigserial. I read the docs but wasn't able to understand how to legally ...
- 172k
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 ...
- 82.9k
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'...
12
votes
1
answer
3k
views
Why is removing the Identity property on a column not supported
I have read that after SQL Server 2000, the ability to "un-identity" an identity column was removed. And that this was "By Design" (not just a missing feature).
Here is an example I found on a blog. ...
- 82.9k
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 ...
- 45.9k
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, ...
CommunityBot
- 1
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 ...
- 82.9k
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 ...
- 172k
14
votes
3
answers
14k
views
Why is SSMS inserting new rows at the top of a table not the bottom?
Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
- 97.4k
16
votes
5
answers
79k
views
Add autoincrement to existing PK
I created a table in a DB that already exists in another DB. It was initially populated with the old DB data. The table's PK had to receive the values that already exist on those records, so it couldn'...
- 17.3k
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,...
- 32k
3
votes
2
answers
3k
views
Availability Group, asynchronous mode, manual/forced failover - handling identity overlap
We have a two replica 2017 Standard Availability Group in asynchronous mode. With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if ...
CommunityBot
- 1
48
votes
10
answers
74k
views
How do I copy a table with SELECT INTO but ignore the IDENTITY property?
I have a table with identity column say:
create table with_id (
id int identity(1,1),
val varchar(30)
);
It's well known, that this
select * into copy_from_with_id_1 from with_id;
results in ...
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 ...
- 482
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