Re: foreign keys
Date: Tue, 25 Nov 2014 08:23:19 +0000 (UTC)
Message-ID: <m51e9n$7gh$3_at_solani.org>
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
> WHERE essn NOT IN
> (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 http://mgogala.byethost5.comReceived on Tue Nov 25 2014 - 09:23:19 CET