Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Primary Key doesnt treat NULL a value. But create statement with NULL succeeds..
Susil Kumar Naga...
post Feb 21 2012, 12:20 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 17-February 12
From: Chennai, India
Member No.: 46,779



Create table X(
var1 varchar2(20) null,
var2 varchar2(20) not null,
constraint pk_var1 primary key(var1)
);

We all know Primary key doesnt treat NULL as a value. But the above statement is fine to be executed without problem.
Is this something to be highlighted? or am i not right in understanding 'var1 varchar2(20) null '?



Go to the top of the page
 
+Quote Post
burleson
post Feb 21 2012, 09:48 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,801
Joined: 26-January 04
Member No.: 13



Hi Susil,

>> We all know Primary key doesnt treat NULL as a value.

Well, Oracle enforces the PK with a unique index which does not index on NULL values.

Please read, this will help:

http://www.dba-oracle.com/oracle_tips_ault_nulls_values.htm

See here, you can create an index on the NULLS:

http://www.dba-oracle.com/oracle_tips_null_idx.htm

***************************************
>> or am i not right in understanding 'var1 varchar2(20) null

This says that NULL values are allowed, nothing more.

Try inserting a NULL PK and see what happens!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
SteveC
post Feb 21 2012, 01:12 PM
Post #3


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



There is a huge difference between creating a table (with constraints in place) and actually inserting data into it. If there are no values in a table (because you just created it), then what is the issue? All the constraint means is that when data is present, with a PK defined, that the PK value is unique AND not null.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 18th December 2014 - 03:13 PM