Re: FW: Informix versus Oracle Spatial

Just tryed: create table nullix( a int, b char(10), primary key(a)); insert into nullix (b) values ('One '); ---- error does not allow null in col a ... So obstacle is dictating to have a PK which does not allow nulls in my table... this: create table nullix( a int unique , b char(10)); insert into nullix (b) values ('One '); insert into nullix (b) values ('One Dupl'); insert into nullix (b) values ('Two Dupl'); succeeds.... > Absolutely. null is absence of any information about a datum, > how can you know that it is a duplicate? It might well not be. sorry mate i disagree with you on that select * from nullix where a is null gives me the info. so sqlnul is sqlnul and also sqlnull in an index and is a unique value; to get the real answer i guess one has to goto the sql specs. -- haven't got them; any takers....??? Superboer. Noons schreef: > rkusenet apparently said,on my timestamp of 1/12/2005 10:11 PM: > > > my knowledge about Oracle is minimal. > > > > Could it be because Oracle does not index null values. > > Actually, it can. Quite easily. But it requires reading > a manual, something these "geniuses" are alergic to... > > > >> > >>you will probably tell me that the above is how it should work... > >>right?? > >>having multiple null values in a unique index.... > >> > > Absolutely. null is absence of any information about a datum, > how can you know that it is a duplicate? It might well not be. > > You are confusing unique index with primary key index and > unique key. There is a difference. Unnoticed in products that > don't have a clue what keys are for. But once again, it requires > reading a manual... > > -- > Cheers > Nuno Souto > in stormy Sydney, Australia > wizofoz2k@yahoo.com.au.nospam

"Superboer" <superboer7@planet.nl> wrote in message news:1133532431.734359.218240@z14g2000cwz.googlegroups.com... > Just tryed: > > create table nullix( a int, b char(10), primary key(a));> > insert into nullix (b) values ('One ');> > ---- error does not allow null in col a ... > > So obstacle is dictating to have a PK which does not allow nulls in my > table... > > this: > create table nullix( a int unique , b char(10));> > insert into nullix (b) values ('One '); > insert into nullix (b) values ('One Dupl'); > insert into nullix (b) values ('Two Dupl');> > succeeds.... > >> Absolutely. null is absence of any information about a datum, >> how can you know that it is a duplicate? It might well not be. > > sorry mate i disagree with you on that > > select * from nullix where a is null> > gives me the info. > > so sqlnul is sqlnul and also sqlnull in an index and is a unique value; > > to get the real answer i guess one has to goto the sql specs. > -- haven't got them; any takers....??? > > > Superboer. > .... not quite following all the comments, but i think this post is about whether NULL values are considered duplicate (equal) or unique AFAIK, according to the SQL Standard two NULL values by definition are never considered equal -- however SQL Server muddies things up (considerably) by not allowing multiple NULL values in a UNIQUE index. Oracle and others correctly allow multiple NULL values in a UNIQUE index ++ mcs

Superboer apparently said,on my timestamp of 3/12/2005 1:07 AM: > Just tryed: > > create table nullix( a int, b char(10), primary key(a));> > insert into nullix (b) values ('One ');> > ---- error does not allow null in col a ... > > So obstacle is dictating to have a PK which does not allow nulls in my > table... Try also function-based indexes with the function being nvl(column,constant). > > to get the real answer i guess one has to goto the sql specs. > -- haven't got them; any takers....??? > Exactly. Mark has already mentioned why it is so in Oracle: it follows the standard while others don't. Not a problem IMO, you can use the function-based indexes to make it behave like the others. Or use a true PK (which does not allow null values to start with) if you want to be really specious about standards. The important thing to consider is to think outside the box and use *all* features rather than expect vanilla Oracle to match other's behaviours: it won't. -- Cheers Nuno Souto in hot Sydney, Australia wizofoz2k@yahoo.com.au.nospam