Re: Composite foreign key - null values
--------------82F7B33222C76A8FF5737C94
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
This is not an informix issue. Instead, it is an ANSI SQL standard issue.
Primary key cannot be null does not mean each of the composite key cannot be
null. Referencial Integrity is verified only if the foreign key is not
null. Please note the comment below -- there is no RI violation here. ANSI
standard states "if a column is null, the result is unpredictable." It is
then up to the vendor to define what will happen.
Once you know the problem of null values for composite primary/foreign key.
Update is not an issue. What will really mislead people is the join of the
two table. This is one of the interview questions I used to ask
interviewees. The answer I expect from interviewees is "the answer is
unpredictable."
Chao Y. Din
> > > 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.
This is not an RI violation according to ANSI SQL standard.
> > > 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
> } Art S. Kagel
--------------82F7B33222C76A8FF5737C94
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
<HTML>
This is not an informix issue. Instead, it is an ANSI SQL standard
issue.
<P>Primary key cannot be null does not mean each of the composite key cannot
be null. Referencial Integrity is verified only if the foreign key
is not null. Please note the comment below -- there is no RI violation
here. ANSI standard states "if a column is null, the result is unpredictable."
It is then up to the vendor to define what will happen.
<P>Once you know the problem of null values for composite primary/foreign
key. Update is not an issue. What will really mislead people
is the join of the two table. This is one of the interview questions
I used to ask interviewees. The answer I expect from interviewees
is "the answer is unpredictable."
<P>Chao Y. Din
<BR>
<BLOCKQUOTE TYPE=CITE>> > We had a strange problem. Obviously our tables
are designed with
<BR>> > composite
<BR>> > primary keys, foreign keys in referencing tables are sometimes
null
<BR>> > allowed.
<BR>> > Unfortunately we updated several rows with bad (incomplete) FK
, we
<BR>> were
<BR>> > surprised
<BR>> > that we could do it without error - RI violation.</BLOCKQUOTE>
<B>This is not an RI violation according to ANSI SQL standard</B>.
<BR>
<BLOCKQUOTE TYPE=CITE>> > So we made some tests
<BR>> > example
<BR>> > table A
<BR>> > column1 PK
<BR>> > column2 PK
<BR>> > column3 PK
<BR>> >
<BR>> > table B
<BR>> > column1 null allowed FK --> references table A
<BR>> > column2 null allowed FK
<BR>> > column3 null allowed FK
<BR>>
<BR>> It sounds like you have defined three separate FOREIGN keys but if
the
<BR>>
<BR>> PRIMARY KEY to table A is a composite (three columns) how can you
<BR>> declare tableB.column 1 as a FOREIGN KEY independent of the other
two
<BR>> columns? Please post a more complete schema, perhaps a dbschema
-ss
<BR>> for each of the two tables so that we can understand.
<BR>>
<BR> Sorry for misleading you
of course FK in tableB is composite</BLOCKQUOTE>
<BLOCKQUOTE TYPE=CITE>} Art S. Kagel</BLOCKQUOTE>
</HTML>
--------------82F7B33222C76A8FF5737C94--