Autoincremented id with two primary keys

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Hi all. I have this table:

CREATE TABLE sites_pages (
  id int(6) NOT NULL,
  site_id int(4) NOT NULL,
  name varchar(80) NOT NULL,
  UNIQUE KEY site_id_name (site_id, name),
  PRIMARY KEY (id, site_id)
) TYPE=InnoDB;

I would like to have an auto generated id but for each site. For example:

id  site_id  name
1   1        index
2   1        home
3   1        article
1   2        index
2   2        home
3   2        article

Is it possible to have this done automatically by mysql or I have to lock
the table and do a thing like that:

LOCK TABLE sites_pages;
SELECT MAX(id) AS page_id FROM sites_pages WHERE site_id=1;

take the site_id value and increment by one and then INSERT...


Are there alternatives to lock the entire table?

Thanks in advance,


Site Timeline