MySQL Insert Where Record Does Not Already Exist In PDO

Question

Im using PDO (PHP Data Objects) for database interactions from PHP. This question concerns three database tables: two are just standard tables with primary keys, and the other is a table to describe relations between those other two tables. As such it has no primary key, but 2 foreign key columns (table_1_id and table_2_id). Neither can be unique as there is a many to many relationship between records in the other two tables.

To determine if a relationship should be added, I need to insert a record only if there is not already a row in the relational table which matches both table_1_id AND table_2_id.

Ideally my insert would look like this:

INSERT INTO table_3 (table_1_id, table_2_id) VALUES (:id1, :id2)
WHERE NOT EXISTS (
    SELECT 1 FROM table_3 WHERE table_1_id = :id1 AND table_2_id = :id2
)

However, I know this is not valid SQL. I have looked around a bit and can't determine the best way to do this.

Thanks for your help

Just make sure you got the right database engine otherwise the following answers won't work. FWIR — Class, Jan 03 '14 at 03:37

score 0 · Answer 1 · answered Jan 03 '14 at 03:32

0

Try adding a combined primary key

ALTER TABLE table_3 ADD PRIMARY KEY(`table_1_id`,`table_2_id`);

answered Jan 03 '14 at 03:32

php_nub_qq

15,199
21
74
144

score 0 · Answer 2 · edited May 23 '17 at 10:25

The relation you're talking about is called "Many to Many".

MySql let's you specify more than one column as a unique index, that is, a column combined unique index.

Please see this question in SO that has detailed information about the subject.

This prevents you from creating duplicate roles, which is want you want to accomplish.

As for the PHP code, PDO throws an error when a sql statement fails, so you can use try/catch

score 0 · Accepted Answer · edited Jan 03 '14 at 03:59

First of all you better define a composite PK (table_1_id, table_2_id) explicitly in table_3

CREATE TABLE table3
(
  table_1_id INT NOT NULL, 
  table_2_id INT NOT NULL,
  PRIMARY KEY (table_1_id, table_2_id),
  FOREIGN KEY table_1_id REFERENCES table_1 (table_1_id),
  FOREIGN KEY table_2_id REFERENCES table_2 (table_2_id)
)

Secondly you can then use IGNORE clause instead of a sub-query

INSERT IGNORE INTO table_3 (table_1_id, table_2_id) VALUES (:id1, :id2)

Here is SQLFiddle demo

If you were for some reason wanted to stick with a sub-query version then your INSERT statement should've looked something like this

INSERT IGNORE INTO table_3 (table_1_id, table_2_id) 
SELECT ?, ?
  FROM dual
 WHERE NOT EXISTS
(
  SELECT *
    FROM table_3
   WHERE table_1_id = ?
     AND table_2_id = ?
);

Here is SQLFiddle demo

Thanks to all who answered! I didn't know about composite keys. I have learnt something very useful! I will mark your answer as correct as you have provided a little more explanation and the SQL Fiddle. Thanks again! — Ben Guest, Jan 03 '14 at 03:40

score 0 · Answer 4 · answered Jan 03 '14 at 03:40

One option would be to define a unique constraint (or primary key) on the combination of the two columns. That's the normative pattern. But that won't allow multiple rows with the same combination of values.

So, assuming you do want to allow "duplicate" rows, but it's just this one INSERT statement you want to not insert a duplicate row, then use a SELECT in place of the VALUES keyword. Basically, write a SELECT statement that conditionally returns the values.

Something like this might work for you:

INSERT INTO table_3 (table_1_id, table_2_id) 
SELECT v.id1, v.id2 
  FROM ( SELECT :id1 AS table_1_id, :id2 AS table_2_id ) v
  LEFT
  JOIN table_3 d
    ON d.table_1_id = v.table_1_id
   AND d.table_2_id = v.table_2_id
 WHERE d.table_1_id IS NULL

MySQL Insert Where Record Does Not Already Exist In PDO

4 Answers4