Re: constraint

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 database itself
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 will give 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 meaning of 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 in another
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 leave the database in a
physically OK, but logically inconsistent state. The big
benefit is, that if an application programmer 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 key you 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. You setup
a FOREIGN KEY constraint, so that noone can store an article
with a user that doesn't exist, and noone can delete 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 the case of
UNIQUE and FOREIGN KEY, this sounds much simpler than it is
in concurrent reality. For example in a WEB application,
implemented by tens of CGI scripts, it's hard to never forget
about a single check. It's easier to add 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 the database.

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 sheet of 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. until he
buys the result.

Yes, that's really the way of good application development.
The key here is, that however buggy your application is, as
long as the database schema is good you run a very little
risk that the application corrupts the data. 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, according to point 3. above.

A relational database management system (RDBMS) isn't a
stupid byte container with a powerful search engine layerd on
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(at)Yahoo(dot)com #