SQL Server unique constraint problem

sql sql-server visual-studio-2010 unique-constraint
b0x0rz picture b0x0rz · May 7, 2010 · Viewed 12.5k times · Source

How to create a unique constraint on a varchar(max) field in visual studio, visually.

the problem is when i try it:

manage indexes and keys > add > columns

I can only chose the bigint columns, but not any of the varchar(max) ones.

Do I maybe have to use check constraints?

If yes, what to put in the expression?

Thnx for the info

Answer

marc_s picture marc_s · May 7, 2010

You cannot put a unique constraint on a VARCHAR(MAX) column (which could be up to 2 GB of text!!). You just simply cannot.

The unique constraint is enforced by a unique index in the background, and SQL Server has a 900 byte limit on index entries. You also cannot put a unique constraint on a VARCHAR(2000) field for that reason.

You'll need to find another way to achieve what you're trying to do. You could e.g. calculate the length and something like a checksum over your text and put a unique constraint on those length and checksum columns.

Related questions

Uninstalling SQL Server Management Studio 2014 in Windows 8

I have a student who installed SQL Server 2014 with tools, but the Management Studio part did not install. There is no SSMMS.EXE on the machine. So I tried to reinstall it, and the Management Studio tools come up checked …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More