Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Check Constraints Expand / Collapse
Author
Message
Posted Wednesday, October 02, 2002 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/usingcheckconstraints.asp



Robert W. Marda
SQL Programmer
Ipreo
Post #7165
Posted Wednesday, June 04, 2003 10:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 17, 2003 12:00 AM
Points: 4, 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.






Post #42819
Posted Monday, June 09, 2003 9:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #42820
Posted Monday, June 09, 2003 9:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 30, 2004 8:52 AM
Points: 253, 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



Post #42821
Posted Thursday, April 19, 2007 9:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 07, 2012 7:42 AM
Points: 75, 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

Post #359663
Posted Monday, December 15, 2008 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 15, 2008 10:16 AM
Points: 3, Visits: 11
Really Nice article. I would like to know whether we can do cross table tasks using Check Constraint.


Thanks & Ragards,

Suresh Patibandla.
Post #619784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse