Hello Alex,

> 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;

That's actually not "two primary key", but 1 compound primary key.

> 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

No, MySQL cannot do this automatically.

> 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?

Don't lock it, just try to insert your new value. It will return an
exception
if the value already exists, meaning you have to try again.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com