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, 2020 at 17:39
  • Can you add the actual table DDL and rows from DBA_CONSTRAINTS WHERE TABLE_NAME='WORKORDER'?
    – mustaccio
    Dec 3, 2020 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?
    – User1974
    Dec 3, 2020 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.
    – User1974
    Dec 3, 2020 at 20:42

1 Answer 1

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.
    – User1974
    Dec 9, 2020 at 0:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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