19

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
b0x0rz
  • 3,953
  • 8
  • 52
  • 82

3 Answers3

21

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • it's supposed to be an email field. what can i do? shorten it? – b0x0rz May 07 '10 at 21:00
  • 4
    Yes, shorten it. 320 chars is the max length according to http://email.about.com/od/emailbehindthescenes/f/address_length.htm – Martin Smith May 07 '10 at 21:01
  • 13
    Holy crap man, an EMAIL field? Yes, shorten it. When someone shows me a 2GB long email address, I'll eat my words – Neil N May 07 '10 at 21:02
  • @b0x0rz: an e-mail address **NEVER EVER** needs to be 2 GB in length!! Use an appropriate size - I typically use VARCHAR(200) and have yet to ever see an e-mail address that doesn't fit in that..... – marc_s May 07 '10 at 21:06
  • 2
    I've been using Varchar(100) for about 10 years now and not once have I seen an email that even approaches that. – Neil N May 07 '10 at 21:07
  • 1
    yeah, don't get mad people :( all i did was research it and find: "You can avoid a lot of confusion later on by making all text messages of type varchar(n) [...] Even if other business requirements restrict the maximum length of certain fields to specific values, the DB schema is arguably not the best place to enforce these rules. By the time the data reaches the DB, it is too late to do anything about it (except reject it)." – b0x0rz May 07 '10 at 21:08
  • 1
    @b0x0rz: Yeah - but **varchar(n)** doesn't mean **varchar(max)** for all fields. Use some good judgement and make your fields as long as they need to be - but not any longer. Having all VARCHAR(MAX) might seem like a smart move - it's not, quite the contrary. It has plenty of performance and other negative impacts. Use good judgement, and make the fields as big as they typically need to be. – marc_s May 07 '10 at 21:11
  • 1
    i always did, until TODAY when i read that :( it did seem plausible, with technology advancements and databases being smarter and smarter, i thought - maybe the database CAN adjust itself internally with the data that is in there... anyhow my bad, but database design advice on the internet is somehow not consistent – b0x0rz May 07 '10 at 21:12
  • just one more quick question, is that 900 bytes per table or per column/field? thnx – b0x0rz May 07 '10 at 21:13
  • 2
    @b0x0rz: each index entry (the length of all columns that make up one index entry) cannot be more than 900 bytes - for each index, on any table. E.g. you can have two VARCHAR(200) fields - but not two VARCHAR(500) fields. – marc_s May 07 '10 at 21:14
  • 2
    @b0x0rz The paragraph before that recommends 32, 256 or 4k - and I think that's fine as a general rule for complete unknowns. But typically, I'm going to get better information in my requirements gathering and I'm going to enforce the lengths in the the database once I know them. Enforcing lengths (like any constraints) will help to catch unexpected problems earlier. What's worse, finding out you've been getting huge and invalid emails for years or finding out that someone has an email address too long and you need to revise the system a little. – Cade Roux May 07 '10 at 21:16
  • @b0x0rz: no trouble at all - that's what this site is all about - ask questions and get answers! – marc_s May 07 '10 at 21:23
  • Just remembered that I used to have a 36 character email address and that annoyingly used to get rejected from some web forms. I can't imagine that many people would go much above 50. – Martin Smith May 07 '10 at 21:28
  • yes, it is awesome how fast you can get an answer here, i searched the net for hours trying to see what the problem was and before that looking for database design advice. thnx again – b0x0rz May 07 '10 at 21:28
  • 1
    Sorry for the zombie-comment, but .... an email address can be *at most* 254 characters, so setting it as VARCHAR(254) would be very sensible. It's not _possible_ to have a longer email address ... definitely not 2gb! – Algy Taylor May 11 '16 at 15:55
2

One way to do this would be to add a column for a hash that is calculated whenever you insert or update the column and put a unique index on that. While hash collisions do happen, it is extremely unlikely.

You could use this T-SQL keyword:

http://msdn.microsoft.com/en-us/library/ms174415.aspx

Keith Adler
  • 20,880
  • 28
  • 119
  • 189
1

Even if this were possible, it would be a bad idea.

1) There is another way. Find some other data to use as your unique column

2) If you ABSOLUTELY HAVE TO use the varchar(Max). Maybe hash it on insert/update and add a hash column?

Neil N
  • 24,862
  • 16
  • 85
  • 145