469,951 Members | 2,625 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,951 developers. It's quick & easy.

Autoincremented id with two primary keys

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,

Alex
Apr 7 '06 #1
0 1049

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by Philip Yale | last post: by
5 posts views Thread by Geoff Cayzer | last post: by
18 posts views Thread by Thomas A. Anderson | last post: by
115 posts views Thread by LurfysMa | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.