Questions tagged [auto-increment]

Numerical sequence generation for automatic key creation

Filter by
Sorted by
Tagged with
2 votes
2 answers
29 views

For each row create a linked row in another table with auto-generated ID

In Postgres, I have an existing table: things thing_id thing_name destination_id 10 thing 10 null 15 thing 15 null For each row in that table, I want to add rows to two new related tables one of ...
1 vote
2 answers
159 views

MySQL suddenly producing out of sequence autoinc numbers

In MySQL (8.0.27-cll-lve) have an invoice table using an autoincrement field, invoice_id, as the PK. A transaction inserts data for a new invoice, uses LAST_INSERT_ID() to get the new invoice_id and ...
0 votes
1 answer
44 views

SQLite: excluded int primary key null value replaced with next auto value

I'm trying to create generic upsert query builder with specific behavior. Table has two unique constraints: primary key itId and column slug: create table it ( itId integer primary key -- an alias ...
0 votes
0 answers
30 views

Laravel > Session Table : How to speed up querying for the database sessions table?

Is there a reason laravel uses a varchar(191) column type for its sessions table id column? +----------------+------------+-------------+------------+---------+---------------+ | id | ...
0 votes
1 answer
47 views

Postgres performance implications of using negative integer space for IDs

The Problem I have a massive table that is about to run out of positive integers for the autoincrementing integer primary key. I'd like to buy myself a few years to fix the underlying architectural ...
0 votes
1 answer
774 views

commit or rollback on a statement based replication, effect on auto increment id

How commit or rollback is safe on statement based replication in terms of auto_increment column? If I execute below queries, shoudn't there be discrepancy between master and slave in auto_increment ...
0 votes
1 answer
32 views

In Oracle SQL 19c, how can I INSERT a new record into a supertype and its related subtypes in one statement?

I'm designing a property management database that will hold - OWNER, CUSTOMER, and STAFF information. As these use many of the same attributes, I intend to create tables for PERSON, ADDRESS and ...
3 votes
3 answers
14k views

does the mere existence of a primary key increase query speed for large data sets?

So, I have a table with about 2.4M rows and ~30 columns. There is no unique column within the data. The data is transaction logs from stores where each transaction number is only unique on a per store ...
1 vote
1 answer
56 views

Greenplum add a column to table and insert value to it in incremental order

I have a table st.student like following, which has one column. STUDENT_ID 100001 100002 100003 100004 100005 100006 I need to insert a column to this and values in it should be incremental ...
9 votes
2 answers
15k views

Randomizing the Auto Increment in Mysql

We have a database, where we insert data into table whenever we receive an order from our services. We use auto_increment to generate the ID and assign the order with the same. This is usually same ...
0 votes
2 answers
166 views

Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT?

I have a complex primary key, I wonder if what I write in the title is possible: I have a look up table with 4 fields (they should compose the primary key of the table): 1) an INT column; 2) ...
0 votes
1 answer
275 views

Mysql Duplicate entry error during new column addition on production database

