Referential Constraints on Index Organized Tables in 8.1.5 on NT

From: Martin Haltmayer <Martin_Haltmayer_at_in.augsburg.net>
Date: Mon, 26 Jul 1999 23:39:19 +0200
Message-ID: <379CD587.34D3772F@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 Mon Jul 26 1999 - 16:39:19 CDT