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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
