Re: foreign keys

From: Mladen Gogala <>
Date: Tue, 25 Nov 2014 08:23:19 +0000 (UTC)
Message-ID: <m51e9n$7gh$>

On Mon, 24 Nov 2014 20:50:00 +0100, Peter Schneider wrote:

> You have ESSN entries in WORKS_ON which do not exist as SSN in EMPLOYEE.
> Try
> SELECT essn
> FROM works_on
> (SELECT ssn
> FROM employee);

I usually prefer NOT EXISTS in queries like this. The reason is handling of the NULL values.

> If you create a constraint in the state of ENABLED an VALIDATED, all
> preexisting data must satisfy this constraint, otherwise the statement
> will fail.
> Gruß Peter

The fastest way of finding out would be to create table EXCEPTIONS using $ORACLE_HOME/rdbms/admin/utlexcpt.sql (I think that's the correct file name) and execute ALTER TABLE ENABLE CONSTRAINT EXCEPTIONS INTO EXCEPTIONS. Usually, that's an order of magnitude faster than using a query like you suggested.
Also, if there are the initial values that the user knows will not have a parent key, the constraint can be created using NOVALIDATE clause. In this case, I doubt that it's the matter of conscious decision.

Mladen Gogala
The Oracle Whisperer
Received on Tue Nov 25 2014 - 09:23:19 CET

Original text of this message