1

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?

4
  • I don't know for certain that this is the case, but perhaps when there is not a primary key present in the table, Oracle automatically uses some type of automatically generated row id instead. Perhaps the ROWID pseudocolumn
    – Hannah Vernon
    Dec 3 '20 at 17:39
  • Can you add the actual table DDL and rows from DBA_CONSTRAINTS WHERE TABLE_NAME='WORKORDER'?
    – mustaccio
    Dec 3 '20 at 18:39
  • @mustaccio You'll have to excuse my ignorance. I'm not a dba, so I don't have access to DBA_CONSTRAINTS. But I do have access to ALL_CONSTRAINTS. I performed this query select * from all_constraints where table_name='WORKORDER' and owner = 'MAXIMO' . ... And I've shared it as a WITH/query on db<>fiddle: dbfiddle.uk/…. Is that what you were looking for?
    – User1973
    Dec 3 '20 at 20:41
  • @mustaccio Please bear in mind that the database is a COTS product from IBM. I didn't design the table and don't have much flexibility when it comes to altering it.
    – User1973
    Dec 3 '20 at 20:42
3

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
1
  • It would be interesting to see some documentation on what the criteria is for columns that are"good enough" to be primary keys for MV logs.
    – User1973
    Dec 9 '20 at 0:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.