Re: foreign keys

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.com