SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 07:33 AM
| ||||
| ||||
 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      |
|
02-28-2008, 07:33 AM
| |||
| |||
| Re: Autoincremented id with two primary keys 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 |
|
02-28-2008, 07:33 AM
| ||||
| ||||
| Re: Autoincremented id with two primary keys 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 |
Linear Mode
