how to design a table in mysql with two auto_increment fields

Question

For a project we do the database design at the moment. We think we should use two auto_increment fields in one table.

table master:

`pid` int(10) NOT NULL auto_increment,
`iid` int(10) NOT NULL auto_increment,
...

To start with a alternate auto_incremet you can use ALTER TABLE tbl AUTO_INCREMENT = 100000; This will work only for the whole table 'tbl'. auto_increment for pid should be 50000000 and auto_increment for iid should be 80000000

We want to avoid splitting it into 3 tables with relations master -> table.pid and master -> table.iid.

altering the table is not working cause /* SQL Error (1075): Incorrect table definition; there can be only one auto column and it must be defined as a key */

Is it possible or what alternative do you recommend?

Could you elaborate on why you need two auto_incrementing fields in the same table? — PatrikAkerstrand, Nov 05 '10 at 09:08
We discussed the topic internal and with our customer. Our customer want to rely on "meaningful" numbers. Explaing the possible risks and investigating the pros and cons, we decied to modify the database design. The idea results from an existing application using Microsoft SQL server where a feature like that is already implemented. — vbd, Nov 05 '10 at 10:01

score 0 · Accepted Answer · answered Nov 05 '10 at 09:10

0

If you cannot use two auto columns I think you must redesign your database. What do you need exactly?

answered Nov 05 '10 at 09:10

MatTheCat

18,071
6
54
69

score 0 · Answer 2 · answered Nov 05 '10 at 09:15

I dont fully understand your question but you can use triggers to maintain key values like the following:

drop table if exists grid;
create table grid
(
grid_id int unsigned not null auto_increment primary key,
name varchar(255) not null,
next_token_id int unsigned not null default 0,
next_node_id int unsigned not null default 0
)
engine = innodb;

drop table if exists grid_token;
create table grid_token
(
grid_id int unsigned not null,
token_id int unsigned not null,
name varchar(255) not null,
primary key (grid_id, token_id) -- note clustered PK order (innodb only)
)
engine = innodb;

drop table if exists grid_node;
create table grid_node
(
grid_id int unsigned not null,
node_id int unsigned not null,
name varchar(255) not null,
primary key (grid_id, node_id) -- note clustered PK order (innodb only)
)
engine = innodb;

-- TRIGGERS

delimiter #

create trigger grid_token_before_ins_trig before insert on grid_token
for each row
begin

declare tid int unsigned default 0;

  select next_token_id + 1 into tid from grid where grid_id = new.grid_id;
  set new.token_id = tid;
  update grid set next_token_id = tid where grid_id = new.grid_id;

end#

create trigger grid_node_before_ins_trig before insert on grid_node
for each row
begin

declare nid int unsigned default 0;

  select next_node_id + 1 into nid from grid where grid_id = new.grid_id;
  set new.node_id = nid;
  update grid set next_node_id = nid where grid_id = new.grid_id;

end#

delimiter ;

-- TEST DATA

insert into grid (name) values ('g1'),('g2'),('g3');

insert into grid_token (grid_id, name) values
(1,'g1 t1'),(1,'g1 t2'),(1,'g1 t3'),
(2,'g2 t1'),
(3,'g3 t1');

insert into grid_node (grid_id, name) values
(1,'g1 n1'),(1,'g1 n2'),
(2,'g2 n1'),
(3,'g3 n1'),(3,'g3 n2');

select * from grid;
select * from grid_token;
select * from grid_node;

how to design a table in mysql with two auto_increment fields

2 Answers2