0

I have a website connected to a database. In one of its tables, one entity attribute that is not the primary key needs to be unique in that table.

Currently, I am querying the database before inserting a value into that column to check, if the value already exists. If it does, the value gets altered by my script and the same procedure starts again until no result gets back, which means it doesn't exist yet in the database.

While this works, I feel it's a great performance hog – even when the value is unique, the database needs to queried at least two times: One time for checking & one time for writing.

To improve performance & to make my (possible buggy/unnecessary) code obsolete, I have the idea to mark the column as Unique Key & to use a try/catch block for the writing/error handling process. That way, the database engine needs to handle the uniqueness, which seems a bit more reasonable than my query-write procedure.

Is this a good idea or are Unique Keys not made for this behavior? What is the typical use case of a Unique Key in a SQL database?

inetphantom
  • 2,498
  • 4
  • 38
  • 61
Sven
  • 12,997
  • 27
  • 90
  • 148
  • Yes, this is a good idea. A typical use case is as you described. – Jay Blanchard Apr 21 '15 at 17:57
  • Unique key is a way to combine multiple columns as one. If it's only one column, use unique index instead. – avk Apr 21 '15 at 18:01
  • I don't necessary see it as a waste to keep some type of structure with two queries. If the first query (or subquery), for example, retrieves a MAX(column) and then you use that to add 1. In that case, you wouldn't ever have more than 2 queries. I guess it depends on what the unique column represents: an integer, random string, or what have you. – Devon Bessemer Apr 21 '15 at 18:10

1 Answers1

1
INSERT INTO table (uniquerow) VALUES(1) ON DUPLICATE KEY UPDATE uniquerow = 1;

With this statement, you can insert if it is unique and update if the key allready exists.

With unique constraints you can check a tuple of values not to be there multiple times, without being a primary key.

inetphantom
  • 2,498
  • 4
  • 38
  • 61
  • 2
    `INSERT INTO table (uniquerow) VALUES(1) ON DUPLICATE KEY UPDATE uniquerow = VALUES(uniquerow);` --> This will update the value to.... itself... That's the best we can do. Your suggestion is quite close, but is missing the last bit. (Doc: https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_values) – Ismael Miguel Apr 21 '15 at 18:02
  • I've never heard of such thing. And I went on Google but Google didn't found it. Only `ON DUPLICATE KEY UPDATE`. – Ismael Miguel Apr 21 '15 at 18:07
  • another possibility is to use UPDATE IGNORE. there is allready a qestion about which is better here http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update @IsmaelMiguel – inetphantom Apr 21 '15 at 18:08
  • For this case, I guess it is the best choice. I came across that link, but I didn't gave much importance. I under-estimated it. – Ismael Miguel Apr 21 '15 at 18:21