Mysql - second auto_increment col with diff behaviour

Question

Hi guys!

I have a proposals table like this
- ID (auto_increment)
- proposal_id
- client_id

There a way in sql that the proposal_id increments just for each client_id

example:

ID proposal_id client_id
1 1 1
2 1 2
3 2 1
4 3 1
5 2 2
6 3 2

i know i can get the last poposal_id and +1 and i add the new entry... but i dont want to do a sql instruction just to get this value... instead i want to use in a sql!

Tkz Roberto

Answer 1

I think that you could design a complicated enough query to take care of this without any non-sql code, but that's not in the spirit of what you're asking. There is not a way to create the type of field-specific increment that you're asking for as a specification of the table itself.

Answer 2

As I understand you wish to have proposal_id as a sequence in a continuos manner per client_id. Either you should normalize the table to split into per-client-table [tricky and not advisable] to do this or write a SELECT

Answer 3

I think this is what you want if using innodb (recommended) although you can simplify this with myisam

delimiter ;

drop table if exists customer;
create table customer(
 cust_id int unsigned not null auto_increment primary key,
 name varchar(255) unique not null,
 next_proposal_id smallint unsigned not null default 0
)engine = innodb;

insert into customer (name) values ('c1'),('c2'),('c3');

drop table if exists proposal;
create table proposal(
 cust_id int unsigned not null,
 proposal_id smallint unsigned not null,
 proposal_date datetime not null,
 primary key (cust_id, proposal_id) -- composite clustered primary key
)engine=innodb;

delimiter #

create trigger proposal_before_ins_trig before insert on proposal for each row
begin
  declare new_proposal_id smallint unsigned default 0;

  select next_proposal_id+1 into new_proposal_id from customer 
   where cust_id = new.cust_id;

  update customer set next_proposal_id = new_proposal_id where cust_id = new.cust_id;

  set new.proposal_id = new_proposal_id;
  set new.proposal_date = now();
end#

delimiter ;

insert into proposal (cust_id) values (1),(2),(1),(3),(2),(1),(1),(2);

select * from proposal;
select * from customer;

hope it helps :)

i've added the myisam version below for good measure:

drop table if exists customer;
create table customer(
 cust_id int unsigned not null auto_increment primary key,
 name varchar(255) unique not null
)engine = myisam;

insert into customer (name) values ('c1'),('c2'),('c3');

drop table if exists proposal;
create table proposal(
 cust_id int unsigned not null,
 proposal_id smallint unsigned not null auto_increment,
 proposal_date datetime not null,
 primary key (cust_id, proposal_id) -- composite non clustered primary key
)engine=myisam;

insert into proposal (cust_id,proposal_date) values 
(1,now()),(2,now()),(1,now()),(3,now()),(2,now()),(1,now()),(1,now()),(2,now());

select * from customer;
select * from proposal order by cust_id;