Oracle is pretty strong at enforcing constraint.
Table for this blog post:
create table t(x number primary key, y number);
For instance if you
alter table t add check (y<1000); then Y will not be bigger than 1000, right?
SQL> insert into t values (1,2000); insert into t values (1,2000) Error at line 1 ORA-02290: check constraint (SCOTT.SYS_C0029609) violated
I believe this code to be unbreakable. If you have only SELECT and INSERT privilege on the table, you cannot bypass the constraint.
Let’s imagine some complex constraint. CHECK (sum(y) < 1000)
SQL> alter table t add check (sum(y) < 1000); alter table t add check (sum(y) < 1000) Error at line 1 ORA-00934: group function is not allowed here
Ok, clear enough I suppose, we cannot handle this complex constraint with a CHECK condition.
We could have some before trigger that fires an exception
CREATE TRIGGER tr BEFORE INSERT OR UPDATE ON T FOR EACH ROW WHEN (NEW.Y > 0) DECLARE s NUMBER; BEGIN SELECT SUM (y) INTO s FROM t; IF (s + :new.y >= 1000) THEN raise_application_error (-20001, 'SUM(Y) would exceed 1000'); END IF; END; /
Now the trigger will compute the sum and return an exception whenever it fails.
SQL> insert into t values (2, 600); 1 row created. SQL> insert into t values (3, 600); insert into t values (3, 600) * ERROR at line 1: ORA-20001: SUM(Y) would exceed 1000 ORA-06512: at "SCOTT.TR", line 8 ORA-04088: error during execution of trigger 'SCOTT.TR'
SQL> drop trigger tr; Trigger dropped. SQL> truncate table t; Table truncated.
But I am not good with triggers, and the triggers are as bad as their developers and have dark sides like mutating triggers and thelike.
As Tom Kyte mentioned in the comment, the code above is not
efficient effective if more than one user update the table at the same time
Another popular approach is to create a fast-refreshable-on-commit mview with a constraint.
Let’s see how this works.
create materialized view log on t with rowid, primary key (y) including new values; create materialized view mv refresh fast on commit as select sum(y) sum from t; alter table mv add check (sum < 1000);
The constraint is on the mview, so once you commit (and only at commit time), Oracle will try to refresh the mview.
SQL> insert into t values (4, 600); 1 row created. SQL> commit; Commit complete. SQL> insert into t values (5, 600); 1 row created. SQL> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (SCOTT.SYS_C0029631) violated SQL> select * from t; X Y ---------- ---------- 4 600
So far so good. The mechanism rollbacks the transaction in case of an ORA-12008. A bit similar to a DEFERABLE constraint.
But how safe is this after all? Oracle does not enforce anything on the table, it just fails on refresh…
Anything that does not fulfill the materialized view fast refresh requisites will also break the data integrity.
SQL> delete from t; 1 row deleted. SQL> commit; Commit complete. SQL> alter session enable parallel dml; Session altered. SQL> insert /*+PARALLEL*/ into t select 100+rownum, rownum*100 from dual connect by level<20; 19 rows created. SQL> commit; Commit complete. SQL> select sum(y) from t; SUM(Y) ---------- 19000 SQL> select staleness from user_mviews; STALENESS ------------------- UNUSABLE
Your data integrity is gone. By “breaking” the mview, with only SELECT, INSERT and ALTER SESSION privilege, you can now insert any data.
This is documented as
For both conventional DML changes and for direct-path INSERT operations, other conditions may restrict the eligibility of a materialized view for fast refresh.
Other operations like TRUNCATE may also prevent you from inserting fresh data
SQL> alter materialized view mv compile; Materialized view altered. SQL> exec dbms_mview.refresh('MV','COMPLETE'); PL/SQL procedure successfully completed. SQL> select * from mv; SUM ---------- SQL> insert into t values(1,1); 1 row created. SQL> commit; Commit complete. SQL> select * from mv; SUM ---------- 1 SQL> truncate table t; Table truncated. SQL> insert into t values(1,1); 1 row created. SQL> commit; commit * ERROR at line 1: ORA-32321: REFRESH FAST of "SCOTT"."MV" unsupported after detail table TRUNCATE