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_clause, the exceptions_clause clause, or the ON DELETE clause of the references_clause. 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_at_yahoo.com (js) wrote in message news:<23869fd0.0310081500.21e0d68b_at_posting.google.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_DATETIME,'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_DATETIME,'DD')+1, -12) AND
> A.EC_DATETIME - TO_CHAR(A.EC_DATETIME,'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_DATETIME,'DD')+1 AND
> ADD_MONTHS(A.EC_DATETIME -
> TO_CHAR(A.EC_DATETIME,'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_DATETIME,'DD')+1, -12) AND
> ADD_MONTHS(A.EC_DATETIME -
> TO_CHAR(A.EC_DATETIME,'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_DATETIME,'DD')+1
> AND ADD_MONTHS(A.EC_DATETIME - TO_CHAR(A.EC_DATETIME,'DD')+1, 1)
> - 1
> AND RTRIM(A.PR_ID_CODE) = 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_DATETIME,'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_constraint [column_constraint]...]
> | table_or_view_constraint
> }
> [, { alias column_constraint [column_constraint]...
> | table_or_view_constraint
> }
> ]...
> )
> | object_view_clause
> ]
> AS subquery [subquery_restriction_clause];
>
> table_or_view_constraint::=
> [CONSTRAINT constraint]
> { UNIQUE ( column [, column]... )
> | PRIMARY KEY ( column [, column]... )
> | FOREIGN KEY ( column [, column]... ) references_clause
> | CHECK ( condition )
> }
> constraint_state
>
> constraint_state::=
> [ [[NOT] DEFERRABLE] [INITIALLY { IMMEDIATE | DEFERRED }]
> | [INITIALLY { IMMEDIATE | DEFERRED }] [[NOT] DEFERRABLE]
> ]
> [ RELY | NORELY ] [using_index_clause] [ ENABLE | DISABLE ]
> [ VALIDATE | NOVALIDATE ] [exceptions_clause]
Received on Thu Oct 09 2003 - 09:56:46 CEST