Questions tagged [auto-increment]

Numerical sequence generation for automatic key creation

Filter by
Sorted by
Tagged with
1 vote
1 answer
17 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 ...
user avatar
  • 13
0 votes
0 answers
25 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 ...
user avatar
0 votes
1 answer
39 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, `...
user avatar
0 votes
3 answers
133 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, ...
user avatar
2 votes
5 answers
232 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 ...
user avatar
  • 489
1 vote
2 answers
99 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 ...
user avatar
-2 votes
1 answer
28 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 ...
user avatar
1 vote
2 answers
278 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 ...
user avatar
  • 121
0 votes
1 answer
140 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 ...
user avatar
  • 477
0 votes
1 answer
58 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 ...
user avatar
3 votes
2 answers
111 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 ...
user avatar
  • 133
2 votes
3 answers
48 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 ...
user avatar
  • 23
0 votes
0 answers
45 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 ...
user avatar
0 votes
1 answer
28 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 ...
user avatar
  • 373
2 votes
3 answers
1k 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 ...
user avatar
0 votes
3 answers
2k 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 ...
user avatar
  • 477
0 votes
0 answers
54 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 ...
user avatar
-1 votes
1 answer
21 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 ...
user avatar
1 vote
2 answers
5k 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 -------------+----------------------...
user avatar
0 votes
1 answer
30 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, ...
user avatar
  • 163
0 votes
1 answer
33 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; ...
user avatar
0 votes
2 answers
30 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 | +-----------------+-------------+---------------+ | ...
user avatar
  • 123
0 votes
1 answer
256 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 ...
user avatar
0 votes
1 answer
1k 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 ...
user avatar
  • 41
0 votes
1 answer
54 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 ...
user avatar
  • 123
0 votes
1 answer
90 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 ...
user avatar
0 votes
2 answers
107 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 ...
user avatar
  • 3
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 ...
user avatar
1 vote
1 answer
949 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 ...
user avatar
3 votes
2 answers
378 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 * ...
user avatar
0 votes
1 answer
619 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) ...
user avatar
  • 1,768
0 votes
0 answers
16 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 ...
user avatar
  • 139
0 votes
1 answer
314 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 ...
user avatar
0 votes
2 answers
103 views

MSSQL: Auto-numbering rows in commit order

General setup I have an event table with an ID field and some data. Multiple producer threads should be inserting events to this table at the same time. Each batch of inserts is done in a separate ...
user avatar
  • 103
0 votes
0 answers
21 views

Auto Increment Field

I am trying to implement a simple table in MS - ACCESS where im using two PK's for say a WEATHER table. PK LOCATION PK ID DATE TEMPERATURE I wish to increment the id based on the location, ...
user avatar
0 votes
1 answer
194 views

Reached maximum possible value for auto_increment function set on column

I'm using a MySQL database. I have set an auto_increment function on "ID" column (which is also set as Primary key). What will happen when it reaches its maximum possible value, according to the data ...
user avatar
  • 111
0 votes
1 answer
730 views

In case of inserted a fixed value into autoincrement how to automatically skip into the next sequence value without causing insert to have an error?

In my postgresql 9.2 database I created the following table: CREATE TABLE dummy(id SERIAL PRIMARY KEY,text TEXT); And I inserted a record via: INSERT INTO dummy(text) VALUES ("Hello Word"); Then I ...
user avatar
0 votes
1 answer
48 views

How to AUTOINCREMENT against another column rather than the whole table in sqlite?

I'm using a table to track messages sent in several different channels. I'd like to be able to track the index of each message on a per-channel basis. 'index' is a forbidden word in SQLite so here I'...
user avatar
0 votes
1 answer
1k views

"INSERT INTO res_partner () VALUES ()" failure (autoincrement in postgres)

This is DDL for the table: CREATE TABLE public.res_partner ( id serial NOT NULL, CONSTRAINT res_partner_pkey PRIMARY KEY (id) ); This is SQL: insert into public.res_partner () values (); ...
user avatar
1 vote
1 answer
765 views

Is there a way to store int in ObjectID? [closed]

I'm trying to migrate MySQL user ids to MongoDB. user ids are incremental values like 1,2,3,4... Is there a way to store int in ObjectID?
user avatar
  • 3,546
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 ...
user avatar
12 votes
4 answers
1k views

Identity column re-seed: when it is necessary?

During one of the last lessons at university (I'm a student), lecturer asked us to develop a database (MySQL Server if it matters) and tiny client app that would consume the database as data source. ...
user avatar
  • 223
1 vote
1 answer
1k views

Huge gap in autoincrement primary key

I Have normal InnoDB table with autoincremental primary key. There Is about 100 thousands records. Accidentally was saved record with ID 10 million. Other record continue in this row. Is any possible ...
user avatar
  • 33
1 vote
1 answer
840 views

reseed auto_increment to specific number?

Is it possible to set the identity column of a table to start from a specific value? I'm converting an ETL stage from mysql to a sql server database where a staging database loads records with a ...
user avatar
  • 143
0 votes
1 answer
137 views

Do I need an auto-generated ID in addition to a unique field?

I have a table that represents a list of currencies. Here is a simplified version of it: CREATE TABLE currencies ( id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY, code ...
user avatar
  • 111
1 vote
0 answers
22 views

How can I auto_increment main and sub category conditionnaly?

So I have a table of items as so |mainCtgry |subCtgry| --------------------- | 1 | 1 | --------------------- | 1 | 2 | --------------------- | 1 | 3 | -------------...
user avatar
  • 113
3 votes
1 answer
244 views

Is it possible to create an alias/shortcut/makro/function for a collection of keywords?

In Oracle 12c, it's possible to use AUTO_INCREMENT functionality by using the keywords/phrase GENERATED ALWAYS AS IDENTITY. This seems cumbersome/clunky to me, given that in other dialects of SQL that ...
user avatar
3 votes
1 answer
983 views

MySQL InnoDB Reset Existing Auto-Increment Records

Using MySQL Workbench, I have a MySQL InnoDB with a few hundred records. The records table has the following format (id is auto_increment and unique); +------+-------+ | id | name | +------+-----...
user avatar
2 votes
1 answer
1k views

HSQLDB get next value for IDENTITY column

I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into. I did some ...
user avatar
1 vote
1 answer
2k views

Manually increase AUTO_INCREMENT column by one

I was wondering if there is a simple way to manually increase the auto increment vaulue of a MySQL column by one. The use case would be to keep the indexes in sync of two tables. In my case they ...
user avatar