SQL Server unique constraint problem

views:

247

answers:

+1 Q:

SQL Server unique constraint problem

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

+1 A:

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 2010-05-07 20:58:21

it's supposed to be an email field. what can i do? shorten it?

b0x0rz 2010-05-07 21:00:25

Yes, shorten it. 320 chars is the max length according to http://email.about.com/od/emailbehindthescenes/f/address_length.htm

Martin Smith 2010-05-07 21:01:52

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 2010-05-07 21:02:04

@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 2010-05-07 21:06:17

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 2010-05-07 21:07:24

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 2010-05-07 21:08:16

it's from http://www.ibm.com/developerworks/web/library/wa-dbdsgn1.html btw

b0x0rz 2010-05-07 21:08:31

@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 2010-05-07 21:11:32

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 2010-05-07 21:12:10

just one more quick question, is that 900 bytes per table or per column/field? thnx

b0x0rz 2010-05-07 21:13:25

@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 2010-05-07 21:14:25

@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 2010-05-07 21:16:53

k thank you, sorry for the trouble

b0x0rz 2010-05-07 21:17:02

@b0x0rz: no trouble at all - that's what this site is all about - ask questions and get answers!

marc_s 2010-05-07 21:23:46

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 2010-05-07 21:28:03

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 2010-05-07 21:28:22

+1 A:

I am not sure why you neeed to do this. It sounds highly suspect. But, if you must do it, I would create a separate column containing a checksum of the text, and put a unique index on that.

RedFilter 2010-05-07 21:00:08

thnx for trying to help

b0x0rz 2010-05-07 21:15:01

+1 A:

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 2010-05-07 21:00:48

thnx for trying to help

b0x0rz 2010-05-07 21:14:29

+1 A:

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

Nissan Fan 2010-05-07 21:04:32

thnx for trying to help

b0x0rz 2010-05-07 21:15:18

ansaurus

tags:

views:

answers:

SQL Server unique constraint problem

related questions