0
Requirement from Client
As an advocate of enforcing as much integrity as possible declaratively, I am
disappointed that little has changed in that arena in 10G. CHECK constraints are still
limited to simple single-table, single-row checks, and triggers have to be used to
enforce any more complex rules.
Do you think Oracle will ever add multi-table check constraints or ASSERTIONS as defined
by ANSI SQL? Or has declarative integrity gone about as far as it is going to in Oracle?
and we said...
I'm asking around about the future of "SQL" as far as enhancments go like that (will
update when I get some feedback), but -- you are not limited to triggers (in fact, I
would avoid triggers as it is virtually IMPOSSIBLE to implement cross row/cross object
constraints with them!!!! at least correctly)
I look to database objects to enforce them - lets look at a couple of cases (i know,
these are not assertions, these are not domains -- they are working solutions using
existing features in a way "perhaps not intended")
o case: we need to have a rule that enforces unique names for "current" projects. That
is, we have a projects table, there are active projects and historical (completed)
projects. the project name for all active projects must be unique. historical projects
can have duplicates.
ops$tkyte@ORA9IR2> create table projects
2 ( id int primary key,
3 status varchar2(1) not null check (status in ('A','C')),
4 name varchar2(10)
5 )
6 /
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create unique index proj_name_idx on projects
2 ( case when status = 'A' then name end )
3 /
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into projects values ( 1, 'C', 'hello' );
1 row created.
ops$tkyte@ORA9IR2> insert into projects values ( 2, 'C', 'hello' );
1 row created.
ops$tkyte@ORA9IR2> insert into projects values ( 3, 'A', 'hello' );
1 row created.
ops$tkyte@ORA9IR2> insert into projects values ( 4, 'A', 'hello' );
insert into projects values ( 4, 'A', 'hello' )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.PROJ_NAME_IDX) violated
function based indexes can be used to implemented some interesting and complex rules
for conditional uniqueness
Some will say this is better solved via a data model change (eg: a projects table with
subtypes "active" and "completed" -- but sometimes this is just easier to have a single
physical table)
o case averages/mins/counts whatever "by something". refresh fast on commit MV's can be
useful. consider, we want between 1 and 6 employees/department:
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA9IR2> alter table emp add constraint emp_pk primary key(empno);
Table altered.
ops$tkyte@ORA9IR2> create materialized view log on emp with rowid, (deptno) including new
values;
Materialized view log created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno, count(*) cnt from emp group by deptno
5 /
Materialized view created.
ops$tkyte@ORA9IR2> alter table emp_mv
2 add constraint check_cnt
3 check ( cnt between 1 and 6 )
4 deferrable
5 /
Table altered.
ops$tkyte@ORA9IR2> insert into emp (empno,deptno) values ( 1000, 20 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> insert into emp (empno,deptno) values ( 1001, 20 );
1 row created.
ops$tkyte@ORA9IR2> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (OPS$TKYTE.CHECK_CNT) violated
the logic is to "materialize" the aggregation and add a check constraint to the
materialization