I've got a table and I have a text field with a URL again, I don't want any rows with duplicate URL's but I'd rather not do a check before inserting, is there no way to make a text field unique or could you suggest another field type to use?
A:
You could try using another field that contains a hash of the URL field.
Joe Philllips
2009-04-20 19:32:44
+6
A:
If your text field in the database is set to unique or a primary key or however you have it set up, than an insert statement will fail upon trying to input a duplicate. That way all you should have to do is properly handle the failed insert and there you have it.
As pointed out elsewhere, you can't have an actual TEXT field be unique, but if you are using a varchar field it is doable. This may not be important at all (may just be a miscommunication), because if you are storing URL's in a TEXT field, you have other problems to worry about as well.
TheTXI
2009-04-20 19:33:31
i can't seem to add a max length to the field which is required to add the unique key, any ideas?
zuk1
2009-04-20 19:40:56
Change your field from text to varchar and give it an appropriate length
TheTXI
2009-04-20 19:43:07
Ok but I was just worried I'd try and insert URL's over 250 in length, but that is highly unlikely.Wht are the other problems you mentioned which come from storing URL's in text fields?
zuk1
2009-04-21 15:36:50
zuk1: I do not believe that 250 is the maximum size of a varchar field. You should be able to set that varchar field to be a larger number (say 500, 750, whatever) that way you know you'll have enough size for the URL. Another problem with TEXT fields is that it is not a very good field to store things if you are going to do string searches (such as finding all URL's that end in ".com")
TheTXI
2009-04-21 15:42:48
A:
It seems you can't make text fields unique, atleast with InnoDB. You could use a varchar field with a length of 767 (this appears to be the max). Then alter the same field to make it unique.
Mike B
2009-04-20 19:36:11
A:
The correct way, is to Check whether the url exists in the table, if yes then insert, else, donot insert.
As a best practice, Exception Handling should be used where it actually requires, it should not be used as a replacement of a normal functions.
Smart Pandian
2009-04-20 20:46:27