Re: null or not null

Cosmo Lee <cosmo@echonyc.com> wrote: I first want to say that anything I write below is not meant as critisism. Your errors are far too easy to commit for any negative critisism to have much value. Just think about the issues and the world might become simpler. :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. If there is such a "dissonance" the theory is wrong - allways. We are however discussing different things. Let's take the things you now say, which also contain some misunderstandings. :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. Only the real world can and does dictate things. The theory helps you understand it, and reality is the one and only final arbiter for the correctnes of your theories. (Aristotle - 2500 years ago.) It's a sad state of affairs that so many philosophers for so long has said differently, that most people for very natural reasons misses this simple point. The computing field (and many other human endavours) would have come much further if it hadn't been missed. :Below is actual output from `dbschema`. As I said, this bug/feature :depends on the version of the DB you have. I think it's a feature that is according to the SQL standard. I am not quite sure about the standard issue, but I do think it's a feature in all Informix databases, short of any that may have bugs that I am not aware of. : 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). This is a missunderstanding. As soon as you define one or more columns as the primary key in the schema those columns can *not* contain null's whether you say "not null" or not at the individual column level or not. If this is not the case in your version 5 datbase it's a clear defect. It does work this way in my 7.x versions. But do try to *insert* a row with null in csub in the csub table. This should give you an error and no data inserted (even if there is no such row in the table allready). As I said, if you can insert such a column you have found a defect in the engine. You may claim that this is a "theoretical" defect, but it's prety bad. If the defect is there it is one according to the SQL standard, and according to any sensible definition one can come up with of a primary key. But read on below about foreign key columns which is another issue. :$ 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! Yes, it allways will, unless you put in "not null" on this column. :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. The foreign key column will accept nulls whether there is a "not null" on csub.csub or not. : It's :not supposed to happen, but it *does*. That's the point. It is supposed to happen. I am quite sure the SQL standard says this is the way it should work. At least there is no problem with this, and I think it is quite important functionallity the way SQL works. If you don't want null's in the foreign key column you simply define it with "not null". That's a simple enough way to take care of that part - and reliable. An example: If you have a department and employee table you might want to be able to insert employees without assigning any value to the departement column initially. It's set to null. However when you do assign a value you want to make sure it's a leagal value, one that exists in the department table. Therefore you define a foreign key on the department column in the employee table. Even if you do, and with the above described functionality of foreign keys, you can still insert employees with null in the department column, but no values (remember null is no value) appart from those who a