Re: null or not null

In article <3366971D.42F9@echonyc.com>, Cosmo Lee <cosmo@echonyc.com> writes >Nils Myklebust wrote: > >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