Numerical sequence generation for automatic key creation

learn more… | top users | synonyms

0
votes
1answer
11 views

Incremental column depening on column value

I would like to have a column which has an incremental integer depending on the column value after sorting the table. E.g. after sorting by state: Nr | State | Counter ...
0
votes
0answers
21 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 ...
5
votes
3answers
200 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 ...
1
vote
0answers
27 views

can't change autoincremet variable in mariadb

I have problem with auto_increment in mariadb. I have 4 galera nodes. but my auto_increment_increment variable is 4.. Can I change dynamic? Is there any problem in big systems when I have billion or ...
0
votes
2answers
84 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 ...
0
votes
1answer
47 views

MariaDB is Forgetting Previous Auto Increment IDs

I'm using MariaDB 5.5.39, and it seems like it is forgetting the max id of the auto-incremented primary key if I delete all rows from the table and then too much time passes before another row gets ...
0
votes
0answers
53 views

Mariadb, Galera auto_increment_increment

I have a 3 node Galera cluster configured and as a temporary solution so I can re point a client at a time (for testing) I also have a master-master old style slave from the Galera cluster to the old ...
2
votes
1answer
85 views

autoincrement column issue in MYSQL

We are using an autoincrement column in a temporary table, and doing multiple time inserts. The autogenerated ids are not being generated consecutive (I have attached a sample simple query: 16 is ...
1
vote
0answers
22 views

Table metadata auto_increment value for table modification

if table X is modified(DELETE, INSERT, UPDATE) then table X's metadata value should get incremented. I can write trigger for the same. But inbuilt solution will be better. This way I can stop ...
0
votes
2answers
60 views

When to use auto_increment?

I wonder when to use auto_increment. It is said that it can be used for a unique identifier. But why the unique identifier is needed? Suppose the data of a student has entered in a student table. If ...
2
votes
0answers
38 views

Mysql History Trigger Auto Increment bug or problem

I want to log history of a table, I created a history table and triggers for update,insert and delete operations. I do my test on realtime replication slave. When a record inserted master's main ...
3
votes
0answers
22 views

Is it a good way to use an auto increment integer field as the primary key? [duplicate]

In database design, is it a good way to use an auto-increment integer field as the primary key? For Example, I have a tenant table, tenant_id is unique. In this case, do I need a auto_increment key? ...
3
votes
1answer
105 views

Oracle sequences to MySQL

Migrating from Oracle to MySQL we've found some many incompatibility issues. We've some workarounds for many of them, however we still have one: The Sequences We have this table CREATE TABLE ...
0
votes
0answers
14 views

Auto Increment Sequence Broken After Applying a Key [duplicate]

After SQL Server 2012 Enterprise edition's evaluation period expired, I applied another key using the edition upgrade wizard. After it was successfully done, all Auto-Increment columns started ...
0
votes
2answers
134 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 | ...
1
vote
1answer
113 views

My SQL Server table identity is incrementing way too fast

I have a table with pretty basic design: 1 primary key (id), 1 unique column (filename) (the filename of the file I am tracking) The primary key is an identity column (bigint) (I had to make it ...
1
vote
2answers
110 views

Is there risk importing data with auto increment id #'s that are higher than the current numbers?

I have to import data into a new database. That new database already has a small set of data. I have two large tables I need to import in there. Is there any risk of a higher id getting over ...
0
votes
0answers
13 views

Pat_ID Incrementing by 2 only after close

I am working on an internal project connecting my database to my application. I am using C# to write the program. I am still learning and this is more of a training tool than anything else. When the ...
0
votes
0answers
22 views

How to reorder again' auto increment column' after deleting some rows [duplicate]

I have a MySQL db table, and first column is for id which has auto_increment. I want the first column be reordered after deleting some rows. iIs there any way to do that? id name 1 alex 2 ...
2
votes
2answers
116 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 ...
2
votes
1answer
214 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
44 views

auto_increment incremented on non-affecting queries

When performing the following query on 'sessions': INSERT IGNORE INTO sessions (user_id, archived) VALUES (3, 0) while: CREATE TABLE `sessions` ( `id` bigint(20) unsigned NOT NULL ...
1
vote
1answer
261 views

Reverse auto_increment id existing column in phpMyAdmin

I have imported csv file through phpMyAdmin into one table. I have set auto-increment field, as shown below, but of course it ordered it ascending: +----+------------+------+ | id | date | name ...
0
votes
1answer
33 views

Returning auto-generated primary key after SQL insertion

I am trying to submit data to a database and then have it return the value of the user_id (primary key) created by the insertion, but I can't get it to return the value. I use the same query in ...
0
votes
1answer
40 views

Auto number for big data

I'm working with application that can have millions of data. Just wondering if it is ok to use auto_number as primary key. I have read that bigint, int etc has limit. So for example site is on live ...
0
votes
0answers
56 views

Creating mysql config setting per database

I am trying to setup MySQL so one database auto increments by 2. I am using the auto_increment_increment variable in my.cnf configuration file, but this is being applied to every database. I would ...
1
vote
2answers
180 views

Reset the sequence of a serial PK with dynamic SQL

How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this? My goal is to do ...
0
votes
1answer
1k 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 ...
2
votes
1answer
95 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 ...
1
vote
2answers
1k views

Assigning new auto increment value [duplicate]

I have a table that is created as below. CREATE TABLE [dbo].[Table1]( [ID] [INT] IDENTITY(1,1) NOT NULL, [Title] [VARCHAR](255) NULL, ... Lets say that the latest ID is 100 ( I have 100 ...
0
votes
0answers
123 views

How to use ID field AUTO_INCREMENT sorted by Date field?

Any ideas how I could use ID field AUTO_INCREMENT sorted by Date field? Date field consist of dates and I would like to have to ID (INT NOT NULL AUTO_INCREMENT) field ascending values from oldest to ...
15
votes
8answers
2k views

Reasons for avoiding large ID values

We are working on a web application, not yet accessible to users. My boss noticed that newly created records get an ID of over 10 000, even though we only have under 100 records in the table. She ...
0
votes
1answer
329 views

Prevent Primary Key increment when an INSERT statement fail 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
3answers
811 views

does the mere existence of a primary key increase query speed for large data sets?

So, I have a table with about 2.4M rows and ~30 columns. There is no unique column within the data. The data is transaction logs from stores where each transaction number is only unique on a per store ...
2
votes
1answer
315 views

How mysql (innodb) handles auto increment column

I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can ...
0
votes
0answers
62 views

MySQL on Server side PRIMARY KEY taking automatically random Id

I have problem in my database which i have uploaded in Database Version libmysql 5.1.73 In that i got problem like one PRIMARY_KEY field incrementing random values. inserted id's like 1, 2, 35, 67, ...
1
vote
1answer
205 views

mysql autoincrement counter

I need a way to keep auto-increment counter between mysql service restart. As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
0
votes
2answers
749 views

How to create identity field in MySQL without using auto_increment column?

I am trying to create a dynamic system when a user can create different objects and relate them to another objects. I need is to be able to generate an identity column similar to auto_increment but ...
2
votes
1answer
401 views

Why does Oracle not support an autonumber data type? [closed]

I know that Oracle implements autonumber functionality using sequences and triggers, but why doesn't it have a built-in autonumber data type as do most relational DBMSs? There are two specific parts ...
2
votes
1answer
189 views

Why does MySQL increment the auto_increment value when there's a constraint violation?

I have a table in a MySQL database with one column id, which has auto_increment and the Primary Key. There's also a Unique constraint in this table, on different columns. I noticed that when I try to ...
0
votes
0answers
485 views

Proper usage of last_insert_id() in PHP procedure

I have a problem with performing sql query from php script. The query in sql looks like this: INSERT INTO `table1` VALUES (NULL, 'Blah', 'blah', 'blah', 'blah'); SET @lastID = LAST_INSERT_ID(); ...
0
votes
0answers
137 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 ...
2
votes
1answer
2k views

Auto_increment value needs to be set as S1

Hi I need to set auto increment for a table as S1. I tried by altering the table and also by creating by new table I am aware how to set auto_increment but when i try to set auto increment as S1 i am ...
1
vote
2answers
2k views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
3
votes
1answer
853 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 ...
2
votes
1answer
153 views

Can triggers be used to implement automated polymorphic association?

(Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.) I'm attempting to (re)design a MySQL database for a personal social ...
3
votes
1answer
443 views

Auto-increment ids with multiple inserts in mysql/mariadb

Suppose I have a table with an auto_increment column, and then insert multiple values in a single statement: insert into foo (bar) values ('a'), ('b'), ('c'); From the documentation, ...
0
votes
2answers
163 views

Alter auto-increment of table with 16 million rows

Learned this the hard way. Need to alter a table's auto-increment with 16 million rows. Now, it's taking quite sometime. Any estimate how long it would take? Sorry.
1
vote
2answers
585 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
2k views

ALTER TABLE AUTO_INCREMENT number in a Trigger

I want to have my auto increment column to increment by step of 3 upon insert. Can I do something like: Alter table `mytable` auto_increment = (select max(id) from mytable) + 3; in a Trigger? I ...