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 #
