Not_null_pk_oracle.sql

drop table test_pk purge

create table test_pk(
  n_pk  number not null,
  n_val number,
  constraint test_pk_pk primary key (n_pk))


create table test_reference(
  n_pk   number not null,
    constraint test_reference_pk primary key (n_pk),
  n_ref  number,
  CONSTRAINT fk_test_reference_ref
    FOREIGN KEY (n_ref)
    REFERENCES test_pk
)

alter table test_pk drop constraint test_pk_pk

alter table test_reference enable constraint fk_test_reference_ref

alter table test_reference add constraint fk_test_reference_ref(n_ref)

select * from all_constraints where table_NAME = 'TEST_PK'

select * from all_tab_columns where table_name = 'TEST_PK'

desc test_pk

Name  Null     Type   
----- -------- ------ 
N_PK  NOT NULL NUMBER 
N_VAL          NUMBER 

alter table test_pk modify (
n_pk null)

Table TEST_PK altered.

desc test_pk

Name  Null     Type   
----- -------- ------ 
N_PK  NOT NULL NUMBER 
N_VAL          NUMBER 

alter table test_pk modify (
n_pk null)

ORA-01451: column to be modified to NULL cannot be modified to NULL
01451. 00000 -  "column to be modified to NULL cannot be modified to NULL"
*Cause:    the column may already allow NULL values, the NOT NULL constraint
           is part of a primary key or check constraint.
*Action:   if a primary key or check constraint is enforcing the NOT NULL
           constraint, then drop that constraint.