MySQL INSERT if not exist (not using primary key)

Question

how can i insert into a row if the pair does not exist?
* NOTE these are not primary keys, my primary KEY is set to auto increment

tried insert ignore but did not work

INSERT IGNORE INTO mytable (`myid`, `theirid`) VALUES ('5', '1')
ON DUPLICATE KEY <DO NOTHING>

table looks like:

CREATE TABLE `mytable` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `myid` bigint(20) NOT NULL,
    `theirid` bigint(20) NOT NULL,
    `activated` tinyint(1) NOT NULL DEFAULT '0',
    `dateStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1$$

Answer 1

I think you are not clear about the on duplicate does.... The Idea to use it is If the field is duplicate it, replace the old one for a new one or just change the Primary Key to make it UNIQUE... You can not do, the on duplicate do nothing because by Default it wont do nothing, it wont insert. Take a look here

mysql> insert into t2 values (20000,'a','a',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (20000,'a','a',0);
ERROR 1062 (23000): Duplicate entry '20000' for key 'PRIMARY'

so it did 'nothing'. If you want to know if the value exists you should use a Select statement before the Insert.

Answer 2

1) Can you add a UNIQUE constraint on (myid, theirid)? If yes, add this constraint and use:

INSERT INTO mytable (myid, theirid) 
  VALUES (5, 1) ;

and ignore the produce warnings (or replace the above with INSERT IGNORE)

2) If you can't add such a constraint (e.g. you sometimes want to allow such duplicates and other times you don't), you can use this:

INSERT INTO mytable (myid, theirid) 
  SELECT 5, 1 
  FROM dual 
  WHERE NOT EXISTS
        ( SELECT *
          FROM mytable
          WHERE myid = 5
            AND theirid = 1
        ) ;