I am storing URLs in a column having varchar(4000) data type. I want to enforce uniqueness constraint on this column but not able to do so because 4000 is way beyond max limit.
Is there any way to enforce uniqueness on such columns ?
Asked
Active
Viewed 87 times
0
-
What is your MySQL version? – Alma Do Nov 14 '13 at 11:17
-
I am using Mysql 5.5.31. – Learn More Nov 14 '13 at 11:20
-
So, just to check, and index on the first 767 bytes will be inadequate? – Strawberry Nov 14 '13 at 11:26
-
These are URLs, usually protocol and host part will repeat. So If I take first part of bytes, it might fail other URls which actially differ in their query part. – Learn More Nov 14 '13 at 11:29
1 Answers
0
The uniqueness of that long fields is not enforcable by use of indices.
The right way is likely to perform a select inside a trigger before insert/update and failing for a value that is already contained in the column.
A practicable workaround at the application level or inside a stored procedure may be to perform a select for the new value followed by an insert (if that value is not already present) inside a transaction.
In any case, part of the column in question shoud be (non-uniquely) indexed to speed the select.
Oktokolo
- 164
- 2
- 14