Enforce unique values for a long varchar field in a MySQL database

Question

I have a database field which is very long (2083 characters) and therefore I can't set the UNIQUE constraint on it in MySQL.

I've tried to modify a stored procedure so I can check if the value being attempted to insert is unique and if not, to stop the insert. But the Stored procedure always errors. I've tried lots of different syntaxes. It's really strange, the stored procedure is fine with just the if statement or the insert but not with both.

Any help gratefully appreciated.

BEGIN
SET @insertedid := 0;
SET @alreadyfound := 0;
SELECT COUNT(lref_id) FROM listing_referrers WHERE lref_referring_url=in_lref_referring_url INTO @alreadyfound;

IF (@alreadyfound = 0) THEN
BEGIN
    INSERT INTO listing_referrers (lref_listing_id,lref_referring_url,lref_createdby_userid,lref_created) VALUES (in_lref_listing_id, in_lref_referring_url, in_lref_createdby_userid, in_lref_created);
    SET @insertedid = last_insert_id();
END
SELECT @insertedid;
END

Answer 1

Idea using a Trigger

I would not use a procedure here, but prefer using a trigger ON INSERT. You may find the my answer at How to prevent using digits in a VARCHAR column using mysql? interesting for this, which mainly combines the idea of a CREATE TRIGGER with the idea of SIGNALing it in case an error situation is detected.

So, in your case it may be something like

DELIMITER //

CREATE TRIGGER trg_listing_referrers_unit_ins before insert on listing_referrers
for each row
begin
    IF EXISTS (SELECT * FROM listing_referrers WHERE lref_referring_url = new.lref_referring_url)
        THEN signal sqlstate '45000' set message_text = 'trying to insert duplicate lref_referring_url';
    END IF;
END
//

DELIMITER ;

Note, however, that this requires MySQL 5.5 or higher. If you have an earlier version of MySQL running, this might become ugly...

NB: Depending on the size of your table listing_referrers the SELECT statement in the trigger might become a performance hog. Make sure that, even if you can't define a UNIQUE index over lref_referring_url that have a NON-UNIQUE index in place. Otherwise you would end up in an O(n) algorithm instead of an O(log n) one, which may make a huge difference.

Idea using a Hashed Key Column

Instead of tracking uniqueness over the full VARCHAR, you also may check the uniqueness of a hashed value of your real key. For instance, you may add another column called key_hash typed CHAR(64) ASCII to your table, on which you will put a UNIQUE KEY constraint (you may even make this the primary key, if you want).

When inserting, you let MySQL compute the SHA2-hashed value of it on the fly, like

INSERT INTO listing_referrers (lref_referring_url, key_hash)
  VALUES("http://server.bogus/mylong/url", SHA2("http://server.bogus/mylong/url", 256));

If you enter the same URL twice, its SHA2 value will be the same and thus the UNIQUE KEY constraint of key_hash will start to rebel.

Due to the cryptographically-verified property of collision resistance of this function, it is ensured that it is practically impossible that you will find two URLs which have the same hash value. If you encounter one, please post it to the cryptographers community - they will be very keen to know your case for sure (and many intelligence services as well).

NB: If the URL of a record changes (via UPDATE) you always need to make sure that also the hash_key gets updated accordingly. Otherwise, you will go crazy. If this is a real use case for you, you may also want to have a look at MySQL: Computed Column (I don't know by heart, if this also works on a primary key column).

Idea of limiting the chars for uniqueness

If you are able to limit the uniqueness for the first n chars (with n < 255), then you might find Storing email VARCHAR(320) as UNIQUE, #1071 - Specified key was too long; max key length is 767 interesting.

Idea with limiting to 3072 chars

If you can make sure that your URL won't exceed 3072 chars, changing the charset to ASCII will increase this boundary (compared to 256 chars in UTF-8) to 3072. Details are described in MySQL unique 1500 varchar field error (#1071 - Specified key was too long)