Create Primary Key on View

**Mauro** · Jun 27 '08, 05:20 PM

Re: Create Primary Key on View

Hi...
there are some restrictions on view constraints; "SQL Reference 9.2"
mentions:

/*
View Constraints
Oracle does not enforce view constraints. However, operations on views
are subject to the integrity constraints defined on the underlying
base tables. This means that you can enforce constraints on views
through constraints on base tables.

Restrictions on View Constraints
View constraints are a subset of table constraints and are subject to
the following restrictions:

You can specify only unique, primary key, and foreign key constraints
on views. However, you can define the view using the WITH CHECK OPTION
clause, which is equivalent to specifying a check constraint for the
view.
Because view constraints are not enforced directly, you cannot specify
INITIALLY DEFERRED or DEFERRABLE.
View constraints are supported only in DISABLE NOVALIDATE mode. You
must specify the keywords DISABLE NOVALIDATE when you declare the view
constraint, and you cannot specify any other mode.
You cannot specify the using_index_cla use, the exceptions_clau se
clause, or the ON DELETE clause of the references_clau se.
You cannot define view constraints on attributes of an object column.
*/

So, you can try using this statement:

CREATE OR REPLACE VIEW RPT_VW_WMN (
CARD, EC_D_CODE, EC_M_CODE, START_DATE, PR_NAME, PR_ID, ELIG,
ANNUALY, MONTHLY, PENDING,
CONSTRAINT MyView_pk PRIMARY KEY
(CARD, EC_D_CODE, EC_M_CODE, START_DATE, PR_NAME, PR_ID) DISABLE
NOVALIDATE
)
AS
SELECT 'DUMMY' CARD, A.EC_D_CODE, A.EC_M_CODE,
etc...

Bye.

androidsun@yaho o.com (js) wrote in message news:<23869fd0. 0310081500.21e0 d68b@posting.go ogle.com>...

I am trying to create a primary key constraint on a view in the
following statement. However, I got an error ORA-00907: missing right
parenthesis. If the CONSTRAINT clause is removed, then the view is
created fine. Does anyone know how to creat Primary Key Constraint
for a View? Thanks.
>
CREATE OR REPLACE VIEW RPT_VW_WMN (
CARD, EC_D_CODE, EC_M_CODE,
START_DATE, PR_NAME, PR_ID, ELIG, ANNUALY, MONTHLY, PENDING,
/* ORA-00907: missing right parenthesis error here */
CONSTRAINT MyView_pk PRIMARY KEY
(CARD, EC_D_CODE, EC_M_CODE, START_DATE, PR_NAME, PR_ID )
)
>
AS
SELECT 'DUMMY' CARD, A.EC_D_CODE, A.EC_M_CODE,
TO_DATE(TO_CHAR (A.EC_DATETIME, 'MONTH DD YYYY'),'MM/DD/YYYY')
- TO_CHAR(A.EC_DA TETIME,'DD') +1 AS START_DATE,
C.PR_NAME, C.PR_ID,
COUNT(DISTINCT A.PNT_ID) AS ELIG,
SUM(CASE WHEN A.PNT_ID IN
(SELECT PNT FROM WOM WHERE APTDATE BETWEEN
ADD_MONTHS(A.EC _DATETIME -
TO_CHAR(A.EC_DA TETIME,'DD')+1, -12) AND
A.EC_DATETIME - TO_CHAR(A.EC_DA TETIME,'DD')+1)
THEN 1 ELSE 0 END) AS ANNUALY,
SUM(CASE WHEN A.PNT_ID IN
(SELECT PNT FROM WOM WHERE APPTDATE BETWEEN
A.EC_DATETIME - TO_CHAR(A.EC_DA TETIME,'DD')+1 AND
ADD_MONTHS(A.EC _DATETIME -
TO_CHAR(A.EC_DA TETIME,'DD')+1, 1))
THEN 1 ELSE 0 END) AS MONTHLY,
SUM(CASE WHEN A.PNT_ID NOT IN
(SELECT PNTFROM WOM WHERE APPTDATE BETWEEN
ADD_MONTHS(A.EC _DATETIME -
TO_CHAR(A.EC_DA TETIME,'DD')+1, -12) AND
ADD_MONTHS(A.EC _DATETIME -
TO_CHAR(A.EC_DA TETIME,'DD')+1, 1))
THEN 1 ELSE 0 END) AS PENDING
FROM EC A, PR C
WHERE A.EC_DATETIME BETWEEN A.EC_DATETIME -
TO_CHAR(A.EC_DA TETIME,'DD')+1
AND ADD_MONTHS(A.EC _DATETIME - TO_CHAR(A.EC_DA TETIME,'DD')+1, 1)
- 1
AND RTRIM(A.PR_ID_C ODE) = C.PR_ID_CODE (+)
AND A.APT_STATUS_ID = 1
GROUP BY A.EC_D_CODE, A.EC_M_CODE,
TO_DATE(TO_CHAR (A.EC_DATETIME, 'MONTH DD YYYY'),'MM/DD/YYYY')
- TO_CHAR(A.EC_DA TETIME,'DD') +1 ,
C.PR_NAME, C.PR_ID
>
>
>
/*************** *************** *************** *************** *************** ****The
following is description from Oracle9i Application Developer's Guide -
Fundamentals Release 1 (9.0.1) for creating a view.
*************** *************** *************** *************** *************** ****/
>
CREATE [OR REPLACE] [[NO] FORCE] VIEW [schema .] view
[ ( { alias [column_constrai nt [column_constrai nt]...]
| table_or_view_c onstraint
}
[, { alias column_constrai nt [column_constrai nt]...
| table_or_view_c onstraint
}
]...
)
| object_view_cla use
]
AS subquery [subquery_restri ction_clause];
>
table_or_view_c onstraint::=
[CONSTRAINT constraint]
{ UNIQUE ( column [, column]... )
| PRIMARY KEY ( column [, column]... )
| FOREIGN KEY ( column [, column]... ) references_clau se
| CHECK ( condition )
}
constraint_stat e
>
constraint_stat e::=
[ [[NOT] DEFERRABLE] [INITIALLY { IMMEDIATE | DEFERRED }]
| [INITIALLY { IMMEDIATE | DEFERRED }] [[NOT] DEFERRABLE]
]
[ RELY | NORELY ] [using_index_cla use] [ ENABLE | DISABLE ]
[ VALIDATE | NOVALIDATE ] [exceptions_clau se]

Create Primary Key on View

Create Primary Key on View

Comment