Re: Integrity constraint

From: Jaap W. van Dijk <j.w.vandijk_at_hetnet.nl>
Date: Wed, 4 Feb 2009 23:34:24 -0800 (PST)
Message-ID: <08c01a3a-116d-42af-8f71-67847b888678_at_q35g2000vbi.googlegroups.com>

On Feb 3, 7:01 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "JaapW. van Dijk" <j.w.vand..._at_hetnet.nl> wrote in message
> news:f927f1e9-9007-4791-b864->
> .> DELETE FROM x
> .> WHERE id NOT IN (SELECT id
> .> FROM y)
> .> AND id NOT IN (SELECT id
> .> FROM z);
> .>
> .> ORA-02292: integrity constraint (Y_FK) violated - child record found
> .>
> .> When there is already a clause in my delete to state do not delete
> .> records that are found in table Y, why do I get the integrity
> .> constraint error message?.
> .
> Can id be null in y and z? In that case you are comparing id in x with
> .a set that contains a null. For an id in x that is different from the
> .not null id's in y and z, the NOT IN condition will yield a UNKNOWN
> .and not a TRUE.
> .
> .Solution: extend SELECT id FROM y/z with AND id IS NOT NULL
>
> If Y or Z had a null for id in any row then the delete statement
> would always find no rows to delete - so this particular problem
> would not arise.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Yes, your right of course, I didn't think properly.

Regards,
Jaap. Received on Thu Feb 05 2009 - 01:34:24 CST