Home » SQL & PL/SQL » SQL & PL/SQL » create materialized view log with explicit constraint (merged) (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
create materialized view log with explicit constraint (merged) [message #679916] Mon, 13 April 2020 13:37 Go to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
how do you create a materialized view log with explicitly including the constraint name.

for example:
CREATE MATERIALIZED VIEW LOG ON hrms.SSP_inc_tba constraint SSP_INC_TBA_PK primary key (id) new values;
but the above returns an error:
ORA-12044: invalid CREATE MATERIALIZED VIEW LOG option


i know it is not the correct command. this below works but not the explicitly stating the constraints name.
CREATE MATERIALIZED VIEW LOG ON hrms.SSP_inc_tb WITH PRIMARY KEY INCLUDING NEW VALUES;

please advise. thank you.
create materialized view log with explicit constraint [message #679917 is a reply to message #679916] Mon, 13 April 2020 13:37 Go to previous messageGo to next message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
how do you create a materialized view log with explicitly including the constraint name.

for example:
CREATE MATERIALIZED VIEW LOG ON hrms.SSP_inc_tba constraint SSP_INC_TBA_PK primary key (id) new values;
but the above returns an error:
ORA-12044: invalid CREATE MATERIALIZED VIEW LOG option


i know it is not the correct command. this below works but not the explicitly stating the constraints name.
CREATE MATERIALIZED VIEW LOG ON hrms.SSP_inc_tb WITH PRIMARY KEY INCLUDING NEW VALUES;

please advise. thank you.
Re: create materialized view log with explicit constraint (merged) [message #679920 is a reply to message #679916] Mon, 13 April 2020 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It seems you misunderstood the "WITH PRIMARY KEY" clause.
It means the log includes the values of the primary key of the rows of the table that are modified, not adds a primary key to the log; so the name is meaningless as there is only one primary key on the table.

[Updated on: Mon, 13 April 2020 15:07]

Report message to a moderator

Re: create materialized view log with explicit constraint (merged) [message #679921 is a reply to message #679916] Mon, 13 April 2020 14:16 Go to previous message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
thanks so much.
Previous Topic: Filtering result from 2 PL/SQL UNION queries
Next Topic: Calculating the Difference of Timestamp in Min, Sec
Goto Forum:
  


Current Time: Mon Sep 28 23:51:00 CDT 2020