I struggled over following scenario and I am not really sure if I am just thinking the wrong way or if the DB has a problem with VIEWS and referential constraits. I couldn't find anything in the mailin-list(hope I didn't miss anything ;-)
I think it is a wrong behavior.
The scenario:
I must test for a NOT NULL value on a view column, which is selected
using an oracle type outer join. But the select command somehow seems to
look-up the origin table-columns instead of the view-columns.
I created a small script where you can check the behavior:
Am I wrong, or is there a problem?
Regards,
Manfred
PS: My DB-Version is 7.3.0 / 008-000-054-344 running on SUSE LINUX
7.1
//
// TEST-Script for checking view behaviour
//
create table lookup (
id integer,
name varchar(20)
)
//
alter table lookup add primary key (id)
//
create table test(
id integer,
name varchar (30),
id_lookup integer
)
//
ALTER TABLE test ADD FOREIGN KEY FK_1 (id_lookup) REFERENCES lookup
(ID) ON DELETE restrict
//
create view vtest(
id,
name,
id_lookup
) AS
SELECT t.id, t.name, l.name
FROM test t, lookup l
WHERE t.id_lookup = l.id (+)
//
insert into lookup (id, name) values (1,'first')
//
insert into lookup (id, name) values (2,'second')
//
insert into lookup (id, name) values (3,'third')
//
insert into test ( id, name , id_lookup) values (1,'test1',1)
//
insert into test ( id, name , id_lookup) values (2,'test2',2)
//
insert into test ( id, name , id_lookup) values (3,'test3',null)
//
insert into test ( id, name , id_lookup) values (4,'test4',null)
//
//
select * from vtest
// RESULT:
// ID NAME ID_LOOKUP
// 1 test1 first
// 2 test2 second
// 3 test3 ?
// 4 test4 ?
//
// this should be ok!
//
select * from vtest where id_lookup is null
// RESULT:
// ID NAME ID_LOOKUP
// 1 test1 ?
// 2 test2 ?
// 3 test3 ?
// 4 test4 ?
//
// there must be an error: in the first select for id=1 id_lookup
is not null!!
//
select * from vtest where id_lookup is not null
// RESULT:
// ID NAME ID_LOOKUP
// 1 test1 first
// 2 test2 second
// 3 test3 ?
// 4 test4 ?
// some other wierd result here...
