Using Check Constraints

Question

Using Check Constraints

Robert W Marda

SSChampion

Points: 13413
More actions
October 2, 2002 at 12:00 am

#158505

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

benjust SSC Journeyman Points: 96 More actions · Answer 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 SSChampion Points: 13413 More actions · Answer 2

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 SSCarpal Tunnel Points: 4301 More actions · Answer 3

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 Ten Centuries Points: 1117 More actions · Answer 4

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 SSC Enthusiast Points: 117 More actions · Answer 5

Really Nice article. I would like to know whether we can do cross table tasks using Check Constraint.

Thanks & Ragards,

Suresh Patibandla.