Wednesday, 8 June 2016

Multi Row Based Constraints in Oracle

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> create unique index proj_name_idx on projects
  2  ( case when status = 'A' then name end )
  3  /
Index created.
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 
Materialized view log created.
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;
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 


No comments:

Post a Comment