Oracle Database Discussions

I need to revise the primary keys on a sizable number of tables, and I have run into some unpredictable behavior that is complicating my efforts.

For some (but not all) tables, when I drop the primary key constraint, the column involved becomes nullable:

07:49:08 SQL> select table_name, column_name, nullable from user_tab_cols 
07:49:23   2  where table_name = 'BUS_REGION_LANG' and column_name = 'BUS_REGION_LANG_ID';

TABLE_NAME                     COLUMN_NAME                    N
------------------------------ ------------------------------ -
BUS_REGION_LANG                BUS_REGION_LANG_ID             N

1 row selected.

Elapsed: 00:00:00.08
07:49:23 SQL> 
07:49:23 SQL> select pk.table_name, 
07:49:23   2         pk.constraint_name as pk_constraint_name, pkc.column_name as pk_column_name
07:49:23   3  from  user_constraints pk, user_cons_columns pkc
07:49:23   4  where pkc.table_name = pk.table_name
07:49:23   5  and pkc.constraint_name = pk.constraint_name
07:49:23   6  and pk.constraint_type = 'P'
07:49:23   7  and pk.table_name = 'BUS_REGION_LANG';

TABLE_NAME                     PK_CONSTRAINT_NAME             PK_COLUMN_NAME
------------------------------ ------------------------------ --------------------------------------------
BUS_REGION_LANG                PK_BUS_REGION_LANG             BUS_REGION_LANG_ID

1 row selected.

Elapsed: 00:00:00.09
07:49:23 SQL> ALTER TABLE BUS_REGION_LANG DROP PRIMARY KEY KEEP INDEX;

Table altered.

Elapsed: 00:00:00.07
07:49:34 SQL> select table_name, column_name, nullable from user_tab_cols 
07:49:52   2  where table_name = 'BUS_REGION_LANG' and column_name = 'BUS_REGION_LANG_ID';

TABLE_NAME                     COLUMN_NAME                    N
------------------------------ ------------------------------ -
BUS_REGION_LANG                BUS_REGION_LANG_ID             Y

1 row selected.

Elapsed: 00:00:00.08
07:49:52 SQL> 

Any idea what causes this?

Thanks,
Mike