title: Mysql auto increment alternatives
body: <p>I have the below table.</p>
  
  <pre><code>create table mytable(
  physical_id int auto_increment,
  logical_id int,
  data varchar(20),
  version_start_date datetime,
  version_end_date datetime,
  primary key(physical_id),
  unique key(logical_id,version_start_date, version_end_date)
  );
  </code></pre>
  
  <p>The idea behind the schema is, I want to keep track of modification to 
  every row and find the valid row on any particular date by checking the 
  version_start_date and version_end_date. I want my logical id to be 
  auto_increment, but mysql allows only one id to be auto_increment.</p>
  
  <p>So, I want to set logical_id to physical_id, when creating a new row. I 
  am able to do it using the trigger.</p>
  
  <pre><code>delimiter $$
  create trigger myTrigger before insert on mytable for each row begin set 
  new.logical_id = (select auto_increment from information_schema.tables 
  where table_schema = database() and table_name = 'mytable') ; end$$
  delimiter ;
  </code></pre>
  
  <p>Few other options I checked out are, <a href="http://feedblog.org/2007/06/20/portable-sequence-generation-with-mysql/" rel="nofollow noreferrer">http://feedblog.org/2007/06/20/portable-sequence-generation-with-mysql/</a> and <a href="http://www.redhat.com/docs/en-US/JBoss_Hibernate/3.2.4.sp01.cp03/html/Reference_Guide/Native_SQL-Custom_SQL_for_create_update_and_delete.html" rel="nofollow noreferrer">http://www.redhat.com/docs/en-US/JBoss_Hibernate/3.2.4.sp01.cp03/html/Reference_Guide/Native_SQL-Custom_SQL_for_create_update_and_delete.html</a></p>
  
  <p>The problem with these approaches is, I have to create a new sequence table and keep inserting a record into that table.</p>
  
  <p>Is there a better alternative?</p>
  
  <p>Thank you<br>
  Bala</p>
  
  <pre><code>-- Update
  </code></pre>
  
  <p>I am not sure why @tpdi, why I need parent, when I can just emulate this with below table.</p>
  
  <pre><code>create table logical_id_seq (
   logical_id int auto_increment,
  primary key(logical_id)
  );
  
  create table mytable (
  physical_id int auto_increment,
  logical_id int not null references parent(logical_id),
  data varchar(20),
  version_start_date datetime not null,
  version_end_date datetime not null,
  primary key(physical_id),
  foreign key (logical_id) references logical_id_seq(logical_id),
  unique key (logical_id,version_start_date,version_end_date)
  );
  </code></pre>