Re: constraint
Re: constraint
От:
JanWieck@t-online.de (Jan Wieck)
Дата:
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@Yahoo.com #
constraint
От:
Bernie Huang <bernie.huang@ec.gc.ca>
Дата:
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 - Bernie
