Able to create MV logs using PK — even though there is no PK

logsmaterialized-vieworacleoracle-19cprimary-key

I have a table that does not have a primary key:

enter image description here

I'm able to create MV logs on the table — without specifying whether to use the primary key or the rowid:

create materialized view log on maximo.workorder;

It looks like Oracle has chosen to create the MV logs using the primary key option (even though the table doesn't have a primary key):

enter image description here

I've tested the MV logs and they are working correctly. Any edits that I make to the WORKORDER table are showing up in the MV logs.

Why is it possible for Oracle to create the MV logs using the primary key option — even though the table doesn't have a primary key?

Edit:

Related question here: Create fast-refresh MV over dblink on table without PK?

Best Answer

A unique constraint on a NOT NULL column (which you have, as I checked this table in a database where Maximo was installed) is a good enough substitute for a primary key in this case.

SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create materialized view log on t1;
create materialized view log on t1
*
ERROR at line 1:
ORA-12014: table 'T1' does not contain a primary key constraint

SQL> alter table t1 add unique(c1);

Table altered.

SQL> create materialized view log on t1;
create materialized view log on t1
*
ERROR at line 1:
ORA-12014: table 'T1' does not contain a primary key constraint

SQL> alter table t1 modify c1 not null;

Table altered.

SQL> create materialized view log on t1;

Materialized view log created.

SQL> select master, primary_key from user_mview_logs;

MASTER                         PRI
------------------------------ ---
T1                             YES
Related Question