Insert unambiguously into a MySQL table without unique keys

Question

I would like to insert data into one of my MySQL tables uniquely. That is, if the same record (all columns contain the same value) already exists in the table, the insert operation must be rejected. This is easy to do by defining unique keys and handling the upcoming error, but I cannot change the structure of the table.

I am sure there is an easy way to catch this even in tables without unique keys. Of course, I can manually check for the existence of such a record using the SELECT in advance, but there may be parallel instances that modify my table in the meantime (between checking with SELECT and the actual INSERT ).

I would like to perform validation and INSERT operation in the same SQL command. Can someone point me in the right direction?

Answer 1

Suppose you have 5 columns in your table - col1, col2, col3, col4, col5. And suppose the data corresponding to these columns that you are trying to insert is in variables - $ col1, $ col2, $ col3, $ col4, $ col5 (I assume that PHP is your language, but please change the format of the variables according to your nomenclature).

So your insert might look like this:

 INSERT INTO `tableA` (`col1`, `col2`, `col3`, `col4`, `col5`) SELECT $col1, $col2, $col3, $col4, $col5 FROM `tableA` WHERE NOT EXISTS (SELECT 1 FROM `tableA` WHERE `col1` = $col1 AND `col2` = $col2 AND `col3` = $col3 AND `col4` = $col4 AND `col5` = $col5); 

Another alternative could be:

 INSERT INTO `tableA` (`col1`, `col2`, `col3`, `col4`, `col5`) SELECT $col1, $col2, $col3, $col4, $col5 FROM `tableA` WHERE `col1` = $col1 AND `col2` = $col2 AND `col3` = $col3 AND `col4` = $col4 AND `col5` = $col5 HAVING COUNT(1) = 0; 

Hope this helps.

Answer 2

You can use table locking with a query and until your query completes processing, no other process will change it.

Check out this link: http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

and then do as you said "such a record using the SELECT statement in advance, but there may be parallel instances that modify my table in the meantime (between checking with SELECT and the actual INSERT).

Answer 3

Without a unique key, the only way to guarantee unique rows does not allow writing concurrency to a table (either with a lock or using a serialization level)