> What I'd like to know is to which extend constraints are used in
practice
> for example to modell referentiell integrity.
With Oracle, you can define primary key, unique key, foreign key, and check
contraints.
Primary keys are unique, non-null column(s) that have an associated index
(that Oracle builds for you, "for free"). The best primary keys are
numeric. Define them in Master tables and then as foreign keys in Child
tables, to make joins faster.
Unique keys are unique column(s) values that may be null. Again Oracle
builds the associated index for you. Use them for non-primary key
alternative column lookups.
Foreign key contraints enforce referential integrity. This is valuable to
make sure you don't get "garbage" values in these columns, such as an order
line item for a non-existent item. If you don't use them, how can you
verify at the database level that your data is valid? You'd have to
periodically run outer joins to see where the foerign key values don't have
matching Master row entries.
Check contraints are used to enforce data validity rules for a particular
column or between columns in the same table. For example, you can enforce
the rule that the state abbreviation is always in caps. You can make sure
that a quantity column is greater than zero. These are to make sure your
front-end client application isn't doing something stupid.
> Are they just used as a debugging tool?
No, they're a vital part of a Relational Database. They help you avoid GIGO
-- Garbage In, Garbage Out.
> Are constraint checks switched off at run-time to save performace for
the
> end user?
They shouldn't be turned off when running client applications. The only
time they might be temporarily disabled are on table loads, to speed it up.
Then after the data is loaded, turn on the constraints. If any data fails
the constraint, the constraint is not enabled. You'll then have to
determine which rows violated the constraint and clean them up.
> What are common reactions in case of a constraint violation? Abort the
> current transaction? Just go on working with inconsistent data, because
> voilations don't occur very often?
When an integrity constraint is violated, the current transaction is rolled
back. For example, an insert or update will fail to make changes to the
database due to an integrity constraint violation. But you don't know about
it until you actually try the insert/update/delete. So you wind up putting
the smarts in the client application to check for potential violations
before attempting the transaction. Duplicating database constraints in the
client apps is not an unusual practice.
Received on Mon Nov 17 1997 - 00:00:00 CST
