Oracle Database – ORA 12014: table does not contain a primary key constraint

ORA-12014: table does not contain a primary key constraint

While creating a Materialized View (MVIEW) in the Oracle database, I hit the above error. It seems kinda strange to me. I did a oerr command to try and find out more information.

[oracle@localhost ~]$ oerr ora 12014
12014, 00000, "table '%s' does not contain a primary key constraint"
// *Cause: The CREATE MATERIALIZED VIEW LOG command was issued with the
// WITH PRIMARY KEY option and the master table did not contain
// a primary key constraint or the constraint was disabled.
// *Action: Reissue the command using only the WITH ROWID option, create a
// primary key constraint on the master table, or enable an existing
// primary key constraint.

Table that I wanted to create the MVIEW on

SQL> desc cust_id_sales_aggr
 Name Null? Type
 -------------------------------- -------- ------------------------
 CUST_ID NOT NULL NUMBER
 DOLLAR_SALES NUMBER
 CNT_DOLLARS NUMBER
 CNT NUMBER

How I created the MVIEW LOG

CREATE MATERIALIZED VIEW LOG ON cust_id_sales_aggr WITH ROWID
(CUST_ID,DOLLAR_SALES,CNT_DOLLARS,CNT)
INCLUDING NEW VALUES;

How I created the MVIEW

CREATE MATERIALIZED VIEW cust_id_sales_aggr_mv
PCTFREE 0 TABLESPACE users
STORAGE (INITIAL 8M)
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS SELECT cust_id,dollar_sales,cnt_dollars,cnt FROM cust_id_sales_aggr;

The error from the above MVIEW creation command.

ERROR at line 7:
ORA-12014: table 'CUST_ID_SALES_AGGR' does not contain a primary key constraint

How to resolve the error (Obvious from the oerr output)

  1. Create PK on the source table (CUST_ID_SALES_AGGR)
  2. While creating the MVIEW, use the option with ROWID.

I chose option #1 🙂

CREATE MATERIALIZED VIEW cust_id_sales_aggr_mv
PCTFREE 0 TABLESPACE users
STORAGE (INITIAL 8M)
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH ROWID
ENABLE QUERY REWRITE
AS SELECT cust_id,dollar_sales,cnt_dollars,cnt FROM cust_id_sales_aggr;

Regard,
Wei Shan

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s