SQL: dealing with unique values in the table when UNIQUE key constraint isn't applicable

Question

What you do when you need to maintain a table with unique values when you can't use UNIQUE constraint?

For example, I use MySQL and want to map my urls to ids. So I create a table:

CREATE TABLE url (id INTEGER PRIMARY KEY AUTO_INCREMENT, url VARCHAR(2048));

The problem is that mysql doesn't allow unique field bigger than 1000 bytes. How in general do insert only if not exist in sql atomically?

Answer 1

You could use a not exist condition:

insert  YourTable
        (url)
values  ('blah blah blah')
where   not exists
        (
        select  *
        from    YourTable
        where   url = 'blah blah blah'
        )

Answer 2

In my opinion the best way to handle it is to write a trigger. The trigger is going to check each value in the table to see whether they are equal and if yes, to raise an error. However, I don't think an URL will go beyond 1000 characters but if it does in your case, you should write a trigger to handle the uniqueness.

Answer 3

You could create an extra field which would be the hash of a url e.g. md5, and make that hash field unique. You can certainly be sure that the URL is unique then, and with almost 100% certainty you can insert a new URL if it isn't already there.

It is tempting to create a table lock, however creating a table lock will implicitly commit the transaction you are working on: http://www.databasesandlife.com/mysql-lock-tables-does-an-implicit-commit/

You could create a single-row table e.g. name mutex, type=InnoDB, insert a row into it, and do a select for update on that row to create a lock which is compatible with transactions. It's nasty but that's the way I do table locks in MySQL in my applications :(