-1

I don't want the primary key to increment, even if it hits the duplicate entry error!!

TIMEX
  • 259,804
  • 351
  • 777
  • 1,080
  • 1
    Why do you want to prevent the increment? If you are concerned about gaps in the numbers, you are on the wrong track. –  Jan 16 '12 at 15:36
  • TIMEX you have a great rating, please can you give some more background about this issue, otherwise I think you will get a lot of answers that don't meet your needs. – Michael Durrant Jan 16 '12 at 15:42

4 Answers4

1

You could use a table and triggers to implement an oracle like sequence:

drop table if exists users_seq;
create table users_seq
(
next_val int unsigned not null default 0
)
engine=innodb;

drop table if exists users;
create table users
(
user_id int unsigned not null,
username varchar(32) unique not null
)
engine=innodb;

delimiter #

create trigger users_before_ins_trig before insert on users
for each row
begin
 declare v_id int unsigned default 0;

 select next_val + 1 into v_id from users_seq;

 set new.user_id = v_id;

 update users_seq set next_val = v_id;

end#

delimiter ;

insert into users_seq values (0);
insert into users (username) values ('alpha'),('beta');

Query OK, 2 rows affected, 1 warning (0.03 sec)

select * from users_seq;

+----------+
| next_val |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

select * from users;

+---------+----------+
| user_id | username |
+---------+----------+
|       1 | alpha    |
|       2 | beta     |
+---------+----------+
2 rows in set (0.00 sec)

insert into users (username) values ('alpha');

ERROR 1062 (23000): Duplicate entry 'alpha' for key 'username'

select * from users_seq;
+----------+
| next_val |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

select * from users;

+---------+----------+
| user_id | username |
+---------+----------+
|       1 | alpha    |
|       2 | beta     |
+---------+----------+
2 rows in set (0.00 sec)

insert into users (username) values ('gamma');

Query OK, 1 row affected, 1 warning (0.03 sec)

select * from users_seq;

+----------+
| next_val |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

select * from users;

+---------+----------+
| user_id | username |
+---------+----------+
|       1 | alpha    |
|       2 | beta     |
|       3 | gamma    |
+---------+----------+
3 rows in set (0.00 sec)

hope it helps :)

Jon Black
  • 16,223
  • 5
  • 43
  • 42
0

Just came across this "bug". Don't like this behaviour as it disqualifies any usage of UNIQUE columns in my applications.

So my solution is: to check if unique key exists (with SELECT) before adding it and resign from Unique keys at all.

Grzegorz
  • 3,538
  • 4
  • 29
  • 47
0

Answer is contained in your question: simply don't make it auto increment column. Instead figure out correct value yourself and handle duplicate key errors.

Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
0

Just set the field to be PRIMARY without AUTO_INCREMENT then you can control the value of it.

kodkod
  • 16
  • 2