Re: constraint - Mailing list pgsql-sql

Bernie Huang wrote:
> Hi,
>
> I am a newbie to SQL.  Could someone please explain why do I need a
> contraint, and how do I use it in SQL?  An example is needed.  Thanks
   Constraints  are  used to have the database engine ensure the   integrity of the data. It'l like teaching the
databaseitself   about "what makes sense".  This can be various things, like
 
   -  UNIQUE constraint
      In  a  combination  of columns no duplicate entries should      ever occur, so that a select with an exact key
willgive 0      or 1 rows as result, never more.
 
   -  EXPRESSION constraint
      Restricts  the  possible  values  of  a  row  to  a  given      expression. For example a list of  fixed  values
for one      column or something like "used_pct + unused_pct = 100".
 
   -  NOT NULL constraint
      The  value  for a specified attribute is not allowed to be      the SQL NULL value. NULL in SQL terms means
"unknown",and      you  cannot tell if "unknown" is equal to "anything" - not      even to another "unknown". Thus,
the expression  NULL  =      NULL must return FALSE.  To avoid the problems, that might      arise from this SQL
meaningof NULL,  you  tell  that  the      application   must   allways  provide  a  value  for  this      attribute.
 
   -  FOREIGN KEY constraint
      Restricts  the  possible  values  of  a   combination   of      attributes  to  existing  values  that  appear
inanother      table.
 
   Any attempt to modify the data in a  way,  so  that  the  end   result  would  violate  a  given  constraint,
results in  a   transaction abort and finally in a rollback operation, so  no   application  will  ever  be  able  to
leavethe database in a   physically OK, but  logically  inconsistent  state.  The  big   benefit  is, that if an
applicationprogrammer forgets about,   the database wouldn't let the errorneous action happen.
 
   Think of a database containing users and discussion forums. A   users login name must be unique, because it's the
keyyou use   to identify the user. So you setup a UNIQUE  constraint,  and   the database will ensure it is.
 
   Also  you  have different user types (ADMIN, USER and GUEST).   Your application might behave a little weired if a
user type   "FOO" occurs, so tell the database to never accept that value   for this column.
 
   Next you only want to allow a user to be removed, if all  his   discussion  forum articles are expired and removed.
Yousetup   a FOREIGN KEY constraint, so that noone can store an  article   with  a  user that doesn't exist, and noone
candelete a user   that still has articles referencing him.
 
   Oh - if you change the name of a user, you don't want  to  be   able  to forget to change all the references in the
articles?  Tell the FOREIGN  KEY  constraint  by  specifying  ON  UPDATE   CASCADE,  and  the  database  will
automatically change all   articles to the new user name.
 
   Of course, an application can take care of all these "logical   dependancies"  of  the  data.  But  especially in
thecase of   UNIQUE and FOREIGN KEY, this sounds much simpler than  it  is   in  concurrent  reality.   For  example in
aWEB application,   implemented by tens of CGI scripts, it's hard to never forget   about  a single check. It's easier
toadd another ERROR check   to  a  CGI  later  (because  some  user  complained  about  a   scripting  error  due  to
a not  catched SQL execution that   failed), than to fix  logically  inconsistent  data  that  is   already inside of
thedatabase.
 
   Good  application  development  has  the  following,  general   steps:
   1.  Describe the problem to  be  solved  in  plain  text,  as       detailed as possible.
   2.  Define  what  information  the application needs to solve       the problem.
   3.  Based on  2.  develop  the  data  layout,  including  all       dependancies   (constraints)   of  the
information, the       application has to deal with. This can be done with  some       boxes,  comments and arrows on a
sheetof paper (A0 - one       square meter - is a sheet too).
 
   4.  Convert the result of step 3. into a  DB  schema  in  SQL       syntax,  including  all the comments and arrows
converted      into CONTRAINT clauses.
 
   5.  Hack down a prototype, show it to a possible  user  (your       actual  customer) and iterate back from 5. or 2.
untilhe       buys the result.
 
   Yes, that's really the way of good  application  development.   The  key  here is, that however buggy your
applicationis, as   long as the database schema is good you  run  a  very  little   risk  that the application corrupts
thedata. It might crash,   try things it shouldn't and fail with cryptic error messages,   fall  over  it's  own  feet
and whatnot.  But all data that   remains after any successful transaction  is  consistent  and   makes sense,
accordingto point 3. above.
 
   A  relational  database  management  system  (RDBMS)  isn't a   stupid byte container with a powerful search engine
layerdon   top of it. If you just create a bunch of tables and have your   application doing it all, you abuse it as
such.The important   words  in RDBMS are RELATIONAL and MANAGEMENT. So tell a real   RDBMS "what" it manages, and it
does.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #