Re: null or not null

David Williams <djw@smooth1.demon.co.uk> wrote: : :In article <3366971D.42F9@echonyc.com>, Cosmo Lee <cosmo@echonyc.com> :writes :>Nils Myklebust wrote: Just to make sure: I did not write any of the quoted material in David's post. On the contrary, I posted something similar to what David said. :> :>The field "c_code" will accept Nulls. This is because the referenced :>_Primary Key_ field "csub" has been defined as accepting Nulls. It's :>not supposed to happen, but it *does*. That's the point. Theory is of :>no help when the real world is not cooperating. : : But it should happen - it means that you a record I will not always : know the primary key. Thus the foreign key can also be unknown : since it is the same attribute just a different entity. : : E.g. people / job database : : person : person id <---------parimary key (allows nulls) : person name : : job : person id <---------foreign key (also allows nulls) : job title : organisation name :> : : Primary key NULL means I know Joe but not his person id. : Foreign Key NULL mean I know ACME Inc has a Sales Director : but not who they are (i.e. unknown person id) : :>The end result is that Referential Integrity is not enforced when it :>comes to Null values. Or depending on how you look at it, it's totally :>fine because the Primary Key accepts Null, so the Foreign Key should as :>well. :> : I follow the second tkought. : :>One tool, as I mentioned, is to use Outer Joins. This will aid in :>revealing orphaned joins when they occur. :> : Agreed. : :>It's being in the face of real-world situations like the above that have :>me leaning towards avoiding Nulls when possible. :> : Agreed, : :>The moral is, just because you define a Foreign Key Referencing a :>Primary Key in another table, don't assume that this protects that field :>from Null entries. :> : Agreed, that is what not null is for - do not expect a foreign key to : also have not null AUTOMATICALLY added to it. : :>--Cosmo : :-- :David Williams Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org