SQL Server——如何跨多个列创建惟一的约束，其中一个列是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. See previous errors.

Msg 1919，第16级，状态1，第1行“my_string_tbl”列在表“my_string_tbl”中，这种类型在索引中用作键列无效。Msg 1750, 16级，状态0，第1行不能创建约束。见以前的错误。

How can I create the constraint I need?

如何创建所需的约束?

2 个解决方案

#1

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.

任何索引最多只能包含900字节的列。没有办法绕过这个问题，或者把这个数字增加到更高的值。

#2

It's either:

它是:

text/ntext/image/varchar(max)/nvarchar(max)/varbinary(max)/xml
文本/ ntext /图像/ varchar(max)/ nvarchar(max)/ varbinary(max)/ xml
too long > 900 bytes
>900字节太长

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

当然，如果你有文本/ntext/image，这也不行。因此，修改为SQL Server 2005引入的较新的varchar(max)、nvarchar(max)、varbinary(max)类型之一并对其进行散列处理

#1