I have a MySQL table that has 95929357 records, below is the table schema for the same CREATE TABLE `Friends` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `createdTime` datetime DEFAULT NULL, `...
0 votes
3 answers
922 views

Add an auto increment column by an order condition

I have a big table without auto increment id column. I wanna add this column, but I want to add in order of a column defined as date. What should be the most practical method for this? For example, ...
2 votes
5 answers
405 views

Is a unique index required for a sequential (autoincrementing) id?

If a sequential AUTO_INCREMENT column is not also the primary key of a MySQL table, does it have to have its own UNIQUE index to ensure long-term data integrity? I'm working on reindexing a particular ...
-2 votes
1 answer
43 views

What PostgreSQL query will return unused SEQUENCE #'s?

I have enabled a UNIQUE key so PostgreSQL would act as a proof reader for duplicate entries as a bunch of INSERT INTO statements. Now I need a list of the unused SEQUENCE numbers so I populate these ...
1 vote
2 answers
904 views

PostgreSQL: INSERT in a sequence and increment all values above the INSERT (make "room" for the INSERT) [duplicate]

I have a table that requires storing my item in a specific position, and the user can "move" the items' positions to "make room" for the new item. Here is what the table looks ...
18 votes
1 answer
100k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
0 votes
1 answer
645 views

How to add a serial ID to a table?

I have a table has more than 1000000 rows which was imported from CSV file. The table did not have a primary key, I want to add one with incremented bigint to the table. This is my table DDL: CREATE ...
0 votes
1 answer
93 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 ...
3 votes
2 answers
188 views

Apply nth occurrence number on each distinct value in a column

I have a table with a column of values where each value occurs a variable number of times (i.e., one value may occur 1 time, and another value may occur 3 times). I need to add a column that ...
2 votes
3 answers
58 views

Preserve Autoincrement ID

I am working on an e-commerce webapp and here's what I am thinking about. To make it simple in this question, the user table contains two columns:id (int,PK,NN,AI),name(varchar(45),NN). I want the ...
0 votes
0 answers
62 views

MySQL: Migrate MyISAM table with multicolumn primary index with autoincrement to InnoDB

I have a large (10M rows) MyISAM table with a primary index on 2 different columns where one of them is auto_increment. How can I migrate it to InnoDB in MySQL 5.7? The table is something like: CREATE ...
0 votes
2 answers
188 views

Unique code based on category - a sort of auto_increment

I have to create a unique code to every user registering. The thing is that the unique code needs to be generated based on which city the user is from. So the tables I have are: CREATE TABLE `...
0 votes
1 answer
33 views

Mimic AUTO_INCREMENT in MySQL

I've the following table: create table devies( id int not null, name varchar not null, groupId int not null, primary key(id), unique ux_name(name,groupId)); I get the data to this table and load it ...
2 votes
2 answers
6k views

How to have one Auto Incremented id for two separate tables in mysql?

I have 2 different tables, each having its own id column. I need that if one table is using a specific id, the other table will not use it. This is needed because the object in both tables will later ...
0 votes
3 answers
3k views

ERROR: currval of sequence "rss_sub_source_id_seq" is not yet defined in this session

When I used this query to see the current primary key seed of my PostgreSQL 13 table: select CURRVAL(pg_get_serial_sequence('rss_sub_source', 'id')) it told me: SQL Error [55000]: ERROR: currval of ...
2 votes
3 answers
2k views

Export and import a table without a specific column

I'm trying to export a table with an Auto Increment rowid column and then import it in another server. Considering that the column is primary key and can't accept duplicate keys if i import rows after ...
0 votes
0 answers
86 views

Mapping auto incremented primary keys of deeply nested objects in PostgreSQL when moving between databases

Situation I have a Postgres database where most of the data belongs to a specific tenant. Most tables have an auto-incremented primary key, and the tables have a deeply nested web of foreign keys ...
-1 votes
1 answer
34 views

Upgrade to 10.3 resulted in deleting configuration from old version

We've recently upgraded MariaDB from 10.1 to 10.3 from Cpanel. After the upgrade we started to having issues in the INSERT clauses where we use INSERT into table values ('','one') where the first ...
1 vote
2 answers
4k views

Is it possible to set auto-increment-increment by 10 for only one table?

Is it possible to add auto-increment-increment by 10 for only one table in mysql? I have a testing db that has a lot of tables that do not abide by the auto-increment-increment of 10. I won't want ...
16 votes
5 answers
76k 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'...
1 vote
2 answers
8k views

Add auto increment to already existing primary key column PostgreSQL

I have a database schema of the following table: database=# \d person Table "public.person" Column | Type | Modifiers -------------+----------------------...
3 votes
4 answers
4k views

Primary key with randomly varying increments (so it cannot be guessed easily)

I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100... then the auto generated keys would be something like below: - ...
0 votes
1 answer
36 views

Normalization using DENSE_RANK and LAST_INSERT_ID

db-fiddle I have created a table called orders (orderId, customersId) and a table called orderDetails (orderId, productId). My goal is to denormalize a table called temp which is like scanId, ...
16 votes
2 answers
28k views

Why does auto increment jumps by more than the number of rows inserted?

