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

3 Answers3

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,158
  • 1
  • 19
  • 43
  • 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
  • 34,370
  • 8
  • 37
  • 69
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
  • 967
  • 6
  • 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