Insert row only if it doesn't exist already in MySQL

Question

I have a table table with two columns (idA and idB). The table assigns Bs to As, like this:

    A  |  B
    1  |  4
    3  |  2
    3  |  4
    4  |  1
    4  |  3  ...

So one A can have multiple Bs and thus shows up in more than one row. Hence, the table cannot have a primary key and I cannot use a unique column.

Is there a way to insert new rows only if an equal value pairing does not already exist, all in one query?

I tried REPLACE INTO and INSERT IGNORE INTO as mentioned here, but both seem to work for tables with primary keys only.

possible duplicate of [How do I specify unique constraint for multiple columns in MySQL?](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) — Joe Frambach, Sep 15 '13 at 07:08
"If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed." - Unfortunately, I do not have a primary key or unique index... — eevaa, Sep 15 '13 at 07:09

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

ALTER TABLE your_table
ADD PRIMARY KEY(idA, idB)

That will make sure you only have unique records for both columns.

edited Jun 20 '20 at 09:12

Community

answered Sep 15 '13 at 07:08

juergen d

Wow, I didn't know that. Will try it instantly – eevaa Sep 15 '13 at 07:10
1

Be aware that the order of columns in the index matters. If you index on (cola,colb) and query on `where colb=5`, the index will not be used. The index works in one dimension, then the other. – Joe Frambach Sep 15 '13 at 07:19
1

Just to make sure not to confuse the OP: Both columns in the index will be considered when **inserting**. Speeding up the performance on **selects** is a different issue. – juergen d Sep 15 '13 at 07:21

1 Answers1