4

I have a varbinary column in SQL, and I want this column to be always unique. However, I see that SQL doesn't allow to create a unique constraint on a varbinary column. Is there any workaround to ensure this uniqueness? Maybe by using some other type of constraint, or something else?

Thanks!

user225206
  • 63
  • 6

4 Answers4

2

If the varbinary is reasonably short then you could create a computed column of the hex representation and put a unique constraint on that. Ref SQL Server converting varbinary to string for how to convert varbinary to hex string.

Edit1: As pointed out by @GiorgiNakeuri the limit for unique constraints is 900 bytes, so 450 bytes in hex.

Edit2: Alternatively, if you can tollerate a (very) small risk of failure then you could create a computed column with an MD5 hash of the varbinary value and put the unique constraint on that. See the docs for HASHBYTES.

Community
  • 1
  • 1
Rhys Jones
  • 5,348
  • 1
  • 23
  • 44
  • Is there any other way? What if the length is a problem? Using any other type of constraint or something? – user225206 Feb 03 '15 at 12:28
  • I think **Edit2** is a pretty good approach and I've used it before. Instead of a computed column, I would use HASHBYTES in the trigger to store the hash in a different column. – CSquared Sep 05 '19 at 16:12
1

I guess you have VARBINARY(MAX). The length of it is 2^31-1 bytes, but the maximum length of key should be 900 bytes. So you are actually limited with VARBINARY(900). If the size of VARBINARY <= 900, you can add unique index.

As a workaround you can add Trigger and rollback inserts, if there is already same values in table.

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
0

You could make the column a primary key. Scripted test table shown below

/****** Object:  Table [dbo].[Table_1]    Script Date: 02/03/2015 12:19:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Table_1](
    [test] [varbinary](50) NOT NULL,
    [rf] [nchar](10) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [test] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
Ewan
  • 1,067
  • 8
  • 15
  • No need for a primary key, and it will fail if the varbinary field is longer than 900 bytes. – Rhys Jones Feb 03 '15 at 12:22
  • yeah I agree was just demonstrating that varbinary could be set to be unique subject to it being less than 900 bytes in length – Ewan Feb 03 '15 at 12:24
0

A simpler solution could be to manually maintain the uniqueness of the column by checking the existence of the value to be inserted or updated before inserting or updating the column.

Example:

DECLARE @Exists BIT = (
SELECT  CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
FROM    [Schema].[Table]
WHERE   [DecryptedColumn] = @NewValueToCheck
)
IF (@Exists = 0)
BEGIN
-- Insert or Update
END
ELSE
-- Return Error
BEGIN
END
Anthony Van Tonder
  • 9
  • 2