CODEWITHSUNDEEP
mysqlinsert
bobobobo
bobobobo

Reputation: 67224

mysql automatically prevent redundant inserts

I have a table like

create table adjacencies(
  relationId int not null primary key auto_increment,
  parent int not null,
  child int not null,
  pathLen int not null
) ;

I'm inserting large amounts of entries like

insert into adjacencies( parent, child, pathLen )
select a, b, c from something where condition ;

So sometimes when I run my insert query, its possible that a (parent,child) relation will already exist and be duplicated. Bad news.

I don't want to create a primary key on ( parent, child ) because that results in a hard fail (query failure).

What I want is a soft fail if there is an attempt to insert a (parent, child) pair that already exists in the table (i.e only that pair gets ignored, and the rest of the query proceeds normally).

What's the best way to do this in MySQL?

(* Wondering why is there a relationId member in this table?)

Upvotes: 1

Views: 237

Answers (2)

cdhowie
cdhowie

Reputation: 168988

Create a composite unique key around the parent and child columns. Then issue a query like this instead:

insert ignore into adjacencies( parent, child, pathLen )
select a, b, c from something where condition ;

The IGNORE clause will turn duplicate key errors into warnings.

Upvotes: 1

zerkms
zerkms

Reputation: 254916

Use

INSERT IGNORE ...

and create unique index/primary key

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

(c) http://dev.mysql.com/doc/refman/5.1/en/insert.html

Upvotes: 1

Related Questions