I don't want the primary key to increment, even if it hits the duplicate entry error!!
Asked
Active
Viewed 1,441 times
-1
-
1Why 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 Answers
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