Questions tagged [identity]
Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
200 questions
0 votes
1 answer
27 views
Two sequences associated with one identity column
One of my clients has a database in which a single identity column (called id in that table) has two sequences associated with it(!) Both sequences display Sequence for identity column: <schema>....
- 9,520
0 votes
0 answers
39 views
When I hit iiq console in command prompt my Sailpoint says database version doesn't match the system version
In mine it says Application's system version 8.4-104 does not match IdentityIQ database's system version 8.4-87 I tried to update the database its still not working. If it works I can import xml files ...
0 votes
5 answers
203 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
62 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
98 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
2 votes
1 answer
682 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
118 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 ...
- 444
0 votes
1 answer
298 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 ...
- 444
0 votes
1 answer
328 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 ...
- 609
1 vote
1 answer
822 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
2k 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 ...
- 3,207
0 votes
2 answers
208 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, ...
- 59
6 votes
3 answers
3k 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
389 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
2k 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