Questions tagged [auto-increment]
Numerical sequence generation for automatic key creation
0
votes
1answer
27 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 ...
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
48 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 ...
1
vote
1answer
57 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
33 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 ...
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
201 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
47 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 |
+------+-----...
0
votes
0answers
168 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 ...
1
vote
1answer
569 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
29 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
630 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
78 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
64 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 ...
0
votes
0answers
401 views
Auto increment primary key in Access / sql server application
I have an application developed with Microsoft Access and I have a table with Auto increment primary key(id).the last value is id=32767.
In the add form I clicked on a new line, the id must have the ...
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 ...
0
votes
2answers
49 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 ...
2
votes
1answer
163 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 ...
0
votes
1answer
159 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
2k 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 ...
0
votes
1answer
153 views
Mariadb clustering Index design question
Here are the columns of concern for a table describing tasks:
Org_ID
Task_ID
Task_property_columns
The only requirement for Task_ID is that it is unique per every Org_ID, however I would prefer ...
2
votes
2answers
377 views
Custom Sequences with SQL Server
What is the best method to generate a Custom Auto-Generated Sequence with SQL Server?
Here is my requirement:
My application is hosted on 10 independent servers which are not
interconnected.
Each ...
2
votes
1answer
87 views
How does clustering on a field other than autoincrement id affect inserts?
I have a table with an id column which is auto-incremented, and various other informational columns. Rows are inserted into this table very frequently. When the data is read back, the majority of ...
1
vote
1answer
62 views
MySQL: what is AUTO_INCREMENT “free space”?
Quote from openark-kit. The software seems unmaintained (Google Code hosted and last commit from 2013).
oak-show-limits: show AUTO_INCREMENT “free space”.
What is this? How-to see AUTO_INCREMENT “...
0
votes
0answers
519 views
AUTO INCREMENT concurrency issue and ordering
Let's suppose that table t1 has an AUTO INCREMENT primary key id. And let's say that I am starting two transactions at different moments of time, and that transactions are started by the same ...
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
2k views
How to use mySQL to generate auto-increment ID for me
This may be a bad structure. Let me know if it is, it's been a long time since I did any database design.
I have a tblGame in a mySQL database, which has two fields:
ID_Table
ID_Player
In the ...
0
votes
1answer
293 views
How to copy auto-increment id value from one table to another?
I have a database with two tables, A and B.
Table A contains the title of a book and has an auto-increment column, book_id, that I declared as primary key.
In table B, I want to retain the price ...
0
votes
1answer
394 views
Help me understand and resolve MySQL deadlock - “lock mode AUTO-INC waiting”
I am experiencing an issue with MySQL deadlocks and here is what I see in the MySQL error log:
2017-07-24 09:09:29 7fd5235db700InnoDB: transactions deadlock detected, dumping detailed information.
...
0
votes
2answers
3k 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))
0
votes
2answers
115 views
MySQL using AUTO_INCREMENT of secondary coloum of index, and FOREIGN KEY at the same time
I want to create a pair of table with a structure along the lines of:
CREATE TABLE tableA (
ext_id bigint NOT NULL,
local_id int AUTO_INCREMENT NOT NULL, /* <-- Requires MyISAM (InnoDB ...
0
votes
2answers
321 views
How to run insert statement as much times as select statement gives unique values?
DB Firebird.
I have the following statement
SELECT a.ID FROM CLIENTS a
And I want my INSERT statement into another table to use each of this value, something like
INSERT INTO BANKS (ID, NR, MONEY)...
0
votes
1answer
58 views
auto-incrementing key in self-referencing table, performance
My design contains a table where almost every entry has a parent it has to point to, within the same table (differently put, self references).
/* scenario executions */
CREATE TABLE `boteval`...
1
vote
1answer
277 views
One MySQL Table keeps losing auto-increment after restart [duplicate]
We are trying to figure out why this one table keeps losing auto-increment value, thus giving an error message: Failed to read auto-increment value from storage engine.
The auto ID column has type ...
3
votes
1answer
2k views
GUID vs Auto Increment Insert performance
I'm testing the performance of inserts between Auto increment integers vs GUIDs (v4) in tables with a lot of data. Following this blog post, i was expecting seeing a difference. But right now with ...
1
vote
1answer
564 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 ...
1
vote
1answer
42 views
Inserting Missing day of data into table - Need to increment primary key by 618 for each key
So I recently switched servers, but there is a day inbetween the switch of servers where the data didn't get carried over. I have exported the missing data in a CSV.
I know there is 617 rows to be ...
0
votes
1answer
1k 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 ...
1
vote
1answer
1k 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.
...
0
votes
2answers
2k views
How to add a new column with auto increment ,increment factor, min-value and max value in psql?
I have table t1 and I would like to add a new column app_id with the properties given below
auto increment
increment factor(increment by 4)
min-value/initial value(100)
max-value(2000)
I have tried
...
2
votes
2answers
1k views
Best practices for generating unique multi-column keys for weak entities?
How should one generate non-unique, non-natural identifiers for weak entities?
For example, if order_id is the primary key for an order table, and (order_id, item_number) is the primary key for an ...
2
votes
2answers
52 views
Why do database host services increment id by > 1 unit?
I have worked with various databases and notice that an id column (primary key) with auto_increment usually increases by a seemingly random, but static, value like 3, 5, or 10.
So instead of the id ...
0
votes
1answer
392 views
How can I set the auto_increment value of a column in an existing InnoDB table, without incurring downtime?
I have an InnoDB table whose primary key column is defined as follows:
id INT NOT NULL AUTO_INCREMENT
This column's auto_increment value has reached INT_MAX, so all inserts are failing with a ...
4
votes
2answers
213 views
Creating 7 digit codes in database for every new insertion
I am aware that using identity column we can auto-increment the keys from 0, one-by-one. What I want to do is:
Generate codes in SQL server 2014 which will be of 9 digits (operational codes).
First ...
2
votes
1answer
65 views
Is there any performance reason to append an unused AUTO_INCREMENT column in MySQL 5.6?
In my work environment, MySQL has some weird stuff with performance, and one of the other devs wanted me to research this situation before dropping a column that has the AUTO_INCREMENT attribute.
...
1
vote
1answer
260 views
Creating Auto Number field with preceding number of 0's?
Scenario: I have a table with a (int4) field 'pg_recordid' that has newly inserted records auto-increment the new record id using this macro after running 'SERIAL' in the field (in Navicat)
nextval('...
0
votes
1answer
32 views
Creating a single recid stream for multiple tables
I'd like to have a single reservoir of RECIDs to apply across multiple tables.
Obviously I can do that by creating a table with a single auto-increment field and inserting every time I need a new ...
0
votes
2answers
3k 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 ...
1
vote
2answers
171 views
What are the Pros and Cons of assigning value for ids from an offset? (Ex: 100,200,1000)
Pre-dominantly, everybody uses the int data-type or it's variants tinyint,smallint and mediumint to generate a unique value for the id field of their database table(s).
I have seen so many questions, ...
0
votes
1answer
198 views
mysql set auto increment to 1 with foreign key
I want to set auto increment to 1 by below command, but it doesn't works:
ALTER TABLE tablename AUTO_INCREMENT = 1
I searched and found out that I should use TRUNCATE TABLE. But this command fails ...
