Numerical sequence generation for automatic key creation

learn more… | top users | synonyms

2
votes
4answers
156 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 ...
2
votes
1answer
93 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 ...
4
votes
6answers
4k views

using IDENTITY column with increment -1

I need to run a sql server logging db with 2 main tables in 2 seperate datacentres writing to both at the same time. I had the idea of restoring the db to the new datacentre and then reseeding the ...
1
vote
0answers
26 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
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
2answers
79 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 ...
3
votes
2answers
16k 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 ...
2
votes
1answer
82 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 ...
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 ...
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 ...
11
votes
3answers
27k 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
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
96 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 ...
1
vote
1answer
204 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 ...
3
votes
1answer
831 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 ...
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
130 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
110 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
115 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 ...
3
votes
2answers
9k views

MySQL - Autoincrement does not increment sequentally if last row was deleted

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to ...
2
votes
1answer
306 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 ...
2
votes
1answer
204 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 ...
3
votes
1answer
2k views

Getting duplicate primary key for auto increment column

I have a MySQL Innodb table with 'id' as auto increment primary key column. My Scripts are doing following operations: insert ignore into tablename set x='a', y='b' update ignore tablename set ...
7
votes
2answers
27k 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 ...
0
votes
1answer
43 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 ...
0
votes
0answers
477 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(); ...
1
vote
1answer
255 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 ...
1
vote
3answers
7k 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, ...
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
54 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 ...
5
votes
1answer
16k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
1
vote
2answers
176 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 ...
1
vote
2answers
971 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
120 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
324 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
787 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 ...
1
vote
4answers
5k views

Auto increment value needs to be changed inside trigger

I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table. Original structure: id varchar(15), seqno int(11) auto increment Joint PK ...
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, ...
0
votes
2answers
734 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
393 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
188 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
2answers
162 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.