0
votes
0answers
36 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 ...
1
vote
2answers
1k 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
152 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 ...
1
vote
2answers
995 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
52 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) ...
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 ...
1
vote
1answer
176 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
3k 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 ...
7
votes
1answer
4k 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 ...
1
vote
1answer
639 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 ...
1
vote
1answer
511 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 ...
5
votes
2answers
1k 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 ...
1
vote
2answers
478 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 ...
4
votes
4answers
8k 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 ...