That's actually not "two primary key", but 1 compound primary key.> 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;
No, MySQL cannot do this automatically.> 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
Don't lock it, just try to insert your new value. It will return an> 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?
if the value already exists, meaning you have to try again.
Database Workbench - development tool for MySQL, and more!
Database development questions? Check the forum!