Re: null or not null

Nils Myklebust wrote: > > :But what is really important about this is that by having a primary key > :defined as accepting Nulls, > > Then it's by definition *not* a primary key. > > : if you define a field in another table as a > :Foreign Key Referencing that field, that table will accept Null values > :in that field! What that means is that you will have foreign keys > :referencing non-existent values in the referenced table! > > You don't get into this kind of trouble if you understand primary > keys. I don't know if you can even define a primary key in ver. 5.x or > earlier engines. In 7.x no part of a primary key can be null however > you try to do it. > Remember in all this we are allways talking about primary (and foreign > keys) as defined in the schema. On early engines where primary keys > couldn't be defined one had problems and one would have only what > could be called logical primary keys or something like that. The only, > and appropriate, way to "define" them would be to spesify "not null" > on each column and create a unique key on it. Both are required to > have a resemblance of a primary key. > > :I'm sure that this must be a bug, since it runs completely counter to > :the whole purpose of Referential Constraints, and could set one up for > :some nasty referential integrity surprises. > > So there is no such bug in the versions I have. > > :If this is the case with your database engine, then this is a definite > :argument *against* allowing Nulls in Primary Key fields. I prefer No > :Nulls at all when possible with the default <space> primary key value > :entered in each table. > > Again: A primary key *by definition* disalows nulls. Remember a null > means value unknown - a primary key is a *value* that uniquly > identifies a row. A null in a primary key is a contradiction. > I think you're missing my point, or perhaps I'm not being clear. We have the dissonance of Theory vs. Reality. I'm addressing reality. In reality, you may define Primary Keys in an Informix database which by *schema definition* does indeed accept Nulls. And this is the issue I am trying to alert others to. It matters not what theory would dictate - this is real-world. Below is actual output from `dbschema`. As I said, this bug/feature depends on the version of the DB you have. But folks should be aware of this issue - test it out on your own platform to see if you're vulnerable. Notice that the field "csub" is defined as the Primary Key. Notice also that the field is defined as accepting Nulls (absence of "Not Null" in the schema). $ dbschema -d exhibits -t csub DBSCHEMA Schema Utility INFORMIX-SQL Version 5.00.UD1 Copyright (C) Informix Software, Inc., 1984-1991 Software Serial Number AAA#C390725 { TABLE "exbdba".csub row size = 77 number of columns = 4 index size = 13 } create table "exbdba".csub ( csub char(5), <----------------------- Primary Key accepts Nulls csub_desc char(60) not null, coa_category char(10) not null, cp_category char(2) not null, primary key (csub) constraint "exbdba".u669_60 <-- Primary Key is "csub" ); revoke all on "exbdba".csub from "public"; create unique index "exbdba".ix226_1 on "exbdba".csub (csub); $ Now, if you have another table that has a field defined as a foreign key referencing the "csub" field in the above table, that field will accept Null values! e.g: alter table accounts add constraint foreign key (c_code) references csub; 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. 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. One tool, as I mentioned, is to use Outer Joins. This will aid in revealing orphaned joins when they occur. It's being in the face of real-world situations like the above that have me leaning towards avoiding Nulls when possible. 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. --Cosmo