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

.
.
.