Re: Composite foreign key - null values

In article <6a7j9u$5tp@cssun.mathcs.emory.edu>, Hamzova Katarina <Katarina.Hamzova@swh.sk> writes >We had a strange problem. Obviously our tables are designed with >composite >primary keys, foreign keys in referencing tables are sometimes null >allowed. >Unfortunately we updated several rows with bad (incomplete) FK , we were >surprised >that we could do it without error - RI violation. >So we made some tests >example >table A >column1 PK >column2 PK >column3 PK > >table B >column1 null allowed FK --> references table A >column2 null allowed FK >column3 null allowed FK > >we run several statements > >insert into tableB values (null,"xxxx",null),... etc> >even >update tableB set B.column1=null where column2 is not null> >without any error. >The last one was the most serious and dangerous it destroyed data. > >We were rather confused because we thought that >even incomplete foreign key is considered as referenced value. >Primary key can't be null so FK written in such a way >(part of FK null, the rest not null) must ( we thought) >evoke error -691...missing key in referenced table... > >We tested it in Informix On line V 7.20 DSA > Informix On line V 7.22 WGS > Informix On line V 5.01 >OS Sinix > >primary keys were created in all possible ways >-- create index ... then > alter table ... add constraint primary key > >-- alter table ( without previous create index) > >-- create index ... > alter table add constraint unique ...> >-- alter table add constraint unique ( without explicit. created index) > >It seems to be a feature. >We appreciate any information,hints >how RI in Informix works as far as composite foreign key with >several null columns concerned. > >katarina > > If not sure, but in the table containing the foreign key can't you just declare the foriegn key column to be 'not null'? > >-- >Katarina Hamzova Voice : +42 7 5384324 >SWH s.r.o Bratislava >Slovakia Katarina.Hamzova@swh.sk Wow!! Informix in Slovakia, sounds East Europe, my geography is a bit rusty, where is that?? I'm in London. England!! PS Check out the FAQ below, I'd be amazed to find someone from so far away reading it!! PPS How many people in Solvakia use Informix, it is popular?? > > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care