Questions tagged [auto-increment]

Numerical sequence generation for automatic key creation

Filter by
Sorted by
Tagged with
0
votes
0answers
18 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, ...
19
votes
3answers
129k 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
499 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
344 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
1answer
25 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 ...
1
vote
1answer
474 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 ...
0
votes
1answer
326 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 ...
2
votes
1answer
610 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 ...
5
votes
3answers
62k 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
26 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
15 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'...
13
votes
4answers
57k 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'...
13
votes
2answers
65k 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"-...
0
votes
1answer
228 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
115 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?
3
votes
1answer
1k 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
2k 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. ...
11
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
383 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 ...
16
votes
3answers
75k views

Reset IDENTITY value

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
3
votes
1answer
4k views

Prevent Primary Key increment when an INSERT statement fails in PostgreSQL

I have a simple table in my PostgreSQL database like this: CREATE TABLE person_type ( id serial NOT NULL, name character(55) NOT NULL, CONSTRAINT person_type_pkey PRIMARY KEY (id), CONSTRAINT ...
1
vote
1answer
261 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 ...
0
votes
1answer
52 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 ...
11
votes
1answer
15k 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 (...
1
vote
0answers
19 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 | -------------...
3
votes
1answer
125 views

Align auto increment index to next thousand

I am using MySQL. When applying a big change to a production database, I would like to change the next auto-increment index of all my tables to align them to the next available thousand. In other ...
0
votes
3answers
860 views

Auto increment a value using cursor in SQLServer

I am developing an employee leave management system and My table structure with data is : +-----------+---------+----------+----------+------------+--------------+ | EmpID | CL | PL | ...
36
votes
2answers
60k views

Is MySql's LAST_INSERT_ID() function guaranteed to be correct?

When I do a single row INSERT to a table that has an AUTO_INCREMENT column I'd like to use the LAST_INSERT_ID() function to return the new AUTO_INCREMENT'ed value stored for that row. As many ...
3
votes
1answer
205 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 ...
2
votes
1answer
334 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 | +------+-----...
1
vote
1answer
1k 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 ...
1
vote
1answer
53 views

MySQL - How to convert an existing int column with duplicates to auto-increment?

After adding some thousand records in a table, I noticed that the "Primary" and "Auto-Increment" properties of the main column are removed. Now instead of auto-incremented numbers, most of them (but ...
0
votes
1answer
1k views

How to set Auto_Increment value based on a variable?

Is it possible to set the auto-increment value in MySQL based on a variable? SELECT 1 + MAX(id) INTO @newAiValue FROM Result_Import_Archive; ALTER TABLE Result_Import AUTO_INCREMENT = @newAiValue; ...
1
vote
1answer
46 views

MariaDB is AutoIncrementing on update

I have a weird issue and i can't find the fault. I have a MariaDB table with 4 rows [id] [clientid] [lastcheck] [Version] The id field is set to AutoIncrement. Everything is working fine with my ...
2
votes
3answers
2k 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: - ...
1
vote
1answer
121 views

Moving data from one database to another database with auto incriment keys

I have table1, table2 and table3, all of them have a primary serial key id (or think of any auto increment type). table3 has two foreign keys(table1 id and table2 id). The questions is, I have 2 ...
0
votes
2answers
107 views

Does using an auto increment of 1 on numeric PK hurts performance?

A teacher of mine said that having an numeric PK with auto increment is a bad practice because it hurts performance. He also said that Oracle database doesn't have an easy way to set up auto ...
2
votes
1answer
8k views

adding autoincrement to id already part of foreign key

I would like to add the autoincrement property for all my table's columns named id in a schema. However, most of them are part of a foreign key constraint. Is there any other way to do it without ...
0
votes
2answers
55 views

MySQL auto Increment id has taken but seems like not written to disk

MySQL is having a very strange issue and we are unable to figure out. So I need expert advice to figure out the serious problem. I have tried to provide all possible details. But you can ask for more ...
1
vote
1answer
1k views

MySQL Replication - 1 Master - 2 Slaves. Inserting works for One, not the other

I am a Python developer, working with 3 databases, configured in Master -> slave,slave style. Each slave has their own application associated with it. I am not too familiar with database ...
0
votes
2answers
4k views

Table shows id (auto-increment) is 0 in all rows

I am using laravel 5.2 to develop my web application. i recently uploaded the web application from localhost to my hosting account. I also imported MySQL tables from phpmyadmin localhost to my ...
6
votes
4answers
39k views

How to use 2 auto increment columns in MySQL phpmyadmin

Is it possible to use 2 auto increment values ? One starting from 0 Another starting from 4000400 Please help me
1
vote
1answer
2k views

MySQL - Set trigger BEFORE INSERT for new ID is safe?

How to create customize primary key in MySQL?, example i have table and the table name is X, I have a table field as ID,Code,Name. I am afraid if I have 1000 users and when they input together will ...
0
votes
2answers
5k views

How to get the next auto-increment id in MySQL?

How to get the next id in mysql to insert it in the table ? INSERT INTO payments (date, item, method, payment_code) VALUES (NOW(), '1 Month', 'paypal', CONCAT("chaminda", id))
7
votes
1answer
9k 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 ...
3
votes
2answers
5k views

Trick for AUTO_INCREMENT on composite keys in InnoDB

In contrast to the MySQL MyISAM-Engine, the InnoDB-Engine can not handle when one column of a composite primary key is declared as AUTO_INCREMENT. For example consider the following table: CREATE ...
2
votes
1answer
367 views

Prevent the collision data because duplicate id from imported database MariaDB

I am currently having a trouble with my Web Application. I made a Web application for a certain company. I made the app using CodeIgniter 3. I built the database using Maria DB. For the id in each ...
3
votes
3answers
27k views

What could cause an auto increment primary key to skip numbers? [duplicate]

I have a table created with the following T-SQL: CREATE TABLE [dbo].[TableName]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, ...
5
votes
1answer
412 views

If MAX(id) != COUNT(id), does that mean rows have been deleted?

The question pretty much says it all. We're talking MySQL here, version 5.1. What I'm wondering is whether this would strictly means rows have been deleted, or if there are other possible scenarios ...
1
vote
2answers
5k views

How to make an existing column autoincrement in Oracle

I have created a table in oracle database which I named USERS and I have created a column USER_ID with type NUMBER which i set as primary key. How can I modify the column USER_ID to make it auto ...