1

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?)

Community
  • 1
  • 1
bobobobo
  • 64,917
  • 62
  • 258
  • 363

2 Answers2

1

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

zerkms
  • 249,484
  • 69
  • 436
  • 539
1

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.

cdhowie
  • 158,093
  • 24
  • 286
  • 300