Questions tagged [auto-increment]

Numerical sequence generation for automatic key creation

Filter by
Sorted by
Tagged with
3
votes
2answers
62 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
3answers
41 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
0answers
30 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
1answer
626 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
2answers
164 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
1answer
27 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
2answers
5k 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
3answers
841 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
3answers
338 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
0answers
21 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
1answer
12 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
2answers
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
5answers
72k 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
2answers
1k 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
4answers
3k 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
1answer
25 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, ...
14
votes
2answers
23k 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
2answers
7k 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
1answer
32 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
2answers
29 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
1answer
115 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 ...
-1
votes
1answer
761 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
1answer
51 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
1answer
71 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
2answers
58 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
1answer
682 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 ...
2
votes
2answers
196 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
1answer
478 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
1answer
387 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
1answer
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
0answers
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 ...
0
votes
1answer
144 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 ...
0
votes
2answers
88 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 ...
18
votes
3answers
92k views

How do I specify that a column should be auto-incremented in pgAdmin?

I have started to learn pgAdmin III to manage a PostgreSQL database. But it wasn't an easy to use application. If I create or have created a table with pgAdmin III, how can I add "auto-increment"-...
4
votes
1answer
3k views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT NOT NULL, ...
0
votes
0answers
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, ...
24
votes
3answers
160k views

How can I reset a mysql table auto-increment to 1 in phpMyAdmin?

I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this: ALTER TABLE tablename AUTO_INCREMENT = 1 I am curious if there is a way to do this from within ...
-1
votes
1answer
561 views

Auto increment holes in primary key - any issues?

I have an application that is creating large holes in the auto incremented primary surrogate key to the table as it uses lots of INSERT ... ON DUPLICATE KEY UPDATE queries. I'm looking into changing ...
0
votes
1answer
96 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 ...
2
votes
1answer
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 ...
2
votes
1answer
643 views

Design problem with auto-increment fields

I've made this design similar to my problem, it's not exact but it close enough to understand my problem: Some cellular client frequently send their GPS position and the WLAN name that they have in ...
13
votes
3answers
100k views

Postgres: How to insert row with autoincrement id

There is a Table "context". There is an autoincrement id "context_id". I am using sequence to retrieve the next value. SELECT nextval('context_context_id_seq') The result is: 1, 2, 3,...20.... But ...
0
votes
1answer
261 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 ...
0
votes
1answer
25 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'...
0
votes
1answer
969 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 (); ...
1
vote
1answer
409 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?
4
votes
1answer
2k views

Ansi SQL: Auto Numbered Column

Is there an ANSI standard for auto-numbered columns. Currently, we have a choice of SERIAL, AUTOINCREMENT, AUTO_INCREMENT, IDENTITY() and good old NEXTVAL() among others. I read somewhere that there ...
3
votes
1answer
4k views

Is it safe to rely on auto increments even after restoring data?

I plan of using auto increments as a primary key on one of my table. I need to store data related to each row in an other storage system and I plan to use each primary key id as the key for the data. ...
12
votes
4answers
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. ...
1
vote
1answer
866 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 ...