RE: Composite foreign key - null values
} -----Original Message-----
} From: Art S. Kagel [SMTP:kagel@bloomberg.com]
} Sent: Thursday, January 22, 1998 10:40 PM
} To: informix-list@rmy.emory.edu
} Subject: Re: Composite foreign key - null values
}
} Hamzova Katarina wrote:
} >
} > 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
}
} It sounds like you have defined three separate FOREIGN keys but if the
}
} PRIMARY KEY to table A is a composite (three columns) how can you
} declare tableB.column 1 as a FOREIGN KEY independent of the other two
} columns? Please post a more complete schema, perhaps a dbschema -ss
} for each of the two tables so that we can understand.
}
Sorry for misleading you of course FK in tableB is composite
key designed
properly
schema is :
alter table tableB add constraint foreign key
(column1,column2,column3)
references tablesA
yesterday i got information from Informix that it is feature
if any part of composite key contains null value and the rest do
not
no control of RI is done.
i would apreciate hints how to prevent it .
katarina
} Art S. Kagel