Primary Key doesnt treat NULL a value. But create statement with NULL succeeds.. Options

[Susil Kumar Naga...]

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 '?

[burleson]

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!

[SteveC]

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.