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 ;