SQL Server - how to create unique constraint across multiple columns, where one column is VARCHAR

I need to be able create a unique constraint that checks that a string and integer combination is unique.

I have tried code:

ALTER TABLE mytable ADD CONSTRAINT
constraint_Unique_ForeignID_MyString UNIQUE NONCLUSTERED
(
     foreign_id, my_string_col
)

But I get error:

Msg 1919, Level 16, State 1, Line 1

Column 'my_string_col' in table 'my_string_tbl' is of a type that is invalid for use as a key column in an index.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. Se开发者_StackOverflow社区e previous errors.

How can I create the constraint I need?

It's either:

• text/ntext/image/varchar(max)/nvarchar(max)/varbinary(max)/xml
• too long > 900 bytes

In this case, you can add a computed column based on a hash of the value and use this for a constraint.

Of course, if you have text/ntext/image then this fails too. So change to one of the newer varchar(max), nvarchar(max), varbinary(max) types introduced with SQL Server 2005 and hash that

How BIG of a VARCHAR is your column??

Any index can only contain columns up to a maximum of 900 bytes total. There is no way to get around this, or to increase that number to a higher value.