List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:March 24 2010 4:57pm
Subject:Re: SELECT and INSERT if no row is returned
View as plain text  
Kyong Kim wrote:
> 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.
> 

What you are describing is a UNIQUE key based on the combination of 
parent_id and child_id.

ALTER TABLE sometable ADD UNIQUE(parent_id, child_id);

Based on your descriptions, that should fix your duplication problems.

-- 
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
Thread
SELECT and INSERT if no row is returnedKyong Kim24 Mar
  • Re: SELECT and INSERT if no row is returnedJohnny Withers24 Mar
Re: SELECT and INSERT if no row is returnedRodrigo Ferreira24 Mar
  • Re: SELECT and INSERT if no row is returnedKyong Kim24 Mar
    • Re: SELECT and INSERT if no row is returnedShawn Green24 Mar