CODEWITHSUNDEEP
sql-serverunique-key
user841123
user841123

Reputation:

Making varchar(500) datacolumn unique

I have a requirement to make a column of varchar(500) unique.
Putting unique constraint would not work as it crosses the size limit of unique.
Then, what would be the best strategy to make column unique?

Upvotes: 3

Views: 1919

Answers (2)

user596075
user596075

Reputation:

You can use a DML trigger on INSERT and UPDATE. This gets around having to use a hash. I chose an AFTER trigger for simplicity, but this can be easily done with an INSTEAD OF trigger as well.

create trigger dml_EnforceUniqueVal
on dbo.UniqueBigColumn
after insert, update
as  
    declare @CountOfRepeats int
    select @CountOfRepeats = COUNT(*)
    from UniqueBigColumn
    where somestring in
    (
        select somestring
        from inserted
    )

    if @CountOfRepeats > 1
        rollback
go

Upvotes: 1

JNK
JNK

Reputation: 65147

Create another field with the HASH of that varchar(500), and put a UNIQUE CONSTRAINT on the hash field: HASHBYTES('md5', MyLongVarcharField)

This will cause poor performance but if you have a varchar(500) where you need to enforce uniqueness I'm assuming performance isn't on the forefront of your considerations anyways.

EDIT:

To clarify, the chance of having two strings output the same 128 bit hash value is 1 in 340,282,366,920,938,000,000,000,000,000,000,000,000. It's unlikely but not categorically impossible that you could have a collision.

If you are still concerned you can use SHA or SHA1 algorithms which are 160 bits.

Upvotes: 4

Related Questions