I am very perturbed by this weird behaviour I am seeing in the auto_increment value recorded in the bidID of a Bids table after performing bulk insertion using a stored procedure: INSERT INTO Bids (...
5 votes
2 answers
8k views

MySQL auto increment column increases after insertion error occurs

I was doing some insertion tests in my database and I realized that when the INSERT statement fails, due error 1452, causes the auto_increment value increase by 1. Even though, there wasn't any row ...
0 votes
1 answer
39 views

Am I breaking something if I set an autoincrement PK to 0?

I have this table CREATE TABLE `test_table` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name_UNIQUE` (`name`) ) ENGINE=InnoDB; ...
0 votes
2 answers
32 views

Transfer rows into a new table, forcing to generate unique primary keys till its insertable

Lets say we have the following scenario... +-----------------+-------------+---------------+ | Table | PK | Additional | +-----------------+-------------+---------------+ | ...
0 votes
1 answer
362 views

How to select rows after a certain row using auto increment temporary columns

I have a table that has ID varchar(255) as PK. I have managed to create an auto increment temporary column rowNumber to use with a SELECT, as follows: SELECT (@cnt := @cnt + 1) AS rowNumber, ID ...
0 votes
1 answer
2k views

why does Postgres raise a not-null constraint error on a serial column?

something strange is happening to my database. my understanding is that the SERIAL datatype is the Postgres equivalent of MySQL's auto-increment and that omitting it from an INSERT clause will ...
0 votes
1 answer
66 views

How to automatically increment several Integer columns based on alphabetically ordered text columns?

Hello and thank you very much in advance for taking the time to look at my question. My question is as followed, I have a large dataset in PostgreSQL, recorded in a similar fashion as it is written in ...
0 votes
1 answer
111 views

MySQL id not saving in order (Laravel in production) [closed]

I'm really confused about this.. So, I created a Laravel app and hosted it on Heroku. I'm using ClearDB extension to be able to use MySQL. Problem is: when I save a new User on my DB, it is not being ...
0 votes
2 answers
119 views

Issue with Auto increment in MYSQL

Current Structure in MYSQL: CREATE TABLE `JOB_ONPREM` ( `JOB_ID` bigint(20) NOT NULL AUTO_INCREMENT, `JOB_NAME` varchar(255) NOT NULL DEFAULT '', `SANDBOX` varchar(100) NOT NULL ...
0 votes
1 answer
1k views

MS SQL Server: Import Flat File with auto-increment ID

So I've downloaded a sample database. It originally didn't have an ID column so I inserted one in through Excel. Anyways, I went through DatabaseName > Tasks > Import Flat File and the usual ...
3 votes
2 answers
520 views

Hash IDs (such as base64 YouTube video Ids): store as int/bigint, or as string, and why?

I want to generate "unguessable" IDs, rather than /record/1, /record/2, /record/3 etc. YouTube's style of using base 64 allows for 16.7 million IDs to be stored in just 4 characters (64 * 64 * 64 * ...
1 vote
1 answer
1k views

MySQL Store Procedure - Setting AUTO_INCREMENT value

I am in trouble coding a store procedure as shown below CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12)) BEGIN DECLARE `_rollback` BOOL DEFAULT 0; DECLARE ...
0 votes
1 answer
845 views

How to set current sequence while converting primary key serial(integer) to bigserial(bigint)

The table already has few thousands of rows and I want to convert the primary key from Integer to BigInteger. How to change the sequence for biginteger? --Create new temp sequence (sequence-name1) ...
1 vote
1 answer
2k views

Map auto increment ids on multi-row insert

I have the following table with an AUTO_INCREMENT column. CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); Usually I load multiple ...
0 votes
0 answers
18 views

PostgreSQL: serial is set to 1, despite of some rows already being inserted in the table

I was getting duplicate primary key error while inserting a row in a table, despite of the primary key being a serial and me not passing any predefined value for it. So I went down to see what was ...
0 votes
1 answer
485 views

Does auto increment on Primary Key prevent concurent inserts? [duplicate]

I have a Sql Server logging table, that continuously is being inserted into, I want to revise the design of this table. Currently, we are using a GUID as the PK, because the software can generate ...