Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: check constraints - how implemented and how fast????

Re: check constraints - how implemented and how fast????

From: John P. Higgins <jh33378_at_deere.com>
Date: Sun, 13 Dec 1998 13:18:26 -0600
Message-ID: <36741301.7C7DFD59@deere.com>


A check constraint operate only within a row on insert or update. Other rows of the same table as the inserting or updating row are not available to the constraint logic. Rows of other tables are not available to the constraint logic. So, an index would never be used.

From the Oracle SQL manual:
CHECK Constraints

The CHECK constraint explicitly defines a condition. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). For information on conditions, see the syntax description of condition . The condition of a CHECK constraint can refer to any column in the table, but it cannot refer to columns of other tables. CHECK constraint conditions cannot contain the following constructs:

     queries to refer to values in other rows

     calls to the functions SYSDATE, UID, USER, or USERENV

     the pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM

     date constants that are not fully specified

Whenever Oracle7 evaluates a CHECK constraint condition for a particular row, any column names in the condition refer to the column values in that row.

If you create multiple CHECK constraints for a column, design them carefully so their purposes do not conflict. Oracle7 does not verify that CHECK conditions are not mutually exclusive.

Steve Perry wrote:

> Can anyone tell me how check constraintes are implemented on Oracle
> 7.3.x or 8.x?
>
> I've been in the habit of creating indexes on all foreign keys. I
> assumed this would speed up the lookup when trying to delete a parent
> row that has child rows from another table using the key. I used to use
> triggers to implement the restriction, which needed the index for speed.
> I was talking to a friend and they said it's not needed with check
> constraints, but they couldn't tell me how Oracle does it.
>
> Thanks,
> Steve
Received on Sun Dec 13 1998 - 13:18:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US