Re: Deferred constraint check

In article <893mr3$u8v$1_at_nnrp1.deja.com>,   jeanch_at_my-deja.com wrote:
> Folks,
>
> I've got deferred constraint set on a column in a table;
> However when I update an entry at commit time I get
> ORA-02292: integrity constraint (CCS_IFCS_IFC_FK) violated - child
> record found)
>
> It I disable all constraint obviously it works.
> So I wonder if the ROWID is a hidden part of constraint
> integrity checks.
>
> OR is the order in which those transaction are run matters for ORACLE.
>
> Any help welcome
>
> Cheers
> JC
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

I thought I should feed my findings back into the pool of knowledge. To obtain deferred check at commit; you must create the constraint like this

ALTER TABLE tab1
ADD CONSTRAINT c_PK PRIMARY KEY (col1)

ALTER TABLE tab2
ADD CONSTRAINT c_FK FOREIGN KEY (col1)
REFERENCE tab1(col1)
INITIALLY DEFFERED DEFERRABLE then you do you're transactions and commit; Whatch out for this
the order of which you execute youre transaction will be the order of which oracle will execute checks.

for instance.

insert tab2(col1)..
insert tab1(col1)...
commit;
the above will not work and you'll get a ora-02292 (child not found) because you created the child fisrt.
However if you do
insert tab1(col1)...
insert tab2(col1)...
commit;

Oracle is merry everything works. That was a surprise to me. Any more thought on this could be interesting.

Cheers
JC

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Feb 24 2000 - 13:43:46 CST