Re: Referential Constraints on Index Organized Tables in 8.1.5 on NT

I can confirm that it is broken on 8.1.5 and on 8.0.4, I also tried to include various NOT NULL conditions, restate the constraints etc. to see if that made any difference.

It's broken - and even ON DELETE CASCADE doesn't work.

I have an idea that I've seen a reference to this already though; but I couldn't track it down, and couldn't find anything on MetaLink

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Martin Haltmayer wrote in message <379CD587.34D3772F_at_in.augsburg.net>...
>Hi all,
>
>I found a strange behaviour that looks as if the referential constraints
>are not checked if the master table is index organized.
>
>Please try the following:
>
>-- test_foreign.sql shows an Oracle error.
>-- A foreign key constraint is ignored if the master table is index
>organized.
>-- It does not matter if the foreign key constraint is with or without
>"on delete cascade".
>
>-- Martin Haltmayer, 25th July 1999
>
>-- Last change:
>
>drop table test_slave cascade constraints;
>drop table test_master cascade constraints;
>
>create table test_master (
> n integer
> , constraint p_test_master primary key (n)
>)
>organization index -- if you leave away that line the delete statement
>fails as it should.
>;
>
>create table test_slave (
> n integer
> , m integer
> , constraint p_test_slave primary key (m)
> , constraint f_slave_master foreign key (n) references test_master (n)
>);
>
>insert into test_master (n) values (5);
>insert into test_slave (n, m) values (5, 6);
>
>delete from test_master where n = 5;
>
>select * from test_master;
>select * from test_slave;
>
>drop table test_slave cascade constraints;
>drop table test_master cascade constraints;
>
>Can you please confirm or deny? Do you know a workaround without
>triggers?
>
>Martin
Received on Tue Jul 27 1999 - 04:50:18 CDT