Inconsistency in handling of additional not-null constraints on top of PK

[Bernhard S]

I create in a SRC (=source) schema a table as:

create table SRC.a_table_pk_nullable
(
  a_pk_01 number not null,
  a_pk_02 number not null,
  a_date  date
);
alter table SRC.a_table_pk_nullable
  add constraint pk primary key (A_PK_01, A_PK_02);

While in a TGT (=target) schema I create the table slightly different as:

create table TGT.a_table_pk_nullable
(
  a_pk_01 number, --no not-null-constraint in addition to PK
  a_pk_02 number, --no not-null-constraint in addition to PK
  a_date  date
);
alter table TGT.a_table_pk_nullable
  add constraint pk primary key (A_PK_01, A_PK_02);

Now when you compare both tables using PSD "Compare User Objects" you get "No differences found".

When you export the table using PSD "Export User Objects" you get for both, the SRC and TGT version equally:

prompt

prompt Creating table A_TABLE_PK_NULLABLE
prompt ==================================
prompt

create table A_TABLE_PK_NULLABLE
(
  a_pk_01 NUMBER not null,
  a_pk_02 NUMBER not null,
  a_date  DATE
)
;
alter table A_TABLE_PK_NULLABLE
  add constraint PK primary key (A_PK_01, A_PK_02);

As you can see the additional not-null-constraints get created in SRC and TGT version, even though they actually exist only on the SRC version!
It doesn't really matter in this current status, since the PK prevents NULLs on those columns anyway.
However everything changes once you drop the PK on both schemas:

alter table SRC.a_table_pk_nullable drop constraint PK cascade;
alter table TGT.a_table_pk_nullable drop constraint PK cascade;

Now when you compare SRC and TGT a difference is reported:

-----------------------------------------
--  Changed table a_table_pk_nullable  --
-----------------------------------------
-- Add/modify columns
alter table A_TABLE_PK_NULLABLE modify a_pk_01 not null;
alter table A_TABLE_PK_NULLABLE modify a_pk_02 not null;

When you export SRC and TGT tables again then, only the SRC version has the not-null-constraints now!

Conclusion:
In order to avoid such inconsistent behaviour, additional not-null-constraints should only get generated in addition to PKs by PSD based on a source table, if they really exist there.
In a comparison the difference of (non)-existing not-null-constraints in addition to PK should be shown, i.e. the first compare should have shouwn already the difference that was shown after dropping the PK.
It might make sense to make this difference optionally ignorable, as a different behaviour is not detectable right away.

 

[Marco Kalter]

The Primary Key constraint implicitly adds not null constraints to the key columns, because Oracle does not allow null values for primary key columns.

 

[Bernhard S]

An enabled PK itself guarentees "Not Null", but it doesn't do this via an additional not-null-constraint, as PSD creates it. You won't find any not-null-constraint in DBA_CONSTRAINTS, if you only create a PK. You can only see in DBA_TAB_COLUMNS, that the PK columsn have NULLABLE='N'. This however changes if you drop or disable the PK.
Compare with this one here:http://viralpatel.net/blogs/2010/02/understanding-primary-keypk-constraint-in-oracle.html

Also, if we have an enabled PK constraint, the corresponding column(s) will be ''NOT NULL'' Now if you drop/disable the PK constriant, the column(s) will be changed to the state in which they were before adding the PK constraint.

 

[Bernhard S]

So can you please fix this?

 

[Marco Kalter]

We'll check it out.

 

[Bernhard S]

Compare this also with the result from
dbms_metadata.get_ddl('TABLE' ,'A_TABLE_PK_NULLABLE')

You will only find explicit NOT NULL constraint defintions here, if they actually exist on the table. A PK alone without explicit NOT NULL constraints will not show any NOT NULL definitions on its PK columns in the metadata.
It works the same when you use EXPDP/IMPDP (Export/Import Data Pump).

Please adapt PSD behaviour to the result when using Oracle on-board means and don't create explicit NOT-NULL-Constraints like a rabbit pulled out of a hat where there is no such thing in the first place.