RE: Referential Constraint

Mr/Ms m00n321 wrote: > What part is a bug? Nulls should not be present in referential > columns. By > definition, the data must be present in the controlling tables primary > index > which must be unique and, unless RI is a hobby, won't contain nulls. > > > Well, that was a great help! :-) Holiday blues? Your use of "referential columns" is ambiguous. By definition, "referenced" columns cannot contain null values, but "referencing" columns can. And I can think of several real-life tables where the foreign key columns (ie, the referencing columns) can naturally contain nulls. To get back to the point in my original post, I've since found out that the "insert" statement requires the word NULL to accept a null value in a Char field, whereas for a numeric field an empty string is also accepted as null. The "load" statement, on the other hand, considers two consecutive delimiters in the data file as a null value. Alanoly Andrews.

> Your use of "referential columns" is ambiguous. By definition, >"referenced" > columns cannot contain null values, but "referencing" columns >can. And I > can think of several real-life tables where the foreign key >columns (ie, the > referencing columns) can naturally contain nulls. My use of 'referential columns' was to remove any distinction between referenced and referencing as far as nulls are concerned. If a referencing column is allowed to contain a null, then null must be a legitimate value in the referenced column, by definition. Having a primary key value of null or 'empty' would require special handling of queries against the table, such as precluding the '!= value' from being executed due the null being included in the result set. But then having a primary key of {I don't know} or {Not Important/Required} doesn't make much sense in a design. If the referencing table is a 'Work In Process', such as the validation/translation of incoming data, and the subject piece of the data may be invalid or not supplied, then the WIP must be an intermediate stop for the data, not the final home. This would allow for an index on the data without a true referential link. When the data is complete/corrected it would be moved from the WIP to the destination table, which would have RI. The tables and RI should help ensure the data is valid, not get in the way.

M00n321 wrote: > > > Your use of "referential columns" is ambiguous. By definition, > >"referenced" > > columns cannot contain null values, but "referencing" columns > >can. And I > > can think of several real-life tables where the foreign key > >columns (ie, the > > referencing columns) can naturally contain nulls. > > My use of 'referential columns' was to remove any distinction between > referenced and referencing as far as nulls are concerned. If a referencing > column is allowed to contain a null, then null must be a legitimate value in > the referenced column, by definition. No, you cannot have a null in a primary key but you may have a null in a foreign key. That is the way you get your mandatory and optional participation conditions in the entity relationship model to work in SQL. The outer join exploits this possibility of a null foreign key. If you are avoiding nulls in foreign keys because of this misunderstanding you may be giving yourself problems. > Having a primary key value of null or > 'empty' would require special handling of queries against the table, such as > precluding the '!= value' from being executed due the null being included in > the result set. But then having a primary key of {I don't know} or {Not > Important/Required} doesn't make much sense in a design. > If the referencing table is a 'Work In Process', such as the > validation/translation of incoming data, and the subject piece of the data may > be invalid or not supplied, then the WIP must be an intermediate stop for the > data, not the final home. This would allow for an index on the data without a > true referential link. When the data is complete/corrected it would be moved > from the WIP to the destination table, which would have RI. The tables and RI > should help ensure the data is valid, not get in the way. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 -- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/

>No, you cannot have a null in a primary key but you may have a null in a >foreign key. That is the way you get your mandatory and optional >participation conditions in the entity relationship model to work in >SQL. I guess it depends on your need to incorporate 'maybe' or 'when its not too much trouble' into a database design. I don't use 'maybe' in a data design, and since I write code that processes more than a few rows I don't use 'outer' either. Investigate just how inefficient 'outer' is some time.

M00n321 wrote: > > >No, you cannot have a null in a primary key but you may have a null in a > >foreign key. That is the way you get your mandatory and optional > >participation conditions in the entity relationship model to work in > >SQL. > > I guess it depends on your need to incorporate 'maybe' or 'when its not too > much trouble' into a database design. I don't use 'maybe' in a data design, > and since I write code that processes more than a few rows I don't use 'outer' > either. Investigate just how inefficient 'outer' is some time. I have no complaints about the performance of outer joins in my application. My entities have a huge number of attributes which are foreign keys of code tables. It is normal for a random assortment of them to be genuinely unknown and these are represented as nulls. Presumably, this SQL facility was incorporated for those out here in the real world who have to deal with such problems. The alternative is to fill the referenced code tables with tuples describing nothing. To allow consistent querying you need to ensure that the same code is used for every "nothing" (to avoid confusing the users). This can be problematic. If I recall correctly, early versions of IBM's DB/2 did not have outer joins so you had to use some slow union selects. Later versions, of course, have corrected this deficiency. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 -- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/

>I have no complaints about the performance of outer joins in my >application. > >My entities have a huge number of attributes which are foreign keys of >code tables. It is normal for a random assortment of them to be >genuinely unknown and these are represented as nulls. Presumably, this >SQL facility was incorporated for those out here in the real world who >have to deal with such problems. > >The alternative is to fill the referenced code tables with tuples >describing nothing. To allow consistent querying you need to ensure that >the same code is used for every "nothing" (to avoid confusing the >users). This can be problematic. > >If I recall correctly, early versions of IBM's DB/2 did not have outer >joins so you had to use some slow union selects. Later versions, of >course, have corrected this deficiency. > I am in the 'real world', and still manage to work with a data design that doesn't contradict itself. The words required, correct, complete, and their variations do have meaning. The use for RI in my real world is to maintain data integrity, not a mechanism for getting around it. The point on 'outer' may be subjective. Given that the RI in place in my environment allows for a direct join the degradation of the outer may be more visible.