Re: Composite foreign key - null values

Well I shall give a try at a possible explanation but then again I have never seen The Source of the engine. My explanation goes uppon a supposition : Informix uses the triggering mechanism to know how to check Referential Integrity. This is purely speculation but If it is implemented that way would give the results you have : Referential constraint could be using field triggering for checking changes. A feature : informix doesn't trigger when null values are updated (to and from) So that would mean that it doesn't check integrity even if you change it from null to not null. null values seem to be exception to a lot of things within informix so beware of them ! Speculatively yours, Gaetan Maerten, Informix DBA EDS Belgium (Bacob account) David Williams <djw@smooth1.demon.co.uk> wrote in article <81HqRcAFF$x0Ew01@smooth1.demon.co.uk>... > 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 >