SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Check Constraints


Using Check Constraints

Author
Message
Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2959 Visits: 113
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/usingcheckconstraints.asp

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
benjust
benjust
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1
i found the examples a bit strange, surely noone would use a varchar field to store a date of birth, you would use a datetime or timestamp field. and the 19[0-9][0-9] OR 200[0-2] was a bit wierd (possibly [0-9][0-9] OR [0-9][0-9][0-9][0-9] instead, either way is bad).

what i really wanted to comment on was the conclusion where it is declared "constraints are not necessary when you check things client-side", well, i would argue that constraints are necessary so long as the database can still keep up in terms of performance.

noone should ever trust anything coming from the client, even if they did go to the trouble of throwing together some javascript regexs. if you can't technically trust the information being given, you must check it, unless doing so cripples performance and that particular data integrity isn't mission critical.

anyway, that's my 2 cents.



Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2959 Visits: 113
Thanks for your comments. Please allow me to explain the reason I wrote the article the way I did:

The examples I used for the BirthDate constraint are based on the assumtion that you are talking about an employee table. At the time I wrote this article it seemed extremely unlikely to have an employee that was born before 1900 or after 1999.

Your trust of client-side applications depends on who controls the building and testing of those applications. If you trust those people then you will not need to use constraints. If you don't trust them then using constraints will give you the assurance you need.

In my article I failed to specify the data type for the columns used in the examples. They were run on the NorthWind database and for the BirthDate the data type is datetime.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
mromm
mromm
SSC Eights!
SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)

Group: General Forum Members
Points: 927 Visits: 1
quote:

Your trust of client-side applications depends on who controls the building and testing of those applications. If you trust those people then you will not need to use constraints. If you don't trust them then using constraints will give you the assurance you need.




The question should not be "do I trust those people or do I not". It sounds kind of personal, offensive and... not technical. Instead we may want to think "how do I protect data integrity of my database now and in the future". What if those trustworthy people quit and new ones arrive? What if a new application starts feeding the database? What if "bad data" is put into the database by a mistake? Of course, some trustworthy people never make mistakes but all those are already hired by.. the marsians I guess.

Examles given in the article are not very good. I would not put such a constraint on a telephone column. It does not break any integrity if someone put a "strange" value in a telephone column. The given LIKE check is very expensive. Checking birthdat with LIKE is also not needed. If you really cautious, use >= and <= operations but this one could be left to the business rules implemented somewhere else.

It seems the author of the article took the worst (slowest) types of constraints. There are better examples. In addition, the article does not even mention the word PERFORMANCE.


Edited by - mromm on 06/09/2003 09:49:43 AM



Shah_hs
Shah_hs
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 83

Thanks for such a nice article. Further in day to day tasks i need to create a constraint which will restrict the table field size in 50 k. The field is a attachment field and i need to restrict this field with maximum of 50 k size. Any idea how this can be done?

Thanks


sureshp
sureshp
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 11
Really Nice article. I would like to know whether we can do cross table tasks using Check Constraint.


Thanks & Ragards,

Suresh Patibandla.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search