[thelist] This ISP has got the idea!!

rudy Rudy_Limeback at maritimelife.ca
Wed Oct 18 14:15:46 CDT 2000

> ... Or all other common sense warnings on stuff 
> that are there because some moron used it stupidly.

hi ron

right on -- see http://www.geocities.com/b120282/pictures/motorola.jpg

not my site, it's part of 3bp.com -- sorry, i can't help it, i think that 
site is a scream, i'm having a hard time stifling my guffaws here in my 
cubicle...  slow day at work, i guess

okay, now i owe for sure

<tip type="data design" synopsis="use check constraints wherever 

the database will *always* execute check constraints faster than if you 
perform the same action in program code

example of a check constraint --

   create table employee
     ( emplid integer
     , emplname varchar(100)
     , emplstatus char(1)
          constraint valid_emplstatus
             check (emplstatus in ('F','P','C','I'))  )

if you name the constraint, the database will use the constraint name when 
sending you an error message, along the lines of

     "...constraint valid_emplstatus violated; UPDATE action cancelled"

note that there are THREE places where you can check for valid data -- 
most people are familiar with client-side and server-side, but there is 
good server-side and better server-side...

1. client-side script (e.g. javascript) 

if it's a dropdown select list option, the chance of it being invalid is 
slim, so often you don't bother validating these types of fields 
client-side, perhaps just fields where the user types in the values

2. server-side script (e.g. cf, asp, php) 

for example, you often see logic like 

     <cfif form.emplstatus is "F">
        or form.emplstatus is "P">
        or form.emplstatus is "C">
        or form.emplstatus is "I">
            <!---process the transaction--->
            <!---send an error message--->

which is not bad (at least it ensures that bad data doesn't get into the 
database) but it can be a pain to maintain -- okay, maybe not for fields 
that will "never" change, but we've all worked on those, right?

3. server-side in the database 

our emplstatus example now requires no "if" logic at all --

    <!---process the transaction--->

this is guaranteed to run faster (it is "compiled" into the database 
definition of the table) to say nothing of being easier to maintain -- 
just think of the code you would have to modify if you suddenly want to 
allow another value of emplstatus and you had the values coded into CFIF 

by using a constraint, you define and maintain the constraining values in 
the database, and never have to touch the code again

did i mention it executes faster?

"but what about the error message, i don't want some butt ugly database 
error message displayed on my web page," i can hear some of you 

yes, that is why you need to use descriptive error messages, which you get 
simply by naming your constraints properly


[homer voice]: mmmmmmmmm, check contraints....


More information about the thelist mailing list