CODEWITHSUNDEEP
mysqlinsertunique
Björn Marschollek
Björn Marschollek

Reputation: 10009

Insert uniquely into MySQL table without unique keys

I would like to insert data into one of my MySQL tables uniquely. That is, if the very same entry (all columns contain the same value) already exists in the table, the insert operation should be dismissed. This can be easily be done by defining unique keys and handle the upcoming error, but I cannot alter the table structure.

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

I would like to perform the check and the INSERT operation in one SQL command. Can anyone point me in the right direction?

Upvotes: 4

Views: 2388

Answers (3)

Abhay
Abhay

Reputation: 6645

Let us assume that you have 5 columns in your table - col1, col2, col3, col4, col5. And assume that the data corresponding to these columns that you are trying to insert is in variables - $col1, $col2, $col3, $col4, $col5 (I'm assuming PHP as your language but please modify the variables format as per your nomenclature).

So your insert might look like:

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 might 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.

Upvotes: 5

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26749

Without unique key the only way to ensure unique rows is not allowing any write-concurrency on the table (either with locking them or using the serialisable isolation level)

Upvotes: 0

Ovais Khatri
Ovais Khatri

Reputation: 3211

You can use table lock with the query and until your query has finished processing, no other process will modify it.

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

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

Upvotes: 0

Related Questions