Home » SQL & PL/SQL » SQL & PL/SQL » how null values are stored
how null values are stored [message #378725] Wed, 31 December 2008 23:38 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Unique key constraint do not accept duplicate values,but why it's accepting morethan one null value?

I think any null value is not equal to other null value so that it's accepting morethan one null value.

But if we perform a query like this
select * from table_name where column_name is NULL;

we will get all null values what is the reason?
How oracle stores null values?Please help me.

[Updated on: Thu, 01 January 2009 00:29] by Moderator

Report message to a moderator

Re: how null values are stored [message #378729 is a reply to message #378725] Thu, 01 January 2009 00:17 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
I think that you will like reading Oracle Concepts book, where all these things are described. It is availabe with other Oracle documentation books e.g. online on http://tahiti.oracle.com/.

In short, uniqueness is enforced by creating (and using) unique index. As it does not contain NULL values, the NULL values may repeat in table. By the way, NULL value is not equal nor unequal to any other NULL value, it is undefined.

If you are curious how Oracle stores NULL values in table, use DUMP function.
SELECT DUMP(column_name) FROM table_name WHERE column_name is NULL;
Re: how null values are stored [message #378730 is a reply to message #378725] Thu, 01 January 2009 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
NULL means "don't know", is a "don't know" equals another "don't know"? The answer is I "don't know", isn't it? So it is not "true" and they are not equal.

Regards
Michel
Re: how null values are stored [message #378773 is a reply to message #378730] Thu, 01 January 2009 12:28 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Please ignore this message..

[Updated on: Thu, 01 January 2009 12:29]

Report message to a moderator

Re: how null values are stored [message #378775 is a reply to message #378773] Thu, 01 January 2009 12:31 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
DEFAULT means when you don't give a value.
Giving NULL is NOT not giving value, it is saying "I don't know" or "the value is not defined or meaningless for this row".

Regards
Michel


Quote:
Please ignore this message..

Too late, I already answered. Smile

[Updated on: Thu, 01 January 2009 12:32]

Report message to a moderator

Previous Topic: How to insert one column from one table to another?
Next Topic: DEFAULT value
Goto Forum:
  


Current Time: Wed Feb 12 16:40:41 CST 2025