Numerical sequence generation for automatic key creation

learn more… | top users | synonyms

0
votes
0answers
14 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
1answer
18 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
31 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 ...
1
vote
1answer
51 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 ...
1
vote
1answer
50 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
1answer
37 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
2answers
63 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.
0
votes
0answers
42 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
118 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 ...
0
votes
2answers
87 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 ...
1
vote
0answers
42 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
39 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 ...
2
votes
1answer
79 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, ...
1
vote
2answers
90 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: - ...
0
votes
1answer
95 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 ...
0
votes
1answer
120 views

make MariaDB Auto-Increment-Increment dynamic

In a certain replication setup I have created multi-master replication in STAR topology using MariaDB. I have an unknown number of 'client-masters', so I have set the auto_increment_increment to 30 ...
1
vote
1answer
95 views

Import using pgAdmin3 produces ERROR: duplicate key value violates unique constraint

I am importing from the GUI a CSV file into a table. I have created the following table: CREATE TABLE jira_tickets.daqa_rpt_tbl ( daqa_report_id bigint NOT NULL DEFAULT ...
1
vote
2answers
1k views

How are auto_increment keys handled in INSERT (SELECT * FROM…)

I have table1 and table2 in MySQL. Both have a primary auto_increment key id. If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows ...
0
votes
1answer
1k 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
2answers
259 views

MySQL row does not persist, but primary key with auto-increment is incremented

I have a MySQL database that is acting oddly. I insert a new row and observe that for a brief period of time (less than 30 seconds), the row persists as expected in the table. After this time, though, ...
1
vote
2answers
2k views

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

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
0answers
182 views

Converting UUID's from CHAR(32) to BIN(16)

I have a few questions relating to UUID's, but before I go on, let me explain our schema a bit, first, we're on MySQL 5.5 (Percona's version to be exact)... In each applicable table, we use a two ...
3
votes
1answer
3k 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) ...
0
votes
1answer
63 views

autonumber and one-to-many relationship

I have a main table with an autonumber key. I need to introduce a new table, related to the main table in a one-to-many relationship (i.e. a record in the main table could conceivably link to several ...
0
votes
1answer
55 views

Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT?

I have a complex primary key, I wonder if what I write in the title is possible: I have a look up table with 4 fields (they should compose the primary key of the table): 1) an INT column; 2) ...
2
votes
1answer
585 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 ...
0
votes
1answer
99 views

Postgres CYCLE and existing values

I love that SERIALs can wrap around with CYCLE. However, I can't seem to figure out if that there are lingering values, will Postgres skip over them or throw an error. In other words, I have a table ...
0
votes
1answer
96 views

How to add CYCLE to BIGSERIAL in phpPgAdmin

I'm trying to apply CYCLE to a BIGSERIAL in phpPgAdmin. I've seen how to do it with a query, but I'd much rather be able to do it from phpPgAdmin. Can this be done with phpPgAdmin? If so, how? If ...
1
vote
1answer
1k views

Auto Increment Primary ID inserting random values

Today I was asked to look into this problem and I have never seen anything like this before. A table in our MySQL database, with a primary IDs column, which is set to autoincrement int, had some weird ...
7
votes
1answer
5k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select ...
0
votes
1answer
2k views

MySQL auto increment problem with deleting rows / archive table

A hosted server is running "maintenance" each weekend. I am not privy to the details. In a database on this server there is a MyISAM table. This table never holds more than 1000 rows and usually much ...
3
votes
1answer
89 views

How to check with SQL whether a column is an identity column for an Access database?

Knowing how to check whether a column is an identity column for Microsoft SQL Server, I currently still found no solution for doing the same with SQL for a Microsoft Access database. My question: ...
1
vote
1answer
190 views

MySQL auto_increment delivers different primary key in MyISAM and InnoDB

I have a table with auto incremental primary key. If I want to change a row and add new rows I got different primary keys depending on engine MyISAM and InnoDB. The original table is: pk.. ...
0
votes
2answers
4k 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
262 views

Using the auto increment id from one query as the fk of the next insert

I'm not sure if I'm overcomplicating this or whether I can't see the wood for the trees. I have two tables, both have auto incrementing ID's. Table 1: ID - auto field - int other field - varchar ...
2
votes
1answer
1k views

MariaDB, Triggers and last_insert_id

I have a situation where I have three tables: sample, sample_name and run (extra columns removed to be only relevant information). CREATE TABLE `sample` ( `sample_id` int unsigned NOT NULL ...
0
votes
3answers
2k 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 ...
3
votes
2answers
2k views

Auto-increment key and Foreign Key as composite for relational database

I do not have any experience in databases other than what I have from just playing with workbench and trying to understand how I need to setup this database for a project. I have been searching the ...
2
votes
2answers
4k 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 ...
4
votes
1answer
10k 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 ...
2
votes
2answers
434 views

Creating sequence for longer numeric values

I need to create a database model that includes a field to store 20 digit unique values. Which will be used by many clients over an API. The value must be 20 digit long, numeric and unique. Best ...
0
votes
0answers
59 views

Autoincrement by 2

I use MariaDB 5.5. It increases every auto_increment value by 2. So all my ai cols look like 1 3 5 7 9 ... Where can I change this behaviour? Or might this be a bug?
2
votes
1answer
346 views

Why is InnoDB auto_increment column skipping over large blocks of unused values?

We have an active and important InnoDB table with about 8M rows. We just noticed that for several months, the primary key value (a BIGINT) has been jumping by hundreds at a time. I turned on query ...
2
votes
3answers
7k 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
521 views

SQL - double column used from primary key with autoincrement

I am user the following as primary key in the SqlDB: I am doing the Index as Autoincrement, but it is autoincremented in general not per user. Example userid=1,index=1, then insert another for ...
1
vote
1answer
701 views

does last_insert_id() returns correct result when writes from multiple sessions?

I have a table in which there is a combination of 4 columns that will act as a unique key. Instead of using a composite primary key, I want to keep an id column as the primary key. This field is on ...
4
votes
5answers
2k 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 ...
15
votes
2answers
18k 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 SQL ...
3
votes
1answer
1k views

Getting duplicate primary key for auto increment coloumn

I have a MySQL Innodb table with 'id' as auto increment primary key coloumn. My Scripts are doing following operations: insert ignore into tablename set x='a', y='b' update ignore tablename set ...
2
votes
1answer
878 views

MySql - alternate of auto_increment on multiple columns

Since auto_increment on multiple columns is supported in MyISAM engine only and I can't use it because of its drawbacks (table lock, slower writes, no-transacts etc), I am looking for an alternative. ...