mysql insert if row does not exist already in a table with NO UNIQUE FIELDS

Question

Looking for a while now already for how to accomplish this.

Seems that all solutions need unique fields with indexes.

Answer 1

Have you already considered the INSERT OR UPDATE syntax of mysql, or do you want the newest insertion to be 'cancelled' instead of reverting to an update ?

Edit: after reflexion, the exact syntax, as shown by afftee, of ON DUPLICATE KEY is only triggered by duplicate value on primary key or at least unique columns.

A possible solution would be to use a BEFORE INSERT trigger similar to this :

CREATE TRIGGER block_dup_insert BEFORE INSERT ON my_table
FOR EACH ROW BEGIN
  -- check condition and stop if it would be duplicated according to your logic
END

If you want to stop the trigger and prevent the actual insert, it seems like you can use this post from Nicolas Lescure found on [mysql doc][1] :

Another way to "STOP ACTION" is to create a table (stop_action) with just a primary key(reason_to_stop). Then you pre-fill this table with some text ('do not do that', 'or that either')=> to stop action, just do an insert into this table (stop_action) with any of the pre-filled value ('do not do that').

[1]: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html mysql create trigger documentation

Answer 2

there is no IF NOT EXISTS syntax in INSERT, but you could make use of the ON DUPLICATE KEY mechanism. Assuming you create a unique index on firstname, lastname, your update might read:

INSERT INTO tb (firstname, lastname) 
VALUES ('Jack', 'Doe') 
ON DUPLICATE KEY UPDATE lastname = lastname;

which renders the insert neutral.