Kyong Kim wrote:
> I needed to give greater detail.
> parent_id isn't unique. The table has a composite primary key (parent_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.
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN