On Fri, 07 Apr 2006 13:18:52 +0200, Alex wrote:
> 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?

I don't think so, but if there's no ESSENTIAL reason for id to be
duplicated between site_id, then you just slap an autoincriment on that
and not worry about a compount primary key, just use id all by itself.
Most of the tables I use that do need foriegn key relationships are
primary-keyed by an autoincrementing int `ref_id` field that's the first
in the table. I then do all the actual relating via user keys generated
by the application, with appropriate supporting indexes. (There are
cases when this is not the best approach, however. It's been a REALLY
long time since I've had to do something other though, so I don't
remeber what those case are.)

--
26. No matter how attractive certain members of the rebellion are, there is
probably someone just as attractive who is not desperate to kill me.
Therefore, I will think twice before ordering a prisoner sent to my
bedchamber. --Peter Anspach's list of things to do as an Evil Overlord