Don Rhummy Don Rhummy - 1 year ago 50
MySQL Question

Is there an efficient way to insert only if there's no row with the same values in two columns (without subquery)?

I know I can do an:

INSERT INTO MyTable (name, num, dateCreated)
VALUES ( "Joe", 31, "2016-05-01 00:00:10" )
SELECT num FROM MyTable WHERE num = 31 AND dateCreated = "2016-05-01 00:00:10"

But this is very slow as it uses a sub-query on every insert. Is there a faster way to do this?

Can i add a unique constraint on multiple columns?

I do NOT want to use the primary key to test for duplicate

Answer Source

You can make unique index which is a combination of your 2 columns. ALTER TABLE students ADD UNIQUE idx_row_unique(first_name,last_name,...);