Home » SQL & PL/SQL » SQL & PL/SQL » diff on numeric null and char null in unique constraint
diff on numeric null and char null in unique constraint [message #257741] Thu, 09 August 2007 02:01 Go to next message
anuchowdary.k
Messages: 6
Registered: August 2007
Location: bangalore
Junior Member

Hi,
can any one explain the diff between numeric null and char null in unique key constraint.
Thanku
Re: diff on numeric null and char null in unique constraint [message #257750 is a reply to message #257741] Thu, 09 August 2007 02:10 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this out
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements005.htm#i59110
Re: diff on numeric null and char null in unique constraint [message #257756 is a reply to message #257750] Thu, 09 August 2007 02:19 Go to previous messageGo to next message
anuchowdary.k
Messages: 6
Registered: August 2007
Location: bangalore
Junior Member

Thank u for gave me reply.But i want how it stores the null values in memory.Like in one table one column is declared as unique. Unique key accepts null values also.if that column is of datatype numeric how it will take and if it is of char what it will do? I think u got my doubt.
Thank u
Re: diff on numeric null and char null in unique constraint [message #257759 is a reply to message #257756] Thu, 09 August 2007 02:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think you didn't get the explanation for null. As the word null stands it is void, it is nothing, it won't make any difference whether it is a character, number, date, long, blob, clob .... A Null is a Null. That's the reason a Null is included in a unique constraint because it has no value. It is like infinity in mathematics. And moreover as far as i know Null is specific to RDBMS. When it is outside RDBMS we can identify Null using Indicator variables, which will be populated while making calls to your database provided you code for it.

Also search for it in google. You may find loads of answers about this topic.
Re: diff on numeric null and char null in unique constraint [message #257773 is a reply to message #257759] Thu, 09 August 2007 02:44 Go to previous messageGo to next message
anuchowdary.k
Messages: 6
Registered: August 2007
Location: bangalore
Junior Member

Thanku. I got ur answer .But in sql one NULL is neither equal nor inequal to another NULL .so it should store these two NULLs differently.can u clarify.
thanku.
Re: diff on numeric null and char null in unique constraint [message #257778 is a reply to message #257773] Thu, 09 August 2007 02:51 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Can you please try to use real language and not IM speak?

Also, the definition that Null is alwas Not Equal to another Null is just a definition of how it should be on the logic level.

It has nothing to do with how that Null is stored internally in the database.
Re: diff on numeric null and char null in unique constraint [message #257940 is a reply to message #257741] Thu, 09 August 2007 11:44 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
When you have defined a column to be unique, it means unique EXCEPT for null columns. The null rows are not even in the unique index. Null will only be meaningful if the column is defined as UNIQUE and NOT NULL, in which case every row must be unique and not null.
Re: diff on numeric null and char null in unique constraint [message #257961 is a reply to message #257741] Thu, 09 August 2007 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
------------------------------------------
Ode to Null
 
It is neither a mountain nor a creek; 
It is neither Latin nor Greek; 
It is neither strong nor weak; 
It is neither courageous nor meek. 
 
It is neither nothing nor something; 
It is neither a good thing nor a bad thing; 
It is neither a big thing nor a small thing; 
But it is something we have to deal with among all things. 
 
It is neither a lender nor a borrower; 
It is neither a zero nor a blank; 
It is neither here nor there; 
But, in programming, it is everywhere. 
 
Oh! NULL, Oh! NULL! 
You are neither a push nor a pull; 
You always make us mull 
Ov'r your dangerous effects, Oh! NULL! 
  
Ram Srinivasan 21-Feb-2000
 
Re: diff on numeric null and char null in unique constraint [message #257965 is a reply to message #257961] Thu, 09 August 2007 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good one! ./fa/2115/0/

Regards
Michel
Re: diff on numeric null and char null in unique constraint [message #257977 is a reply to message #257741] Thu, 09 August 2007 13:05 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Permission to share that with colleagues ?

Very well done
Re: diff on numeric null and char null in unique constraint [message #257980 is a reply to message #257741] Thu, 09 August 2007 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
The author, Ram Srinivasan, posted it to the ORACLE-L a few days ago which is how I got it.
I made sure he was named in my post so as NOT to take credit for his work.
There has been a recent thread on ORACLE-L concerning NULL, too.
ORACLE-L has a searchable archive.
You don't need my permission to share it.
Re: diff on numeric null and char null in unique constraint [message #257993 is a reply to message #257977] Thu, 09 August 2007 14:26 Go to previous messageGo to next message
srinivasanram2004
Messages: 2
Registered: August 2007
Junior Member
MarcL wrote on Thu, 09 August 2007 13:05
Permission to share that with colleagues ?

Very well done



No problem. You can share it with any one. In my old work place, one guy got it framed and hung it in his office.

Ram Srinivasan
Re: diff on numeric null and char null in unique constraint [message #258043 is a reply to message #257993] Thu, 09 August 2007 23:09 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, I will do it too. ./fa/1581/0/

Regards
Michel
Previous Topic: DBMS_STATS and stored procedures to recompile or not?
Next Topic: Tree Query Output
Goto Forum:
  


Current Time: Sun Dec 04 14:48:46 CST 2016

Total time taken to generate the page: 0.11896 seconds