Materialized Views

Constraints

System Generated Constraints

When a materialized view is created Oracle may add system generated constraints to its underlying table (i.e. the table containing the results of the query, not to be confused with a base table). In the following example note how Oracle automatically adds a primary key constraint to the table called "MV", which is part of the materialized view also called "MV".

create materialized view mv
  as select key, val from t
;

column constraint_name format a20
column constraint_type format a15
column index_name      format a15

select constraint_name, constraint_type, index_name
from   user_constraints
where  TABLE_NAME = 'MV' ;
 
CONSTRAINT_NAME      CONSTRAINT_TYPE INDEX_NAME
-------------------- --------------- ---------------
SYS_C0019948         P               SYS_C0019948
 

In the next example Oracle automatically adds a check constraint.

drop materialized view mv ;

describe t2
 Name                                         Null?    Type
 -------------------------------------------- -------- ------------------------------
 KEY                                          NOT NULL NUMBER
 T_KEY                                        NOT NULL NUMBER
 AMT                                          NOT NULL NUMBER


create materialized view log on t2
  with primary key, rowid, sequence ( t_key, amt )
  including new values
;

create materialized view mv
  refresh fast on commit
  as
    select t_key, count(*) row_count
    from t2
    group by t_key
;

column search_condition format a30

select constraint_name, constraint_type, search_condition
from   user_constraints
where  table_name = 'MV' ;
 
CONSTRAINT_NAME      CONSTRAINT_TYPE SEARCH_CONDITION
-------------------- --------------- ------------------------------
SYS_C0019949         C               "T_KEY" IS NOT NULL
 

Adding Your Own Constraints

If necessary we can create our own constraints on materialized view tables in addition to the ones Oracle may add. When the materialized view is in ON COMMIT mode these constraints effectively constrain the materialized view's base tables. Let's see this in action by creating a check constraint on MV.

select * from t2 ;
 
       KEY      T_KEY        AMT
---------- ---------- ----------
        10          1        100
        20          1        300
        30          1        200
        40          2        250
        50          2        150
 
alter table mv -- note we used "alter table" here
  add CONSTRAINT MY_CONSTRAINT CHECK ( ROW_COUNT <= 3 ) DEFERRABLE
;

select constraint_name, constraint_type, search_condition
from   user_constraints
where  table_name = 'MV' ;
 
CONSTRAINT_NAME      CONSTRAINT_TYPE SEARCH_CONDITION
-------------------- --------------- ------------------------------
SYS_C0019949         C               "T_KEY" IS NOT NULL
MY_CONSTRAINT        C                ROW_COUNT <= 3
 

Now any attempt to create more than 3 rows per group in table T2 will generate an error at commit time.

insert into T2 values ( 5, 1, 500 );
commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.MY_CONSTRAINT) violated


 

Implementing multirow validation rules such as this one properly is not possible using check constraints on regular tables. Implementing them using triggers can be difficult if not impossible. With materialized views they are declared using a few lines of code and are virtually bullet proof when applied correctly. We will learn more about this powerful multirow validation approach in a future SQL Snippets tutorial so stay tuned! In the mean time Ask Tom "Declarative Integrity" has some good information on the subject.

Gotcha

When we created MY_CONSTRAINT above we use an ALTER TABLE command. Curiously enough an ALTER MATERIALIZED VIEW command would have worked too.

ALTER MATERIALIZED VIEW mv
  add constraint my_second_constraint check ( row_count < 4 ) deferrable
;

select constraint_name, constraint_type, search_condition
from   user_constraints
where  table_name = 'MV' ;
 
CONSTRAINT_NAME      CONSTRAINT_TYPE SEARCH_CONDITION
-------------------- --------------- ------------------------------
SYS_C0019949         C               "T_KEY" IS NOT NULL
MY_CONSTRAINT        C                ROW_COUNT <= 3
MY_SECOND_CONSTRAINT C                row_count < 4
 

The Oracle manual page for ALTER MATERIALIZED VIEW however does not indicate that constraints can be added this way. Until the documentation says this is legal it is best to use ALTER TABLE.

Cleanup

drop materialized view mv ;

drop materialized view log on t2 ;
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12896.html]SQL Snippets: Materialized Views - Constraints[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12896.html">SQL Snippets: Materialized Views - Constraints</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Materialized Views - Constraints
  • URL (href): http://www.sqlsnippets.com/en/topic-12896.html