| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Deferred constraint check
A copy of this was sent to jeanch_at_my-deja.com
(if that email address didn't require changing)
On Thu, 24 Feb 2000 19:43:46 GMT, you wrote:
>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.
>
thats not right -- deferred constraints don't care which order the operations are done in. not at all. they were designed in part to support snapshot group refreshes that have primary keys/foreign keys on the snapshots and you cannot control the order in which the operations take place.
Consider this example
ops$tkyte_at_8.0> select * from v$version;
BANNER
ops$tkyte_at_8.0> create table p ( x int primary key ); Table created.
ops$tkyte_at_8.0> create table c ( x int,
2 constraint c_fk foreign key (x) references p(x) initially deferred
deferrable
3 );
Table created.
ops$tkyte_at_8.0> insert into c values (1); 1 row created.
ops$tkyte_at_8.0> insert into p values ( 1 ); 1 row created.
ops$tkyte_at_8.0> commit;
Commit complete.
I've run this in 8.0.3, 8.0.6, 8.1.5 -- it works consistenly in all of those. can you run in your 8.0.4 instance and see what happens?
>Cheers
>JC
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Feb 25 2000 - 20:56:45 CST
 |
 |