Numerical sequence generation for automatic key creation

learn more… | top users | synonyms

4
votes
1answer
150 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
277 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
6k 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 ...
3
votes
1answer
552 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 ...
4
votes
2answers
3k 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
656 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
71 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
754 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
12k 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
623 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
1k 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
3k 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 ...
19
votes
2answers
24k 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 ...
2
votes
1answer
1k 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. ...
2
votes
1answer
479 views

Design problem with auto-increment fields

I've made this design similar to my problem, it's not exact but it close enough to understand my problem: http://i42.tinypic.com/bhbp5.jpg Some cellular client frequently send their GPS position and ...
3
votes
1answer
995 views

User autoincrement ID for username

I want to automatically create user's, which all have a unique ID. Now here's the problem. If two users are created at exactly the same nanosecond, it may not cause problems. Thus, what i do now is: ...
5
votes
1answer
6k views

MySQL get next unique value without auto increment

I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization. There are multiple 'sites', and each site has work order numbers starting at 100. WorkorderID ...
1
vote
2answers
654 views

serial field issue

Q: I use a serial as a datatype for my primary keys in my tables. I insert a lot of data then truncate these data one after one many times to test the data entry. My question is: Is there any ...
5
votes
2answers
2k views

What Is The Point of a Primary Key?

Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than ...
5
votes
4answers
10k views

MySQL: Why is auto_increment limited to just primary keys?

I know MySQL limits auto_increment columns to primary keys. Why is this? My first thought is that it's a performance restriction, since there probably is some counter table somewhere that must be ...
6
votes
2answers
4k views

MySQL Auto_increment going 2 by 2

I installed MySQL Workbench the other day, accessed my company's database and made myself a table to work with. So far so good. The problem is, I noticed my auto_increment is incrementing 2 by 2. For ...
4
votes
1answer
4k views

How do I make MySQL auto increment in random order?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
2
votes
1answer
12k 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 ...