MySQL: Conditional Insert

When dealing with databases it is advisable to avoid redundancy of the data as much as possible. So we need a simple way to check, whether some kind of data is already stored in the database and if not INSERT it. When the column we want to check is a Key or Unique attribute of the relation this easy. But it gets a little harder when it is just a simple attribute.

For the following examples we use the table poems with the attributes pid (integer) and poemtext (varchar). Consider the attribute pid as an autoincrement Primary Key. Now we have a poem we want to store, but are unsure whether it is already in the database. We need a way to INSERT with the condition, that there is not yet another tuple with the same poemtext.

With a Key

Let's assume that the poems we'd like to store are very short. So we set the maximal length of poemtext to 100. With this condition we can easily set poemtext to a Unique attribute. With this we can use the following syntax:

INSERT IGNORE INTO `poems` SET `poemtext` = 'Cogito ergo sum.'

When using IGNORE you tell MySQL to first check whether your new poem would cause a duplicate-key violation. If it does the INSERT is ignored. It's as simple as that.

Without a Key

When the length of our poems is getting bigger we have got a problem. In MySQL we can not make an attribute Unique if it is greater than 1000 bytes. With unicode this is only 500 characters! So if the poems are longer than this, we must use another way of conditional inserts.

For our problem we can use the INSERT ... SELECT syntax. With that you can insert data from one table to another. In our case we just select the poem we want to insert and check whether it is already stored in the table with a subquery:

INSERT INTO `poems` (`poemtext`)
SELECT 'Cogito ergo sum.' FROM DUAL WHERE NOT EXISTS (
    SELECT `poemtext` FROM `poems` WHERE `poemtext` = 'Cogito ergo sum.'
)

The table name DUAL is a dummy table provided by MySQL. With this workaround it is also quite easy to do en conditional insert on your table. You now should be able to do that yourself ;)