Is a check constraint for max length necessary?

Question

Is a check constraint for max length necessary?

vba-dev

Right there with Babe

Points: 717
More actions
March 29, 2011 at 1:01 pm

#237556

Please consider the constraint defined for a varchar field named Description of length 150:
(len([Description])>(0) AND len([Description])<=(150))
If I insert a new record with length of Description = 0, I am informed that the check constraint was violated. If I insert a new record with length greater than 150, I'm informed the string will be truncated. Is the check constraint (<=150) violated?
I note that if I bump the max length in the check constraint to something less than 150, then violate that length on an insert, I am informed that I've violated the check constraint.
This exercise leads me to wonder why a check in a constraint for max length is necessary. Is it useful to constrain input to a maximum length when that length is the same as the length defined for the field in question?
Thanks for any help on this.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you try to insert a value larger than the field can accept it will fail long before the check constraint would be checked.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vba-dev Right there with Babe Points: 717 More actions · Answer 2

Thanks.

Yes, that is apparent when I test an insert. So, is a check constraint for maximum length useful, when the max length defined in the constraint is the same as the length defined for the field?

steveb. SSC-Forever Points: 46733 More actions · Answer 3

I would have thought that if the check constraint is checking for the same max length as the data type length then it is kind of pointless..

vba-dev Right there with Babe Points: 717 More actions · Answer 4

Hey, guess what? Me, too.

The better question is, why the heck am I even asking?

On to something else...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

steveb. (3/29/2011)
I would have thought that if the check constraint is checking for the same max length as the data type length then it is kind of pointless..

Absolutely, it'll never fire for any value that breaks it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 6

I don't see a reasonable use for Check Constraints. I have pre-process validations I perform before doing major processing, I will mark records as error if the fields in them fail validations so my processing doesn't fail, then fix the errors after processing.

How would CHECK Contraints be useful in this scenario?

steveb. SSC-Forever Points: 46733 More actions · Answer 7

churlbut (3/31/2011)
I don't see a reasonable use for Check Constraints. I have pre-process validations I perform before doing major processing, I will mark records as error if the fields in them fail validations so my processing doesn't fail, then fix the errors after processing.
How would CHECK Contraints be useful in this scenario?

Check constraints are used by the query optimisation engine so by having them you can improve the performance of a query (like everything with the engine how much improvement really depends on a lot of other factors as well)

Also it is good practice to restrict the values in the database level, just as it is good practice to have foreign keys and the correct data types.

vba-dev Right there with Babe Points: 717 More actions · Answer 8

Thanks to all, I appreciate the discussion of this topic.

Since SQL Server can use this information for optimization, as SSCrazy points out, I'll go ahead and add both min and max length constraints to varchar columns. And, since this is done by script, which I have in my toolbox already, it costs me nothing in time. This of course assumes SSCrazy is correct. Though I seldom post, I have read this forum for a number of years and have developed a respect for SSCrazy's opinions. So, I have no trouble taking SSCrazy at his/her word. In any case, I strongly doubt there is any particular downside to using max length constraints.

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 9

SSCrazy is just a rank based on questions of the day answered or points earned for posting.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

Michael Valentine Jones

SSC Guru

Points: 64818

April 18, 2011 at 1:00 pm

#1313028

That SSCrazy guy really seems to know his stuff.