Questions tagged [auto-increment]
Numerical sequence generation for automatic key creation
170
questions
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
3answers
180 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
3answers
504 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 ...
0
votes
0answers
18 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 ...
0
votes
2answers
622 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
-------------+----------------------...
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, ...
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
92 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
649 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
64 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
54 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
589 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 ...
1
vote
1answer
401 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 ...
2
votes
2answers
176 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 * ...
0
votes
1answer
339 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) ...
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
112 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
85 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 ...
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, ...
0
votes
1answer
82 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 ...
0
votes
1answer
167 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
24 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
905 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
335 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?
1
vote
1answer
1k 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 ...
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
826 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
706 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
113 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
20 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
232 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
803 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 |
+------+-----...
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 ...
1
vote
1answer
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 ...
1
vote
1answer
256 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
2k 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;
...
2
votes
1answer
249 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
217 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 ...
1
vote
1answer
51 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
67 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
569 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
617 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 ...
2
votes
2answers
11k 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
736 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
2k 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
160 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
154 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 “...
