I have a table with a text column that I need to be unique. Because I can't put a unique constraint on the column in MySQL, I want to know if the following approach would guarantee uniqueness in the column:
- Create a varchar column for holding the hash of the unique text column. Put a plain INDEX on it.
- Start transaction
SELECT FOR UPDATEto see if hash exists
a) Use a subsequent
INSERTif hash doesn't exist
b) If hash exists,
SELECTby unique text to see if it exists. If unique text does not exist, do a subsequent
INSERT, otherwise do 'UPDATE'
- End transaction
If I use the above approach for all inserts in that table, can that guarantee uniqueness?