SELECT and INSERT if no row is returned

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=gcdmg-mysql-2@m.gmane.org

Re: SELECT and INSERT if no row is returned

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 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
> =A0seq_id int auto_increment ) ENGINE=3DINNODB
>
> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohnny@pixela=
ted.net
>
>

--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: SELECT and INSERT if no row is returned

--0-48086854-1269437195=:12079
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

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 p=
roblem with one statement.

Rodrigo Ferreira

--- On Wed, 3/24/10, Johnny Withers wrote:

From: Johnny Withers
Subject: Re: SELECT and INSERT if no row is returned
To: "Kyong Kim"
Cc: "mysql"
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=0A time.

JW

On Tuesday, March 23, 2010, Kyong Kim 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
>=A0 =A0seq_id int auto_increment ) ENGINE=3DINNODB
>
> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohnny@pixela=
ted.net
>
>

--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?unsub=3Drodrigof_silva@=
yahoo.com

=0A
--0-48086854-1269437195=:12079--

Re: SELECT and INSERT if no row is returned

--00504502cc541b0f4504828e4db2
Content-Type: text/plain; charset=ISO-8859-1

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
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 * wrote:
>
>
> From: Johnny Withers
> Subject: Re: SELECT and INSERT if no row is returned
> To: "Kyong Kim"
> Cc: "mysql"
> 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 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=johnny@pixelated.net
> >
> >
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=rodrigof_silva@yahoo.com
>
>
>

--00504502cc541b0f4504828e4db2--

Re: SELECT and INSERT if no row is returned

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=gcdmg-mysql-2@m.gmane.org