Search My Oracle Blog

Custom Search

09 January, 2010

Adding a PK Constraint sets the key column to NOT NULL

Adding a Primary Key constraint on a column that even if not defined as NOT NULL does set the column to be a NOT NULL !


SQL> create table TEST_PK_TABLE as select owner, object_name, object_id, created from dba_objects where object_id is not null;

Table created.

SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL> -- Rem we see above that the OBJECT_ID colum is NULLable
SQL>
SQL> create unique index TEST_PK_INDEX on TEST_PK_TABLE(object_id);

Index created.

SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL> -- Rem we see above that the OBJECT_ID colum is NULLable
SQL>
SQL> alter table TEST_PK_TABLE add constraint TEST_PK_INDEX primary key (object_id);

Table altered.

SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
CREATED DATE

SQL> -- Rem the OBJECT_ID column is now a NOT NULL !



What if the Column did contain one or more NULLs ? The Primary Key constraint definition would fail.


SQL> drop table TEST_PK_TABLE;

Table dropped.

SQL> create table TEST_PK_TABLE as select owner, object_name, object_id, created from dba_objects;

Table created.

SQL> select count(*) from TEST_PK_TABLE where object_id is null;

COUNT(*)
----------
1

SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL> -- Rem we see above that the OBJECT_ID colum is NULLable
SQL>
SQL> create unique index TEST_PK_INDEX on TEST_PK_TABLE(object_id);

Index created.

SQL> -- Rem a Unique Index can be defined even with NULLs
SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL> -- Rem we see above that the OBJECT_ID colum is NULLable
SQL>
SQL> alter table TEST_PK_TABLE add constraint TEST_PK_INDEX primary key (object_id);
alter table TEST_PK_TABLE add constraint TEST_PK_INDEX primary key (object_id)
*
ERROR at line 1:
ORA-01449: column contains NULL values; cannot alter to NOT NULL


SQL>
SQL> desc TEST_PK_TABLE;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL>


Defining the Constraint as a Disabled constraint also, obviously, doesn't touch the column.


SQL> alter table TEST_PK_TABLE add constraint TEST_PK_INDEX primary key (object_id) disable;

Table altered.

SQL> desc TEST_PK_TABLE
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
CREATED DATE

SQL>





Note : The above tests have been done in 10.2.0.4

.
.
.

2 comments:

Caner said...

Hi,
This is the nature of a PK constraint. PK constraints ensure that the columns cant have dublicate and null values.

Hemant K Chitale said...

Yes. A Primary Key cannot be defined for a NULLable column. What I am pointing out is that even if you have defined a NULLable column, Oracle takes the extra effort to convert the column to a NOT NULL column -- after verifying that there are no duplicates and nulls.
Normally it is better schema design to explicitly create the column as a NOT NULL column up-front (or in the case the column has to be temporarily NULLable because of data transforms and loads that are being executed, to explicitly ALTER TABLE MODIFY to define the column as a NOT NULL yourself).

Hemant

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com