Questions tagged [identity]
Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
198 questions
0
votes
5
answers
81
views
Log table without primary key
I have a SQL Server log table that includes a column "Id"
This is an identity column, but not a primary key, it is not even indexed.
This would have just been set up from some tutorial for ...
- 1
0
votes
1
answer
42
views
Designing a schema with non-enumerable, unpredictable public ids in MySQL
I'm building a REST API, backed by MySQL. Normally for every table I will have at least one: id UNSIGNED INT NOT NULL PRIMARY KEY AUTO_INCREMENT field as a default, but the issue with this is that it ...
- 151
0
votes
1
answer
65
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 ...
- 73
1
vote
1
answer
248
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 ...
0
votes
1
answer
76
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 ...
- 384
0
votes
1
answer
177
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 ...
- 384
0
votes
1
answer
277
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 ...
- 377
1
vote
1
answer
644
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
2
votes
2
answers
952
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,919
0
votes
2
answers
172
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
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, ...
- 245
0
votes
1
answer
328
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
2
votes
1
answer
1k
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 ...
- 23
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,
...
- 145
-3
votes
2
answers
154
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
183
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 ...
- 775
0
votes
3
answers
142
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,807
1
vote
2
answers
366
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 ...
- 125
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
249
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,533
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
50
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
180
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
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 ...
- 1,127
0
votes
1
answer
780
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,692
0
votes
1
answer
100
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
14
votes
1
answer
25k
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, ...
- 375
4
votes
3
answers
11k
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
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 ...
- 825
1
vote
5
answers
3k
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'...
- 385
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,...
- 385
1
vote
1
answer
251
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
246
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
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 ...
- 123
2
votes
0
answers
134
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
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 ...
- 310
0
votes
2
answers
1k
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)....
- 39.5k
3
votes
2
answers
6k
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.
...
- 23.2k
1
vote
2
answers
2k
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
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 ...
- 11.2k
0
votes
1
answer
820
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
90
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
921
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,335
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
101
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
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.
...
- 1,953
2
votes
2
answers
2k
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