I needed to give greater detail. parent_id isn't unique. The table has a composite primary key (parent_id, seq_id). Here's a better schema def
CREATE TABLE sometable ( parent_id INT(10) NOT NULL, seq_id INT(10) AUTO_INCREMENT, child_id INT(10) NULL, PRIMARY KEY(parent_id, seq_id), UNIQUE KEY(child_id) ) ENGINE=INNODB; The requirement is that there can be only 1 parent_id associated with a given child or there can be only one parent_id not associated with a child_id (NULL child_id). I need to avoid a race condition where 2 connections can SELECT and return an empty row and insert rows of the same parent_id not associated with a message_id. It's that .1% of the cases we want to avoid. Kyong On Wed, Mar 24, 2010 at 6:26 AM, Rodrigo Ferreira <rodrigof_si...@yahoo.com>wrote: > First, if you want no duplicate parent_id, make it unique key (as JW > saids). Look at INSERT ... ON DUPLICATE KEY UPDATE, I think this will solve > the problem with one statement. > > Rodrigo Ferreira > > --- On *Wed, 3/24/10, Johnny Withers <joh...@pixelated.net>* wrote: > > > From: Johnny Withers <joh...@pixelated.net> > Subject: Re: SELECT and INSERT if no row is returned > To: "Kyong Kim" <kykim...@gmail.com> > Cc: "mysql" <mysql@lists.mysql.com> > Date: Wednesday, March 24, 2010, 9:32 AM > > Make parent_id a unique key. Doing a select first and inserting if no > result will work 99.9% of the time; however, consider 2 rows being > inserted at the same time. > > JW > > On Tuesday, March 23, 2010, Kyong Kim <kykim...@gmail.com> wrote: > > I need to INSERT a row into a table only if it does not exist to > > insure that there won't be duplicate rows. > > Due to the the usage case and the design of the primary key, > > non-unique id + auto increment, I don't think insert ignore is an > > option. > > > > What would be simplest and cheapest way to make sure that given the > > following schema > > > > create table some_table > > ( parent_id int //non-unique > > seq_id int auto_increment ) ENGINE=INNODB > > > > that no row with the same parent_id can be inserted? > > > > Kyong > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > > > > > -- > ----------------------------- > Johnny Withers > 601.209.4985 > joh...@pixelated.net > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=rodrigof_si...@yahoo.com > > >
