Re: Declaring Unenforced Constraints

Laconic2 wrote:
> Is it possible to do such a thing? sure. Present day RDBMSes have a
> feature that declares an index "inactive", meaning that it still
exists,
> and it's definition is still there, but updates don't have to update
the
> index, at least not right now. You could do the same thing with
> constraints.

Oracle already does this. Quote from the Oracle Data Warehousing Guide:
<QUOTE>
ALTER TABLE sales ADD CONSTRAINT sales_time_fk FOREIGN KEY (time_id) REFERENCES times (time_id) RELY DISABLE NOVALIDATE; This statement assumes that the primary key is in the RELY state. RELY constraints, even though they are not used for data validation, can:

• Enable more sophisticated query rewrites for materialized views. See Chapter 18, " Query Rewrite" for further details.
• Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it. </QUOTE>

The 3 keywords have meanings as follows:

RELY: assume that the constraint holds true when performing query re-writes

DISABLE: do not enforce the constraint for new data

NOVALIDATE: do not fail to create the constraint if it is violated by existing data Received on Fri Nov 05 2004 - 16:50:15 CET