set constraints all default
Logan Palanisamy, July 09, 2003 - 8:45 pm UTC
Tom,
Excellent explanation.
I wanted to put back the constraints to their default state after setting them to either "set constraints all deferred" or "set constraints all immediate".
So I tried "set constraints all default". It didn't work. I had to use the "alter session set constraints=default" to put them back to the default state.
Looks like a commit also put the constraints back to the their default state.
Nevertheless, what could be the reason for not having a construct like "set constraints all default"
when a construct like
"alter session set constraints=default" is available?
Why this inconsistency vis a vis
"set constraints all {immediate|deferred}" and
"alter session set constraints={immediate|deferred|default}"
Just a little curious. That's all.
Here is my demo using the same example.
SQL> set constraints all default;
set constraints all default
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> insert into t values ( -1,1);
insert into t values ( -1,1)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_X) violated
SQL> insert into t values ( 1,-1);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SCOTT.CHECK_Y) violated
SQL>
SQL> alter session set constraints = default;
Session altered.
SQL> insert into t values ( -1,1);
insert into t values ( -1,1)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_X) violated
SQL> insert into t values ( 1,-1);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SCOTT.CHECK_Y) violated
SQL>
July 10, 2003 - 7:38 am UTC
well, it would be a strange situation to be in the of such a facility.
consider that the SET CONSTRAINT command lives for the life of a transaction only.
that it is somewhat assumed that it would be the first, or near the first, instruction of a transaction (this transaction will run in IMMEDIATE MODE, or DEFERRED MODE).
that default is the de-facto default mode -- there is no reason to set to default, you are already there.
Then it makes sense. the ALTER SESSION command is longer lived -- it lives over multiple transactions (for the session) but can be overridden on a transaction by transaction basis.
Integrity constraints
Moiz, July 10, 2003 - 5:10 am UTC
Hi Tom ,
Execellent Explanation!!
I have a table with no data and now in one Existing column,I am adding NOT NULL constraints with deferrable initially deferred constraint. and it gives me error.
Pls help me out...
Thanx in Advance..
July 10, 2003 - 9:44 am UTC
any error in particular, how about this -- give us a CLEAR concise, yet complete example..... then maybe we can comment.
Dave, July 10, 2003 - 9:47 am UTC
I believe that NOT NULL constraints are not deferrable
July 10, 2003 - 10:27 am UTC
they are:
ops$tkyte@ORA920> create table t ( x number constraint x_not_null not null deferrable )
2 ;
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( null );
insert into t values ( null )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.X_NOT_NULL) violated
ops$tkyte@ORA920> set constraints all deferred;
Constraint set.
ops$tkyte@ORA920> insert into t values ( null );
1 row created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.X_NOT_NULL) violated
Chuck Jolley, July 10, 2003 - 11:29 am UTC
Tom,
What would be a real world reason for using defered constraints?
Eventualy, the code has to create the parent or replace the null or foreign key, etc.
Is it just a matter of making coding easier?
In our case, we do a lot of batch type processing and in those processes it seems like this would just cause a lot of needless work for the server if there was a constraint violation somewhere in the batch.
chuck
July 10, 2003 - 11:36 am UTC
snapshots (materialized views) use them in order to perform their refreshes, during the refresh - RI can be violated, constraints out of wack -- but by the time "commit" comes around everything is OK.
you can use them to facilitate an update cascade -- set the fkeys to be deferrable and you set:
set constraints all deferred;
update parent
update children
set constraints all immediate <<== fails if there are any orphans, you can fix
that
commit; <<=== fails and rolls BACK the work if there are any orphans
You can use them in a variety of multi-statement transactions that need to violate integrity during the course of the transaction -- but end up with everything "as it should be"
Deffered constraint
Moiz, July 15, 2003 - 5:37 am UTC
Hi Tom,
SQL>
1 create table m_signature
2 ( Br_code varchar2(2) not null,
3 ac_no number(6) not null,
4* ac_sign blob)
SQL> /
Table created.
THis is the requirement like:
Make Ac_sign column as NOT NULL and that constraint should be DEFERRABLE and INITIALLY DEFERRED. This is required as this table is to be used in REPLICATION and according to Metalink, the BLOB columns should have DEFERRABLE NOT NULL constraint else the table always needs COMPLETE REFRESH.
The syntax required like: (This is not taking place)
SQL> alter table m_signature
2 add (constraint nn_sign not null(ac_sign) deferrable initally deferred)
3 /
add (constraint nn_sign not null(ac_sign) deferrable initally deferred)
*
ERROR at line 2:
ORA-00904: invalid column name
So , I tried it with other way also ..
SQL> ed
Wrote file afiedt.buf
1 alter table m_signature
2* modify (ac_sign blob constraint nn_sign not null deferrable initally deferred)
SQL> /
modify (ac_sign blob constraint nn_sign not null deferrable initally deferred)
*
ERROR at line 2:
ORA-02253: constraint specification not allowed here
Pls , Help me out.
Moiz.
July 15, 2003 - 9:52 am UTC
ops$tkyte@ORA920LAP> create table t ( x int, y blob );
Table created.
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter table t
2 add constraint check_y_is_not_null
3 check (y is not null)
4 deferrable initially deferred;
Table altered.
you cannot specify the NOT NULL constraint out of line (not in the create table) that way -- so the check constraint, which does the same thing, is used.
Parag, July 16, 2003 - 3:15 am UTC
Excellent!!! Just small query ->
Are deferrable constraints by default are of type initially deferred ?
thx
July 16, 2003 - 9:23 am UTC
documentation or simple trivial easy for you to have tried test says.....
initially immediate
ops$tkyte@ORA920LAP> create table t ( x int check (x>0) deferrable );
Table created.
ops$tkyte@ORA920LAP> insert into t values ( -1 );
insert into t values ( -1 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C005785) violated
ops$tkyte@ORA920LAP> set constraints all deferred;
Constraint set.
ops$tkyte@ORA920LAP> insert into t values ( -1 );
1 row created.
parag, July 17, 2003 - 3:08 am UTC
Thanks TOM for helping us by excellent logical answers.
Once again a query to ask you!!!!
if deferrable constraints by default
initially immediate it means constraint has to execute at the end of the statement execution then how deferrable constraint is working in inserting of "child parent records"
in a transaction ?
( bcoz in child parent insert we want deferrable constraints to be checked only at commit i.e transaction ends it means in such situations we have to specially set deferrable constraints initially deferred when we have to insert child - parent insert correct ?)
thx
July 17, 2003 - 10:29 am UTC
a deferrable constraint that is initially immediate works NO DIFFERENTLY then a non-deferrable constraint.
If you want to check at the end of the transaction, instead of statement you would either
a) set constraint all | constraint_name deferred
b) create them initially deferred.
Constraints on multiple rows
A reader, July 23, 2003 - 3:28 pm UTC
Hi Tom,
Assume I have a table
T (id number, seq number, percentage number);
PK is (id, seq).
How could I create a check constraint for the business rule of "the total of percentages of each id always equals 100"?
For example:
(id, seq, percentage)
1, 1, 100
2, 1, 40
2, 2, 60
...
Please help. Thanks.
July 23, 2003 - 9:25 pm UTC
here is a short excerpt from my book coming out next month (aug 2003) - it shows how to do this -- but it'll require another table for you -- one that contains the total pct by id -- maintained via a trigger:
To solve the problem, we can use serialization and server-enforced integrity. There are a couple of ways to do this. Here, we'll use the DEPT table, where we'll maintain an aggregate column EMP_COUNT. It looks like this:
ops$tkyte@ORA920> alter table dept
2 add emp_count number
3 constraint must_be_between_3_8
4 check(emp_count between 3 and 8 OR emp_count = 0)
5 deferrable initially deferred;
Table altered.
ops$tkyte@ORA920> update dept
2 set emp_count = (select count(*)
3 from emp
4 where emp.deptno = dept.deptno )
5 /
4 rows updated.
ops$tkyte@ORA920> alter table dept
2 modify emp_count NOT NULL;
Table altered.
Now, we have an aggregate column in the DEPT table that we will use to maintain a count of employees. Additionally, this column has a declarative check constraint on it that verifies the count of employees is either 0 or between 3 and 8, as specified in the rule. Lastly, we made this constraint deferrable initially deferred, meaning it will not be validated until we commit by default. The reason for doing that is to permit multistatement transactions to execute without error. For example, we could update a row setting the DEPTNO from 20 to 30, reducing the number of employees in DEPTNO 20 to 2 but immediately follow that with an update of another row from DEPTNO 30 to 20, increasing the number of employees back to 3. If the constraint were validated after each and every statement, this sort of logic would fail.
Now, we need a trigger to maintain that value:
ops$tkyte@ORA920> create trigger emp_dept_cnt_trigger
2 after insert or update or delete on emp
3 for each row
4 begin
5 if ( updating and :old.deptno = :new.deptno )
6 then
7 return; -- no change
8 end if;
9 if ( inserting or updating )
10 then
11 update dept set emp_count = emp_count+1
12 where deptno = :new.deptno;
13 end if;
14 if ( updating or deleting )
15 then
16 update dept set emp_count = emp_count-1
17 where deptno = :old.deptno;
18 end if;
19 end;
20 /
Trigger created.
Note: For ease of example, the SQL for the trigger is presented in-line. It would be advantageous to place the SQL into a stored procedure, especially in this case. Not only would we benefit from reducing the soft parsing our application performs, but we would also be able to get away with a single UPDATE statement.
If we are updating and did not change the DEPTNO column, we just return; no changes need to be made. Otherwise, we update the DEPT table and increment or decrement the EMP_COUNT column as needed, since we are using foreign keys. We are assured that the UPDATE statements update at least and at most a single row in DEPT. (You cannot possibly have a child row in EMP with a DEPTNO that does not exist in DEPT, because the database is enforcing that for us.)
The update of the single row in DEPT also causes serialization at the DEPTNO level; that is, only one transaction can insert into DEPTNO=20 at any point in time. All other sessions trying to insert, delete, or transfer where DEPTNO=20 will block on that update. All of the other DEPTNO operations are available for modification; just not this one. We can still update other EMP rows in DEPTNO=20, as long as we do not change their DEPTNO. This serialization is what allows our constraint to be effective.
(Following my last message)
Fan, July 23, 2003 - 7:09 pm UTC
Hi Tom,
I did some DIY, following your another post on triggers and constraints.
SQL> create table trans (id number, seq number, percentage number);
Table created.
SQL>
SQL> create table trans_sum (id number, total number);
Table created.
SQL>
SQL> alter table trans_sum add (constraint pk1 primary key (id));
Table altered.
SQL>
SQL> alter table trans add
2 (constraint sum_fk foreign key (id) references trans_sum(id));
Table altered.
SQL>
SQL> alter table trans_sum add (constraint ck_100 check (total = 100) deferrable initially deferred)
;
Table altered.
SQL>
SQL> create or replace trigger trans_trigger
2 after insert on trans for each row
3 begin
4
5 insert into trans_sum values (:new.id, :new.percentage);
6
7 exception
8 when dup_val_on_index
9 then
10 update trans_sum set total = total + :new.percentage where id = :new.id;
11 end;
12 /
Trigger created.
SQL>
SQL> insert into trans values (1, 1, 80);
1 row created.
SQL>
SQL> insert into trans values (1, 2, 20);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> select * from trans_sum;
ID TOTAL
---------- ----------
1 100
1 row selected.
SQL> insert into trans values (2, 1, 20);
1 row created.
SQL>
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SCOTT.CK_100) violated
SQL>
It seems to work. Again my question if this is the only way...if possible to implement as CHECK constraint?
Thanks.
Materialized View refresh and Foreign Key constraints
Albert de Ruiter, August 14, 2003 - 6:55 am UTC
Dear Tom,
In your reply to Chuck (July 10) you mention the refreshing of materialized views regarding deferred constraints. Also in another thread ("Constraints using snapshot" April 3, 2001) you give a similar solution. Yet I am having some problems in achieving the same result. By the way, I'm on Oracle 8.1.7.
For a reporting solution I use materialized views. Test is as follows.
ALTER TABLE BI_INKOMSTEN_EN_KOSTEN_MV ADD CONSTRAINT INK_KST_FK_VHE FOREIGN KEY
(VHE_FK) REFERENCES BI.BI_VHE_MV
;
create materialized view test_mv_1 as
select '1' PK from dual
;
alter table test_mv_1 add constraint test_mv_1_fk
primary key (PK)
;
create materialized view test_mv_2 as
select '1' FK from dual
;
alter table test_mv_2 add constraint test_mv_2_fk
foreign key (FK) references test_mv_1
deferrable initially deferred
;
set constraint test_mv_2_fk deferred;
bi@SVG2> create materialized view test_mv_1 as
2 select '1' PK from dual
3 ;
Materialized view created.
bi@SVG2> alter table test_mv_1 add constraint test_mv_1_fk
2 primary key (PK)
3 ;
Table altered.
bi@SVG2> create materialized view test_mv_2 as
2 select '1' FK from dual
3 ;
Materialized view created.
bi@SVG2> alter table test_mv_2 add constraint test_mv_2_fk
2 foreign key (FK) references test_mv_1
3 deferrable initially deferred
4 ;
Table altered.
bi@SVG2> exec dbms_mview.refresh('bi.test_mv_1');
BEGIN dbms_mview.refresh('bi.test_mv_1'); END;
*
ERROR at line 1:
ORA-12008: error in snapshot refresh path
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 654
ORA-06512: at line 1
So maybe I should have stated explicitly that the constraint should be deferred (although you would say that 'initially deferred' is the default here; or does 'at the end of the transaction' mean something different here than 'after commit'?)
bi@SVG2> set constraint test_mv_2_fk deferred;
Constraint set.
bi@SVG2> exec dbms_mview.refresh('bi.test_mv_1');
BEGIN dbms_mview.refresh('bi.test_mv_1'); END;
*
ERROR at line 1:
ORA-12008: error in snapshot refresh path
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 654
ORA-06512: at line 1
I must be overlooking something here. Do you know what?
Your help will be greatly appreciated.
Kind regards,
Albert.
August 14, 2003 - 8:08 am UTC
where are your snapshot groups? mv1 and mv2 HAVE to be refreshed as a consistent refresh group -- else all is for naught here.
Refresh groups
Albert R. de Ruiter, August 28, 2003 - 10:05 am UTC
Oracle 8.1.7
Hi Tom,
After your suggestion to apply refresh-groups in this case
I did some studying.
If the solution that I can come up with makes some sense,
then I am a little bit puzzled about why something trivial
like a refresh has to be accompanied by so much extra effort.
Can you please look at my thoughts and tell if it makes sense?
What I understand of the refresh groups is that you should
group together dimension tables with the fact table (in general
tables that reference each other).
All materialized views are refreshed in one go and there is no
ora-02266 bothering you.
But what if a dimension table is referenced by more than 1
fact table? Couldn't you, put simply, eventually end up with
one big group containing all materialized views like that?
So I read somewhere on Metalink that alternatives could be:
1. Disable the constraints prior to the refresh; then you
would have to specify them by name in your code (no such
thing as to disable all constraints within one schema?).
Since this doesn't look very generic to me, I considered
alternative 2.
2. Use a dummy materialized view to group a regular
materialized view with. Disadvantage would be that you would
require a lot of extra objects (the amount of mv-s doubles),
or you would have to create a dummy prior to the refresh and
drop it afterwards.
The idea is that the materialized views are refreshed by means
of a job, so that a commit on the master-table doesn't include
the refresh. The job is created by a trigger on the master-table.
Thus it would go as follows:
/********************************************************
First create the dummy materialized view
********************************************************/
CREATE MATERIALIZED VIEW BI_DUAL1_MV
REFRESH COMPLETE
AS SELECT * FROM DUAL
;
/********************************************************
Then the refresh-group is created
********************************************************/
BEGIN
dbms_refresh.make(
name => 'BI.BI_MISRUBRIEKEN_MV_GRP',
list => 'BI.BI_MISRUBRIEKEN_MV, BI_DUAL1_MV',
next_date => null,
interval => null,
lax => TRUE);
END;
/
COMMIT;
/*******************************************************/
Finally the trigger is created
/*******************************************************/
CREATE TRIGGER MISRUBR_REFR
AFTER INSERT OR UPDATE ON CTAB.CTB_MIS_RUBRIEKEN
DECLARE
l_job NUMBER;
l_count NUMBER;
BEGIN
SELECT COUNT('x') INTO l_count
FROM dba_jobs
WHERE UPPER(WHAT) LIKE '%BI.BI_MISRUBRIEKEN_MV%';
IF l_count = 0
THEN
DBMS_JOB.SUBMIT(
job => l_job,
what => 'dbms_refresh.refresh( ''BI.BI_MISRUBRIEKEN_MV_GRP'');
dbms_stats.gather_table_stats( ''BI'',''BI_PORTEFEUILLES_MV'',CASCADE => TRUE);',
next_date => TRUNC(SYSDATE) + 9/24 );
END IF;
END
;
And the above steps are repeated for the remaining
materialized views.
Apart from the already mentioned problem that so much
extra objects are needed like this, another disadvantage
is that the null-value in the next_date within
dbms_refresh.make is in fact interpreted as 01-01-4000.
This means that a job is created that will refresh the
group on the mentioned date. So I would have to remove
the job afterwards, because I only want the trigger
to create a job.
Of course I could supply next_date and interval with
proper values, but I would like the materialized view
only to be refreshed when the mastertable has changed.
Something is going wrong anyway, cause I now get
ora-12008 error in snapshot refresh path
ora-02292 integrity constraint <name> violated,
child record found.
Do you know of any alternatives for disabling the fk-s
by name or using dummy mv-s on behalf of grouping?
Or is it simply just the way it goes?
Thanks in advance for your comments and kind regards,
Albert.
August 29, 2003 - 7:40 am UTC
if you have multiple tables -- related via primary/fkeys -- you have to refresh them all at once or not at all in general.
think about it -- even with your "trick" -- what happens when you delete a parent key???? bummer, the child tables are going to prevent that.
It goes way beyond the ora-02266 error...
Fact: you have RI
Fact: all tables involved in RI must be consistent with respect to eachother
Fact: you must refresh them all at once or *remove the integrity constraints in order to allow them to actually refresh independently -- during this period, there is NOT ANY RI between them*
Refresh groups
Albert, September 01, 2003 - 3:51 am UTC
Okay Tom thanks. It's all clear now.
Efficiency?
Jon, November 11, 2003 - 10:02 am UTC
I notice in your examples, you tend to favor the construct
after insert or update or delete on table...
if updating then...
elsif inserting then...
elsif deleting then
end if;
as opposed to three separate triggers for update, insert and delete. Is it more efficient this way or is it a wash?
November 11, 2003 - 11:52 am UTC
it was more convienent then three triggers and since they were in support of the same "thing", it made sense to keep them together.
Thanks - hope I'm right
Jon, November 11, 2003 - 12:20 pm UTC
I took your answer to mean there is no efficiency gain. I would appreciate a response only if I misinterpretted your answer. Thanks much either way.
November 11, 2003 - 1:40 pm UTC
to make it more efficient, i would really put the SQL into a plsql package -- the trigger would be one line long - a call to a package with the logic.
There would be no real efficiency gain in three triggers (other then the removal of the need for an if/elsif construct, minimal)
inseting a row contain foreign key constraint
apl, December 02, 2003 - 8:24 am UTC
i got a doubt.I checked the integrity constaint in scott's schema.
sql>insert into emp (EMPNO,ENAME) values (5,'aaa');
i got
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
then i gave
SQL> insert into emp (EMPNO) values (5);
1 row created.
How is it possible?
December 02, 2003 - 9:25 am UTC
show us the definition of pk_emp eh? tell us how you created EMP table.... (our demobld does not create an emp_pk)
Here is one way to get what you got:
ops$tkyte@ORA920PC> create table emp as select * from scott.emp where 1=0;
Table created.
ops$tkyte@ORA920PC> alter table emp add constraint emp_pk unique(empno,ename);
Table altered.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into emp (empno,ename) values(5,'aaa');
1 row created.
ops$tkyte@ORA920PC> insert into emp (empno,ename) values(5,'aaa');
insert into emp (empno,ename) values(5,'aaa')
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.EMP_PK) violated
ops$tkyte@ORA920PC> insert into emp (empno) values(5);
1 row created.
Not clear about Constraint Vs Trigger [Application developers Guide (page 4-2)]
Sami, January 17, 2004 - 12:14 pm UTC
Dear Tom,
I don't understand the following paragraph in
"Oracle 9i Application developers Guide (page 4-2)"
The following trigger does same as FOREIGN KEY constraint. Kindly enlighten me in your way.
<Document>
To enforce this rule without integrity constraints, you can use a trigger to query the
department table and test that each new employeeÂ’s department is valid. But this
method is ##LESS RELIABLE## than the integrity constrain, because SELECT in Oracle uses
"consistent read" and so the query might miss uncommitted changes from other
transactions.
</Document>
create table tr_test(c1 number,c2 number);
create table tr_test_parent(c2 number primary key);
/*
The following trigger does the same as FOREIGN KEY.
*/
CREATE OR REPLACE TRIGGER TR_TEST_TR01
BEFORE INSERT OR UPDATE
ON TR_TEST
FOR EACH ROW
BEGIN
DECLARE
A NUMBER;
BEGIN
SELECT 1 INTO A
FROM TR_TEST_PARENT
WHERE C2 =:NEW.C2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20201,'PARENT KEY NOT FOUND '||:NEW.C2);
END;
END;
/
SQL> insert into tr_test_parent values(1);
1 row created.
SQL> insert into tr_test values(1,1);
1 row created.
SQL> insert into tr_test values(1,2);
insert into tr_test values(1,2)
*
ERROR at line 1:
ORA-20201: Parent key not found 2
ORA-06512: at "HR.TR_TEST_TR01", line 10
ORA-04088: error during execution of trigger 'HR.TR_TEST_TR01'
Thanks in advance Tom.
January 18, 2004 - 12:57 pm UTC
do you have any of my books? I cover this topic in great, large, detail. It is THE important thing to understand in Oracle.
Basically -- you cannot really enforce cross object or cross row integrity using triggers UNLESS you lock lots of objects (the entire object in most cases).
consider what happens when someone logs into session one and issues:
delete from tr_test_parent where c2 = 10;
and walks away for five minutes. After 5 minutes they come back and issue "commit"
Meanwhile -- every firing of your trigger during those 5 minutes queried tr_test_parent -- and "saw" c2 = 10. hmmmmmmmmm. but c2=10 was really deleted (and any triggers that would fire on the delete of c2=10 fired 5 minutes ago -- before any child records existed).
do NOT even consider enforcing integrity across rows or across objects using triggers. You'll never get it right. See
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7144179386439 <code>
and read the entire page, all of the examples. many cases of why you cannot do this and how tricky it can really be.
Guess I was just too thick in the head.
Sami, January 18, 2004 - 3:12 pm UTC
Dear Tom,
I have your both books (did study long time back).
Anyway your
<example>
delete from tr_test_parent where c2 = 10;
and walks away for five minutes. After 5 minutes they come back and issue
"commit"
</example>
has given clear understanding.
Thanks Tom
A reader, May 20, 2004 - 6:49 am UTC
Tom,
update TB_ETR001_GEIS_ETPROJECT set object_nam='issue oracle update issue' where object_nam='Oracle update issue';
ORA-02292: integrity constraint (MATRIX.CN_ETR003001_FK1) violated - child record found
update TB_ETR003_GECI_ETMON_FINANCIAL set object_nam='issue oracle update issue' where object_nam='Oracle update issue';
ORA-02291: integrity constraint (MATRIX.CN_ETR003001_FK1) violated - parent key not found
I am trying to update table etr001, but it fails, bcos child record is there and again if i update etr003 it says parent not found.
Please let me know how to handle this scenario?..
also, how to handle this through application ..?
Thanks in advance
May 20, 2004 - 11:36 am UTC
if constraint is deferrable, defer the constraints and it'll work.
if not, you can either "update cascade" (see </code>
http://asktom.oracle.com/~tkyte <code> or
update child set fk = NULL where fk = 'X';
update parent set pk = newvalue;
update child set fk = newvalue where fk is null;
(assuming the fk's are not null right now -- else, you'd need to save the child primary keys for the rows that would need updating, set their fks to null, update parent and then update just those children)
Thank you so much tom ...
A reader, May 21, 2004 - 1:30 am UTC
SET CONSTRAINTS ALL DEFERRED; using dbms_session
A reader, June 17, 2004 - 12:52 pm UTC
Hi Tom, how can I execute the following command using
dbms_session package or another way to run from forms developer.
SET CONSTRAINTS ALL DEFERRED;
Thanks
June 17, 2004 - 2:55 pm UTC
forms_ddl( 'set constraints all deferred' );
A reader, June 17, 2004 - 3:08 pm UTC
Thanks Tom, but I tried it before and didn't work, I supposed it was another session, even I created a function that did it using execute immediate, If you say this should work I'll check if there is another condition blocking the process.
Question Regarding Referential Integrity Constraint
Muhammad Riaz Shahid, June 25, 2004 - 9:12 am UTC
Hello Tom !
Sorry for asking this question here but i am stuck with this one:
SQL> ed
Wrote file afiedt.buf
1* create table apples(code number(3), code_desc varchar2(10))
SQL> /
Table created.
SQL> ed
Wrote file afiedt.buf
1* create table pears(code number(3), code_desc varchar2(10))
SQL> /
Table created.
SQL> ed
Wrote file afiedt.buf
1* create table pears(code number(3), code_desc varchar2(10))
SQL> create view apples_pears as select code,code_desc from apples union all select code,code_Desc from pears;
View created.
SQL> create table fruit (fruit_code number(3) references apples_pears(code));
create table fruit (fruit_code number(3) references apples_pears(code))
*
ERROR at line 1:
ORA-02444: Cannot resolve referenced object in referential constraints
I actually want to implent a check (FK) that the fruit_code should present either in apples table or in pears table ? Can this be possible ?
June 25, 2004 - 2:34 pm UTC
no, it is not possible, you have modelled the hierarchy backwards if you ask me.
fruit is the supertype (parent)
apples and pears are the subtypes (children)
apples and pears point to fruits, not the other way around.
Or'ed foreign key
Rima, March 03, 2005 - 7:32 pm UTC
We have the following case : loans are either in pending state or purchased(funded) state. i.e. we have two tables pend_asset and fund_asset - both have an asset_seq_id as the pk. An Asset is either in one or the other but never in both tables. And assets first come in the PEND state and move to the fund table when they are bought.
Now we have an attribute "stop loss" that needs to be calculated for a loan regardless of the state it is in. So, I have a 3rd table "STOP_LOSS" that has asset_seq_id, stop_loss_pct - two columns.
I want to enforce a constraint saying entries in this stop loss table should match EITHER asset_Seq_id in PEND_ASSET OR asset_seq_id in FUND_ASSET.
Is there a way to do this?
Thanks for your help
March 03, 2005 - 7:56 pm UTC
not in a single column, no -- and if you try to do it in the application or via a trigger -- make sure you include lock rows...... and lock the stop loss table when you modify either of the two parents
(meaning, you want to have two columns, two fkeys, one column will always be null)
but it sounds like your schema is missing a parent table ASSET with common attributes (or that pend and fund should just be optional attributes of a single table ASSET)
Whos first Triggers or Integrity Constraints
A reader, July 20, 2005 - 2:38 pm UTC
What is checked/Fired first
Triggers or Constraints ?
I think its Triggers First then Constraints if it is a Before Insert/Update/Delete Trigger.
If it is After Insert/Update/Delete Trigger then its the Constraints first then the Triggers.
July 21, 2005 - 7:42 am UTC
constraints are logically done after the statement completes.
they are definitely done after the before triggers - since a before for each row trigger can change the value of the columns being modified.
Check Constraint Question
BC, September 28, 2005 - 11:46 am UTC
Tom,
How do I prevent non whole numbers from being inserted into a column defined as number(n,0). It seems like Oracle rounds the input prior to inserting it to the table.
Here is an example.
SQL> create table t ( numfld number(4,0) );
Table created.
SQL> alter table t
2 add constraint t_check check (floor(numfld) = ceil(numfld));
Table altered.
SQL> insert into t values(4.45);
1 row created.
SQL> insert into t values(5);
1 row created.
SQL> insert into t values(4.75);
1 row created.
SQL> select * from t;
NUMFLD
---------
4
5
5
Your input is highly appreciated.
Thanks
BC
September 28, 2005 - 12:35 pm UTC
Thanks
BC, September 28, 2005 - 1:40 pm UTC
Tom,
You are awesome, This is exactly what I was looking for. Thank you very much for the quick response.
BC
Alex, October 11, 2005 - 11:53 am UTC
Hi Tom,
I was wondering if Oracle offers any kind of user defined constraints. For example, if you have a unique key consisting of three columns, but one them has conditions that need to be met to form the key, else allow dups. Create a constraint based on a query possibly? I searched the docs and found "Complex Integrity Checking", with a brief description, but no examples or real explanation
October 11, 2005 - 3:44 pm UTC
you can do it with function based indexes:
create unique index check_project_name_unique
on t( case when status = 'ACTIVE' then project_name end );
that'll uniquely index ONLY the active records.
Alex, October 12, 2005 - 10:52 am UTC
Tom,
I'm having some trouble getting nulls to adhere to the same rules, i.e. unique. Is there a way to allow only one null row in my FBI? I'd paste what I'm doing but I keep getting resource busy errors every time I try and drop my index to recreate it, even though it's just some test table that only I know about.
SQL> delete from t;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> drop index check_keys;
drop index check_keys
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
My index looks like this, like I said I want to allow only one null value for l_date but this doesn't work:
CREATE UNIQUE INDEX check_keys
ON t( CASE WHEN status <> 'P' THEN l_date END,
CASE WHEN DECODE(l_date, NULL, 1, 0) = 1 THEN l_date END );
Always appreciate your help.
October 12, 2005 - 2:10 pm UTC
what is the deal with status in there? something is missing in the description of the problem.
Alex, October 12, 2005 - 2:46 pm UTC
Status is column that contains the condition whether or not I need the other column to be unique. I was trying to keep mine close to what you suggested.
So IF status <> P then l_date should be unique, including no dup nulls.
October 13, 2005 - 9:30 am UTC
if you can pick a date that won't be valid for your data, like 01-jan-0001 for example, then:
ops$tkyte@ORA10G> create table t ( status varchar2(1), l_date date );
Table created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create unique index t_idx on t( case when status <> 'P' then nvl( l_date, to_date('01-01-0001','dd-mm-yyyy') ) end );
Index created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set feedback off
ops$tkyte@ORA10G> insert into t values ( 'P', to_date( '01-jan-2005', 'dd-mon-yyyy' ) );
ops$tkyte@ORA10G> insert into t values ( 'P', to_date( '01-jan-2005', 'dd-mon-yyyy' ) );
ops$tkyte@ORA10G> insert into t values ( 'P', Null );
ops$tkyte@ORA10G> insert into t values ( 'P', Null );
ops$tkyte@ORA10G> insert into t values ( 'x', to_date( '01-jan-2005', 'dd-mon-yyyy' ) );
ops$tkyte@ORA10G> insert into t values ( 'x', to_date( '01-jan-2005', 'dd-mon-yyyy' ) );
insert into t values ( 'x', to_date( '01-jan-2005', 'dd-mon-yyyy' ) )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
ops$tkyte@ORA10G> insert into t values ( 'x', null );
ops$tkyte@ORA10G> insert into t values ( 'x', null );
insert into t values ( 'x', null )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
ops$tkyte@ORA10G> set feedback on
<b>else, if you don't want to pick such a date, you can do this:</b>
ops$tkyte@ORA10G> create unique index t_idx on t( case when status <> 'P' then l_date end,
2 case when status <> 'P' and l_date is NULL then 1 end );
Index created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set feedback off
ops$tkyte@ORA10G> insert into t values ( 'P', to_date( '01-jan-2005', 'dd-mon-yyyy' ) );
ops$tkyte@ORA10G> insert into t values ( 'P', to_date( '01-jan-2005', 'dd-mon-yyyy' ) );
ops$tkyte@ORA10G> insert into t values ( 'P', Null );
ops$tkyte@ORA10G> insert into t values ( 'P', Null );
ops$tkyte@ORA10G> insert into t values ( 'x', to_date( '01-jan-2005', 'dd-mon-yyyy' ) );
ops$tkyte@ORA10G> insert into t values ( 'x', to_date( '01-jan-2005', 'dd-mon-yyyy' ) );
insert into t values ( 'x', to_date( '01-jan-2005', 'dd-mon-yyyy' ) )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
ops$tkyte@ORA10G> insert into t values ( 'x', null );
ops$tkyte@ORA10G> insert into t values ( 'x', null );
insert into t values ( 'x', null )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
ops$tkyte@ORA10G> set feedback on
<b>that is, index uniquely l_date,NULL when l_date is not null and NULL,1 when l_date is null</b>
Alex, October 13, 2005 - 9:56 am UTC
That's perfect thank you. I do have one other question, can you have multiple columns to form the unique key in the index? Something like this
create unique index t_idx on t( case when status <> 'P' then
nvl( l_date, to_date('01-01-0001','dd-mm-yyyy'))
other_column, col3 end );
Or maybe you have to build individual CASE statements for each?
October 13, 2005 - 10:56 am UTC
The second example:
ops$tkyte@ORA10G> create unique index t_idx on t( case when status <> 'P' then
l_date end,
2 case when status <> 'P' and l_date is NULL
then 1 end )
had multiple columns, you "need" the case because the semantics of your problem dictate that each column in the index be provided based on the value of status.
Alex, October 13, 2005 - 11:59 am UTC
Ahh yes ok I think see/understand what you did now. I missed part of it in the nice big example you gave ;)
Implementing RI Contraints
Su Baba, February 01, 2006 - 2:06 pm UTC
How do I implement RI constraints between 2 tables that have a mandatory relationship on both sides?
Say I have an order and order_line table. An order must have 1 or more order lines. An order line must belong to one and only one order.
It seems this can be done using a deferrable constraints, but I'm not sure how it actually can be done. Can you please show us an example? Thanks.
February 02, 2006 - 4:26 am UTC
the "order line must belong to one and only one order" is easy, that is declaritive RI (a foreign key).
the "an order must have 1 or more order lines" is a bit more difficult and it depends on how you want to approach it. One method is to say "no one shall ever insert/update these tables directly but they shall call our procedures and our procedures do the right thing" (eg: a transactional api, application sends data to procedure, procedure does the right thing in the database with it)
You can do this with triggers and a cnt field in the parent table as well, for example:
ops$tkyte@ORA9IR2> create table orders
2 ( x int primary key,
3 cnt int default 0
4 not null
5 constraint must_have_at_least_one check (cnt>0)
6 deferrable initially deferred
7 )
8 /
Table created.
ops$tkyte@ORA9IR2> create table order_lines
2 ( x references orders,
3 y int,
4 data varchar2(10),
5 primary key (x,y)
6 )
7 /
Table created.
ops$tkyte@ORA9IR2> create or replace trigger aid_order_lines
2 after insert or delete on order_lines for each row
3 begin
4 if ( inserting )
5 then
6 update orders set cnt = cnt+1 where x = :new.x;
7 else
8 update orders set cnt = cnt-1 where x = :old.x;
9 end if;
10 end;
11 /
Trigger created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into orders ( x ) values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.MUST_HAVE_AT_LEAST_ONE) violated
ops$tkyte@ORA9IR2> insert into orders ( x ) values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> insert into order_lines( x,y ) values ( 2, 1 );
insert into order_lines( x,y ) values ( 2, 1 )
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.SYS_C003184) violated - parent key
not found
ops$tkyte@ORA9IR2> insert into order_lines( x,y ) values ( 1, 1 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> insert into order_lines( x,y) values ( 1, 2 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> delete from order_lines;
2 rows deleted.
ops$tkyte@ORA9IR2> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (OPS$TKYTE.MUST_HAVE_AT_LEAST_ONE) violated
<b>You might even go one step further if you explore this approach</b> and use a view on ORDERS that hides the cnt column - so an application could never update it directly.
Bear in mind, we are relying on the trigger to be fired, that obviates direct path loads and such (you would have to handle that yourself if you needed to do those)
Information - Deferrable check constraint not working in IOT
Rajeswari, March 06, 2006 - 4:26 am UTC
Information to readers of asktom. You can see the original post from Gabor in the following link.
</code>
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/ed306861dcf03192/32502acc101a5e55?lnk=raot#32502acc101a5e55 <code>
scott@ORA9.2> create table heap(x int primary key, y int);
Table created.
scott@ORA9.2> alter table heap add constraint h_ck check(y > 0) deferrable;
Table altered.
scott@ORA9.2> set constraint h_ck deferred;
Constraint set.
scott@ORA9.2> insert into heap(x,y) values(1,-1);
1 row created.
scott@ORA9.2> delete from heap;
1 row deleted.
scott@ORA9.2> set constraint h_ck immediate;
Constraint set.
scott@ORA9.2> commit;
Commit complete.
create table iot(x int primary key, y int) organization index
scott@ORA9.2> /
Table created.
scott@ORA9.2> alter table iot add constraint i_ck check(y > 0) deferrable;
Table altered.
scott@ORA9.2> set constraint i_ck deferred;
Constraint set.
scott@ORA9.2> insert into iot(x, y) values(1,-1);
1 row created.
scott@ORA9.2> delete from iot;
1 row deleted.
scott@ORA9.2> set constraint i_ck immediate;
SET constraint i_ck immediate
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.I_CK) violated
refresh groups proof
A reader, May 16, 2006 - 8:32 am UTC
Hi
I am setting up a READ ONLY replication from Site A to Site B. Site A will be the Master site and B the MV site.
I will be replicating 125 tables in a schema. There are lots of Referential Integrity going there. I understand I have to define several Refresh Groups at MV site to
1. speed up Refresh Process (running in parallel)
2. use less UNDO
3. ensure RI
I have some questions regarding READ ONLY replication
1. Do I have to define a Master Group???
2. Is it a good practice use proxy refreshers at both Master Site and MV site?
3. I would like to test if Refresh Group does actually keep RI, dont have a clue how to test this. May be with deferred constraints?
4. How does Oracle ensure RI when Refresh Groups are used? (This last is curiosity)
TIA
RIMMA
May 16, 2006 - 8:46 am UTC
"proxy refreshers"?
You would use deferrable constraints on all of the materialized views, yes.
When you use a refresh group - all of the materialized views are refreshed together in the same transaction. And since the constraints are deferrable (and we defer all deferrable constraints at the beginning of the refresh), the individual refreshes work and when the COMMIT of the refresh of this group happens - all constraints are validated.
Proxy Refreshers
A reader, May 16, 2006 - 8:53 am UTC
Hi
Sorry for the term PROXY. The term is used in the docs as proxy users not refreshers sorry. By refresher I mean the user which will run the refresh processes.
Since you have not mentioned about Master Groups I guess we dont have to define that in READ ONLY replication correct?
I have created MVs and Oracle automatically created PK for each of them however they are not deferrable by default and the FKs are not created neither. Do I have to recreate the constraints?
RIMMA
May 16, 2006 - 9:24 am UTC
If you are using materialized views, you just need refresh groups.
You can add a deferrable UNIQUE constraint on your "key"
You can add deferrable FOREIGN KEY constraints on your foreign keys.
A reader, May 16, 2006 - 10:34 am UTC
Thanks Tom.
Since there are PKs already in the MVs I guess I have to recreate them to make them deferrable.
I have a further question, imagine I have a very busy system and I have to repliate two Master-Detail tables (for example DEPT-EMP),
1. I start refresh fast at 15:00.
2. The replication of Master Table takes 5 minutes however there are still constant inserts in this Master Table.
3. Replication of detail table starts at 15:05 and finishes in 30 minutes.
How can Oracle ensure integrity if during step 3 there are new rows inserted in the Master Tables but not replicated to the MV site? After the replication finishes the detail table in the MV site there might be rows which are in detail tables without parent keys.
May 16, 2006 - 10:57 am UTC
do not touch the stuff Oracle has created. Only do it for your constraints.
Oracle deals with it - it does everything in a read consistent fashion as of the point of time at the beginning of the refresh.
A reader, May 16, 2006 - 11:30 am UTC
"Oracle deals with it - it does everything in a read consistent fashion as of the point of time at the beginning of the refresh. "
May you please clarify if this only happens if we use REFRESH GROUPS? If Oracle deals with data integrity I think we dont have to create deferrable constraints correct?
I have a table which has user defined types, when I try to create the MV in the MV site I get ORA-21700: object does not exist or is marked for delete all the time although I have created the TYPE at MV site too.
RIMMA
May 16, 2006 - 11:44 am UTC
I was talking about refresh groups - the materialized views in the group are refreshed consistently with respect to eachother.
That is "the entire purpose" of the refresh group! We would not have them otherwise.
The constraints are there for the optimizer, it uses primary key, foreign key, not null, check constraints and so on during query optimization.
read:
</code>
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14226/repmview.htm#sthref605 <code>
Object MV
A reader, May 16, 2006 - 12:05 pm UTC
Hi
I read the link you provided and the only condition I dont have is the OID is different in two sites. How can I force an OID when create an object?
I have tried without specifying OID and get these errors:
---------------
compatible string 9.2.0.0.0
MASTER SITE:
---------------------------
SQL> CREATE OR REPLACE TYPE Address_typ AS OBJECT
2 ( Street VARCHAR2(80),
3 City VARCHAR2(80),
4 State VARCHAR2(2),
5 Zip VARCHAR2(10));
6 /
Type created.
SQL>
SQL> CREATE TABLE contacts
2 ( Contact_id NUMBER(10) CONSTRAINT contacts_pk PRIMARY KEY,
3 First_name VARCHAR2(15),
4 Last_name VARCHAR2(15),
5 Address_obj Address_typ );
Table created.
SQL>
SQL> INSERT INTO contacts
2 VALUES ('1','George','Odnil',
3 Address_typ('717 Humber Lane','Orlando','FL','32807'));
1 row created.
SQL>
SQL> INSERT INTO contacts
2 VALUES ('2','Juanta','Odnil',
3 Address_typ('139 North Deerwood Ave.','Orlando','FL','32800'));
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
MV SITE:
----------
SQL> CREATE OR REPLACE TYPE Address_typ AS OBJECT
2 ( Street VARCHAR2(80),
3 City VARCHAR2(80),
4 State VARCHAR2(2),
5 Zip VARCHAR2(10));
6 /
Type created.
SQL> CREATE MATERIALIZED VIEW MV_CONTACTS OF Address_typ
2 TABLESPACE USERS
3 BUILD DEFERRED
4 USING INDEX
5 TABLESPACE USERS
6 AS
7 SELECT * FROM contacts@VMW1920;
CREATE MATERIALIZED VIEW MV_CONTACTS OF Address_typ
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
May 16, 2006 - 12:16 pm UTC
REF TYPE CONSTRAINT REFERENCES / SCOPE IS
Arindam Mukherjee, July 29, 2006 - 8:40 am UTC
Respected Mr. Tom,
Today I was just reading “Constraints” in Oracle 9i SQL reference and sucked into permutation and combination of constraints state / clause like deferrable, initially, rely etc. As a result I started seeing your site having constraints. Your explanation is no doubt very simple and top-notch. Still I could not find easy-going example on “REF” constraint and “RELY” clause to your site. It’s my humble plea to you please to give us your simple and exceptional example on 1> “REF” constraint and 2> “RELY” clause. And at the same time, kindly mention two different things “SCOPE” and “REFERENCE” with “REF” constraint.
The only question – Why does Oracle Corporation recommend creating the unique index explicitly using a CREATE UNIQUE INDEX statement instead of Unique Constraint? (Ref. Page – 7-12, Oracle 9i SQL Reference)
ItÂ’s my candid feelings, sometimes I believe we all readers are selfish because we are using you as Oracle Dictionary.
With Best Regards,
Arindam Mukherjee
July 29, 2006 - 9:15 am UTC
that is because I don't use pointers and have never in my career used a REF, scoped or otherwise.
chapter 7 of the 9i sql reference is common ddl clauses - I don't see that quote?
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses.htm#1011936 <code>
point to it, so we can see it in context.
Likely - it is saying "if you want a unique index, use unique index not create constraint since the constraint might not create a new index, and might not create a unique one if it does"
You would of course create the constraint afterwards, and it would just use the existing index you created for its enforcement.
Please see Again
Arindam Mukherjee, July 29, 2006 - 9:55 am UTC
Respected Mr. Tom,
I have cut that portion from the URL you sent to me - Oracle 9i SQL Reference. I read PDF, so I always refer page no. Anyway you please read the following lines taken from Oracle 9i SQL Reference. So I asked you last time, why we should opt for Unique Index Creation instead of Unique Constraint?
**********************
Note:
When you specify a unique constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle Corporation recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement. See CREATE INDEX for more information.
****************************
Thank you once again for your caring support and unflagging response.
Regards,
Arindam Mukherjee
July 29, 2006 - 11:28 am UTC
oh, I bugged that a while ago. It is technically inaccurate.
But it basically is what I said above - IF you want a unique index, create a unique index (and by all means add the unique constraint).
for you see - the unique constraint does NOT always implicitly create an index (unique or otherwise) on the key.
IF you want a unique index
THEN
create it
END IF
IF you have a set of columns that are to be "unique"
THEN
add constraint
END IF
that is, do both.
Deferred Constraint Delete Performance...
Gondo The Lesser, September 12, 2006 - 9:07 am UTC
Dear Mister Kyte,
I've run into an interesting occurrence whereby if I delete a subset of records from a parent table with a deferred foreign key constraint, and then delete the corresponding records from the child table, I find that the delete from the child table takes literally thousands of times longer than if I delete from the tables in the opposite (conventional) order.
Obviously it's easy to work around this issue, but I was interested in what is actually happening differently that causes this to occur?
I don't see any significant waits in a trace, but nearly all the time shows up as CPU time, and this seems to ring true given the state of the host server while the delete is running.
This happens when the tables are quite large and the delete is on a 10%-ish or larger subset and results in the bizarre situation where it can take several hours to delete a few hundred thousand records. (I've got a simple test case here, but I'm not going to paste it in at this stage unless you're interested)
September 12, 2006 - 9:16 am UTC
got test case?
Drum Roll....the test case
Gondo the Lesser, September 12, 2006 - 9:30 am UTC
DROP TABLE CHILD_TABLE
/
DROP TABLE PARENT_TABLE
/
CREATE TABLE PARENT_TABLE
AS SELECT 999 BATCH_ID, 999999 ID, 'THIS IS SOME DATA' PARENT_DATA
FROM DUAL
WHERE 1=2
/
CREATE TABLE CHILD_TABLE
AS SELECT 999 BATCH_ID, 999999 PARENT_ID, 999999 ID, 'SOME CHILD DATA' CHILD_DATA
FROM DUAL
WHERE 1=2
/
ALTER TABLE PARENT_TABLE ADD CONSTRAINT PK_PARENT_TABLE PRIMARY KEY ( BATCH_ID, ID )
/
ALTER TABLE CHILD_TABLE ADD CONSTRAINT PK_CHILD_TABLE PRIMARY KEY ( BATCH_ID, PARENT_ID, ID )
/
ALTER TABLE CHILD_TABLE ADD CONSTRAINT FK01_CHILD_TABLE FOREIGN KEY ( BATCH_ID,PARENT_ID ) REFERENCES PARENT_TABLE ( BATCH_ID, ID )
DEFERRABLE INITIALLY DEFERRED
/
--
-- it takes a while to generate test data for this case...
-- I know, I know, this is a bit of a silly way to do it...
--
BEGIN
FOR I IN 1..30 LOOP
INSERT INTO PARENT_TABLE
SELECT I BATCH_ID, ROW_NUMBER() OVER (ORDER BY I) ID, 'SOME DATA'
FROM DUAL CONNECT BY ROWNUM < 200000;
INSERT INTO CHILD_TABLE
SELECT I BATCH_ID, ROW_NUMBER() OVER (ORDER BY I) PARENT_ID, 1 ID, 'SOME CHILD DATA'
FROM DUAL CONNECT BY ROWNUM < 200000;
INSERT INTO CHILD_TABLE
SELECT I BATCH_ID, ROW_NUMBER() OVER (ORDER BY I) PARENT_ID, 2 ID, 'SOME CHILD DATA'
FROM DUAL CONNECT BY ROWNUM < 200000;
END LOOP;
COMMIT;
END;
/
SELECT COUNT(*) FROM PARENT_TABLE
/
SELECT COUNT(*) FROM CHILD_TABLE
/
--
-- now, do the delete around this way and there are no problems...
--
--alter session set events '10046 trace name context
--forever, level 12'
--/
DELETE /*+ WITH CONSTRAINTS */ FROM CHILD_TABLE WHERE BATCH_ID = 1
/
DELETE /*+ WITH CONSTRAINTS */ FROM PARENT_TABLE WHERE BATCH_ID = 1
/
ROLLBACK
/
--
-- but this way the delete from the child takes much longer
--
--
--ALTER SESSION SET CONSTRAINTS=DEFERRED
--/
--
DELETE /*+ CONSTRAINTS DEFERRED */ FROM PARENT_TABLE WHERE BATCH_ID = 1
/
DELETE /*+ CONSTRAINTS DEFERRED */ FROM CHILD_TABLE WHERE BATCH_ID = 1
/
ROLLBACK
/
--alter session set events '10046 trace name context OFF'
--/
September 12, 2006 - 11:45 am UTC
confirmed, I'll see if I can dig up anything.
If I had to "guess", the child delete is going back to the parent in some fashion to say "ok, you are good to go"
the current mode gets went literally through the roof. The first child delete used 432,428 of them...
the second one - after I killed it part way - had already used 23,085,623 of them (that'll do it for sure, thats an expensive logical IO)
Some useful and missing information...
Gondo Again, September 12, 2006 - 9:33 am UTC
This is 10.1.0.3 ( reproduced on 10.1.0.5 ) on Red Hat Enterprise Linux.
Thanks....
Golan The Lesser, September 12, 2006 - 11:56 am UTC
"confirmed, I'll see if I can dig up anything."
Cheers Tom, thanks for checking that out.
Also, so that you don't find out and think I'm second guessing you, I've also raised an SR on metalink for it with the same test case.
Can I speed up enable constraint
A reader, September 12, 2006 - 1:24 pm UTC
Tom,
I have to do large data loads using direct path insert. Before doing data loads, I disbale the RI constraints on tables like alter table x disable constraint fk_x_y since the data can be loaded in any table first.
After data load is complete, I enable the RI constraints using alter table x enable constraint fk_x_y. This takes a very long time (2 hours+) because of data volume.
The data I insert via direct path is trusted since it comes from another database where constraints are already enforced. Is there a way to speed up enabling the constraints? We are on 10gr2.
Thanks
Useful explanations
Tavio Lawson, September 12, 2006 - 3:27 pm UTC
Tom,
Thanks for sharing your knowledge of oracle. I come to your site once a day and every time I come I leave with something new that I learn. Once again thanks for the work and the time you are putting into this. The oracle community owes you a lot.
OTL
ORA-00001: unique constraint
Vinayak, June 29, 2008 - 6:52 pm UTC
Hi Tom,
We have a situation where some data is stored in the form of nested table within a table(T). The Nested table has 5 columns in it but no PK. There is a MV on the table T. The MV has been defined to have COMPLETE refresh ON DEMAND. However, when I tried to create the MV, it gave the following error
ORA-12014: table 'LEADBANK_TOP_10_TAB' does not contain a primary key constraint
where LEADBANK_TOP_10_TAB is the name of the nested table. Kindly help me finding what is wrong here. Below is the sample DDL script for your reference:
CREATE OR REPLACE
type T_top_ten_leadbank as object
(
relative_rank integer,
bank varchar2(255),
fees number,
no_of_deals integer,
share_tot number
)
/
CREATE OR REPLACE
type T_top_ten_leadbank_array as table of t_top_ten_leadbank
/
CREATE TABLE market_share_table(
SECURITY_GROUP_ID NUMBER NULL,
REPORT_GROUP VARCHAR2(6 BYTE) NULL,
TXN_YEAR VARCHAR2(20 BYTE) NULL,
DATA_NAME VARCHAR2(100 BYTE) NULL,
DATA_ID NUMBER NULL,
STREET_FEES NUMBER NULL,
ABC_FEES NUMBER NULL,
RANKING NUMBER NULL,
LEAD_BANK T_STRING_ARRAY NULL,
LEADBANK_TOP_10 T_TOP_TEN_LEADBANK_ARRAY NULL
)
NESTED TABLE LEAD_BANK STORE AS LEAD_BANK_TAB
((PRIMARY KEY (NESTED_TABLE_ID, COLUMN_VALUE))
)
NESTED TABLE LEADBANK_TOP_10 STORE AS LEADBANK_TOP_10_TAB
/
ALTER TABLE market_share_table ADD (
CONSTRAINT IBA_MP_FEE_POOL_SUMM_PK
PRIMARY KEY
(SECURITY_GROUP_ID, REPORT_GROUP, TXN_YEAR, DATA_NAME)
USING INDEX );
CREATE MATERIALIZED VIEW IRP_DBO.market_share_table_mv
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE ON DEMAND
WITH primary key
AS
SELECT
security_group_id,
report_group,
txn_year,
data_name,
data_id,
street_fees,
abc_fees,
ranking,
lead_bank
,leadbank_top_10
FROM market_share_table;
ORA-12014: table 'LEADBANK_TOP_10_TAB' does not contain a primary key constraint
I can't imagine of what primary key should I have on the mentioned nested table. The below is code snippet that populates the market_share_table. This is just a snippet and part of a big process.
-------code above---------
INSERT INTO market_share_table
(security_group_id,
report_group,
txn_year,
data_name,
data_id,
street_fees,
abc_fees,
ranking,
lead_bank,
leadbank_top_10
)
select a.*, cast(multiset(select inpyf.managername
from gt_deal_logic_3 inpyf
where inpyf.txndate = a.year
and inpyf.descr = a.dataname
and inpyf.id = a.id
AND report_group = 'A'
and inpyf.ranking = 1
order by inpyf.managername) as t_string_array) leadbank
, cast(multiset(select inpyf.ranking,
inpyf.managername,
round(inpyf.totalmanagerfees,1),
inpyf.deal_cnt, round(inpyf.totalmanagerfees/a.streetfees*100,1)
from gt_deal_logic_3 inpyf
where inpyf.txndate = a.year
and inpyf.descr = a.dataname
and inpyf.id = a.id
AND report_group = 'A'
and inpyf.ranking <= 10
order by inpyf.ranking) as T_top_ten_leadbank_array)
multiset union
cast(multiset(select distinct
--to_number(null),
-1, 'Sub Total', round(sum(inpyf.totalmanagerfees) over (),1),
sum(inpyf.deal_cnt) over (),
round(sum(inpyf.totalmanagerfees/a.streetfees*100) over(),1)
from gt_deal_logic_3 inpyf
where inpyf.txndate = a.year
and inpyf.descr = a.dataname
and inpyf.id = a.id
AND report_group = 'A'
and inpyf.ranking <= 10 ) as T_top_ten_leadbank_array)
multiset union
cast(multiset(select distinct
inpyf.ranking ,
inpyf.managername,
round(inpyf.totalmanagerfees,1),
inpyf.deal_cnt,
round(inpyf.totalmanagerfees/a.streetfees*100,1)
from gt_deal_logic_3 inpyf
where inpyf.txndate = a.year
and inpyf.descr = a.dataname
and inpyf.id = a.id
AND report_group = 'A'
and (inpyf.ranking > 10 and inpyf.managername = 'ABC') ) as T_top_ten_leadbank_array)
multiset union
cast(multiset(select distinct
--to_number(null),
-2, 'Total',
round(sum(inpyf.totalmanagerfees) over (),1),
sum(inpyf.deal_cnt) over (),
100
from gt_deal_logic_3 inpyf
where inpyf.txndate = a.year
and inpyf.descr = a.dataname
and inpyf.id = a.id
AND report_group = 'A') as T_top_ten_leadbank_array) leadbank_top_10
from (
SELECT
641,
'A' report_group,
pyf.txndate year
, pyf.descr dataname
, pyf.id
, sum(pyf.totalmanagerfees) streetfees
, (select inpyf.totalmanagerfees
from gt_deal_logic_3 inpyf
where inpyf.txndate = pyf.txndate
and inpyf.descr = pyf.descr
and inpyf.managername = 'ABC'
AND report_group = 'A') abcfees
, (select inpyf.ranking --rankbysectorbyyear
from gt_deal_logic_3 inpyf
where inpyf.txndate = pyf.txndate
and inpyf.descr = pyf.descr
and inpyf.managername = 'ABC'
AND report_group = 'A') abcrank
from gt_deal_logic_3 pyf
WHERE report_group = 'A'
GROUP BY pyf.txndate, pyf.descr, pyf.id) a;
-------code below---------
I am sorry if I spammed ur space...just wanted to give as much information to you as possible.
Is a deferable constraint currently deferred?
Michael, May 28, 2010 - 2:57 am UTC
Hi Tom,
i have the following table:
CREATE TABLE TEST(
A NUMBER(2,0),
CONSTRAINT TEST_A_CH CHECK(A > 5) DEFERRABLE ENABLE
)
Now, i want to find out if the constraint is
currently set to immediate or deferred.
I tried to achieve this with a query on USER_CONSTRAINTS but failed:
Oracle Database 10g Release 10.2.0.3.0 - Production
SQL> SET CONSTRAINT test_a_ch DEFERRED;
Constraint wurde festgelegt.
SQL> SELECT constraint_name, deferrable, deferred
2 FROM user_constraints
3 WHERE constraint_name = 'TEST_A_CH';
CONSTRAINT_NAME DEFERRABLE DEFERRED
------------------------------ -------------- ---------
TEST_A_CH DEFERRABLE IMMEDIATE
SQL>
Do you know of a way to find this out?
May 28, 2010 - 8:01 am UTC
well, you would have set it that way wouldn't you?
I'm not sure that information exists anywhere exposed as a queryable view - you sort of control it, you already "know" don't you ?
If you need it to BE in a certain state (eg: if you are trying to see if it is deferred so as to make it immediate or vice versa) just set it the way you want it set, no need to check
deferrable constraint
A reader, October 04, 2010 - 10:32 pm UTC
Hi Tom,
1) I read and noticed while testing that while creating a table with
deferrable clause the index that is being created is a non-unique(shown in UNIQUENESS column of user_indexes). My create tabl;e statement is:
create table emp11
(id number(5) primary key
deferrable using index storage(initial 100k next 100k)
tablespace ind01);
What may be reason behind this? Moreover when I tried to insert duplicate value I failed because of
unique constraint violation error. Why is that so when my Index is already non unique?
2) What is the use of "using no index" clause?
3) Can we predict the default name of the unique index created during the primary key declaration?
October 05, 2010 - 12:09 pm UTC
in order for a primary key to be deferrable - that primary key MUST SUPPORT DUPLICATES for some period of time.
Hence the index - by definition - cannot be unique, it must be non-unique - because a deferrable primary key BY DEFINITION - supports duplicates.
By default, a deferrable constraint is "immediate", you can set it deferred as you like
ops$tkyte%ORA11GR2> create table t
2 (id number(5) primary key deferrable )
3 /
Table created.
ops$tkyte%ORA11GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C0016382) violated
<b>by default, immediate, but you can change that:</b>
ops$tkyte%ORA11GR2> rollback;
Rollback complete.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set constraints all deferred;
Constraint set.
ops$tkyte%ORA11GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA11GR2> select * from t;
ID
----------
1
1
<b>duplicates - but when we commit:</b>
ops$tkyte%ORA11GR2> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (OPS$TKYTE.SYS_C0016382) violated
<b>we catch it</b>
2) where do you see a "using NO index" clause?
3) you have the ability to name the constraint, if you do - the index will be named after the constraint.
you also have th eability to name the index, if you do - you named it, you can predict it.
ops$tkyte%ORA11GR2> create table t (id number(5) constraint t_pk primary key deferrable );
Table created.
ops$tkyte%ORA11GR2> select index_name from user_indexes where table_name = 'T';
INDEX_NAME
------------------------------
T_PK
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> drop table t;
Table dropped.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t ( id number
2 constraint t_pk primary key
3 using index (create UNIQUE index t_idx on t(id) ) )
4 /
Table created.
ops$tkyte%ORA11GR2> select index_name from user_indexes where table_name = 'T';
INDEX_NAME
------------------------------
T_IDX
so you can not only predict it, you can CONTROL it.
Don't use unique when creating indexes for deferrable constraints.
Shannon Severance, October 05, 2010 - 12:35 pm UTC
In context of the last comment being a question about deferrable primary key constraints: When explicitly creating the index for deferrable primary key or unique key constraints, the index must not be unique, for the reason Tom explained earlier.
SQL> create table t ( id number
2 constraint t_pk primary key deferrable
3 using index (create unique index t_idx on t(id)))
4 /
create table t ( id number
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.
SQL> create table t ( id number
2 constraint t_pk primary key deferrable
3 using index (create index t_idx on t(id)))
4 /
Table created.
deferrable constraints
A reader, October 05, 2010 - 3:19 pm UTC
Hi Tom,
1) I had create a table with the below command:
create table emp11
(id number(5) primary key deferrable);
Now I should always expect the table is created with default "initial immediate" clause and thus the unique constraint violation will happen as soon as I want to insert duplicate value in the table.
Then why the UNIQUENESS column of
USER_INDEXES view is showing that index created on that table is "
NONUNIQUE"? If here a non-unique index is created, who is responsible to prevent the insertion? Is it the same index which depends upon the deferrable clause we are using("initially immediate" or "initially deferred")?
2) Can I predict the index name to be created in the above command execution?
October 06, 2010 - 4:44 am UTC
1) because you can type in:
SQL> set constraints all deferred;
and the constraint WILL NOT be immediate. It defaults to immediate but can be changed in your transaction at will. Therefore, the column MUST support duplicates for some period time.
Hence a unique index would not work.
2) page up, already asked and already answered.
deferrable constraints
Martin Vajsar, October 06, 2010 - 12:55 am UTC
Hi reader,
1) Index created for a deferrable primary key is always nonunique, even for initially immediate constraint. The reason is you can change the constraint to deferred state anytime and the index must support it, which would not be possible if it was unique. So the index is created the same for both initially deferred and initially immediate constraints.
I assume the constraint checking mechanism is the same for immediate and deferred constraints, only it hapens at different times - at the end of SQL for immediate constraints and at commit for deferred constraints. The details of this processing are not published and may change from release to release - I got this answer from Tom some time ago ;)
2) I don't think there is a practical way to get name that would be assigned to a constraint, the number is probably assigned from a sequence and you cannot "peek" the following sequence value. Moreover, if another user created an unnamed constraint in the meantime, the name you would get would not be valid anymore.
However, you can best avoid this question if you name your constraints when you create them, eg.
create table emp11
(id number(5) constraint WHATEVER_NAME_I_LIKE primary key deferrable);
System generated names of constraints can even change when you export and import tables, so it is really a good idea to supply your own names.
ORA-02292
Thomas, April 05, 2011 - 9:49 am UTC
I have a strange problem in a production system,
which I don't understand.
I have a child table (~10 million records) with an
ID column (primary key) that references the primary
key of a parent table (also ~10 million records).
I have a global temporary table that contains a batch of
IDs to delete from both tables.
Within a transaction, I delete the relevant rows
from the child table and then delete the relevant
rows from the parent table. There are no deletion
rules on the foreign key constraints and there are
no triggers in place. Both the ID columns of the
parent and child table are indexed. The foreign key
constraint is not deferrable. The tables are not
partitioned. The isolation mode is read committed.
First I fill the temporary table, then I do
DELETE FROM child_table a WHERE EXISTS (select null from tmp_user_ids b where a.id=b.id);
DELETE FROM parent_table a WHERE EXISTS (select null from tmp_user_ids b where a.id=b.id);
COMMIT;
Sometimes I get an ORA-02292 from the foreign key
constraint from the child to the parent table during
execution. However, I have no clue what might be
happening so that the foreign key violation in my
session is possible.
It seems to me that even if rows are inserted,
updated or deleted from the parent or child table
in parallel by another session, I should not be able
to get a foreign key constraint violation in my session.
An of course, the temporary table my session sees cannot
be changed by another session in parallel.
Can you think of any scenario where getting such an error
is indeed possible in my session?
April 12, 2011 - 12:32 pm UTC
someone committed a child record after you STARTED:
DELETE FROM child_table a WHERE EXISTS (select null from tmp_user_ids b where
a.id=b.id);
and before you:
DELETE FROM parent_table a WHERE EXISTS (select null from tmp_user_ids b where
a.id=b.id);
Rizwan ghadiyali, May 13, 2011 - 5:56 am UTC
Hi tom ,
I am having a parent table and a child table .. There is data in child table which is not present in parent table .. (parent table is empty )
But still i can see foreign key Constraint is ENABLED and VALIDATED .. I suppose it can be ENABLED but not VALIDATED. Why is this happening .. any idea ..
Below is detail
12:24:23 SQL> select owner , CONSTRAINT_NAME , CONSTRAINT_TYPE , TABLE_NAME , R_OWNER , R_CONSTRAINT_NAME , STATUS , DEFERRABLE , DEFERRED , VALIDATED from dba_constraints where constraint_name ='OSCF_STUD01_INBL01';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
R_OWNER R_CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------ ------------------------------ -------- -------------- --------- -------------
OSIRIS OSCF_STUD01_INBL01 R OST_STUDENT
OSIRIS OSCP_INBL01 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
12:25:56 SQL> select owner , CONSTRAINT_NAME , CONSTRAINT_TYPE , TABLE_NAME , R_OWNER , R_CONSTRAINT_NAME , STATUS , DEFERRABLE , DEFERRED , VALIDATED from dba_constraints where constraint_name ='OSCP_INBL01';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
R_OWNER R_CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
------------------------------ ------------------------------ -------- -------------- --------- -------------
OSIRIS OSCP_INBL01 P OST_INSCHRIJFBLOKKADEREDEN
ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
select * from dba_cons_columns where constraint_name in ('OSCP_INBL01','OSCF_STUD01_INBL01');
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
---------- ------------------------------ ------------------------------ -------------------- ----------
OSIRIS OSCP_INBL01 OST_INSCHRIJFBLOKKADEREDEN BLOKKADE_REDEN 1
OSIRIS OSCF_STUD01_INBL01 OST_STUDENT BLOKKADE_REDEN 1
12:31:33 SQL> select blokkade_reden , count(*) from ost_student group by blokkade_reden ;
BLOKKADE_REDEN COUNT(*)
---------------- ----------
154488
ONGEWENST GEDR 1
VERTROKKEN 13
12:50:07 SQL> select count(*) from ost_inschrijfblokkadereden ;
COUNT(*)
----------
0
12:31:17 SQL> select blokkade_reden from ost_student where blokkade_reden is not null
12:31:20 2 minus
12:31:25 3 select blokkade_reden from ost_inschrijfblokkadereden;
BLOKKADE_REDEN
----------------
ONGEWENST GEDR
VERTROKKEN
Elapsed: 00:00:00.04
May 13, 2011 - 12:56 pm UTC
please contact support for that.
What about performance ?
Marcus Rangel, May 17, 2011 - 12:24 pm UTC
Tom, what about performance ? Is it faster to validate 1000000 rows at the end of the transaction than validating them one by one a million times ?
May 18, 2011 - 9:44 am UTC
give me more information here. are we talking about some data that always has 1,000,000 row transactions (eg: data warehouse) or are we talking about a periodic exceptional case?
that'll drive the decision between using constraints to validate the data - or just using constraints as metadata and validating the data via sql perhaps.
but if we use a constraint to validate the data - you would use immediate above deferrable in all cases EXCEPT when you have a need to use the qualities of deferrable..
About performance
Marcus Rangel, May 18, 2011 - 1:06 pm UTC
I meant in general. For example, we know that this:
insert into some_table
select * from some_big_table;
Is always better (faster) than this:
for i in ( select * from some_big_table ) loop
insert into some_table values (i.data1, i.data2, ...);
end loop;I was wondering if this:
set constraints all deferred;
begin
for i in ( select * from some_table ) loop
insert into some_child_table values (i.data1, i.data2, ...);
end loop;
commit;
end;Would also be always faster than this:
set constraints all immediate;
begin
for i in ( select * from some_table ) loop
insert into some_child_table values (i.data1, i.data2, ...);
end loop;
commit;
end;I did some testing and it seems to make no difference...
May 18, 2011 - 11:37 pm UTC
but there isn't an "in general" here - there are two explicit cases:
a) I only bulk load this table
b) I use single row operations on this table
And they have different answers. If you do (a), you might consider using immediate rely disable novalidate constraints and use parallel SQL to verify the data conforms to the rules. Your load will be fast (no constraints to validate row by row) and then your validation is pretty fast (well written parallel sql). If you do (b) you will use immediate constraints unless you HAVE to use deferred constraints. The reason is - if you have a deferrable constraint, the optimizer cannot use it later to optimize queries (it is as if the constraint doesn't exist, because the constraint doesn't have to always be enforced, so the optimizer cannot rely on it)
Deferred constraints have various drawbacks and overheads associated with them. You would only use them when you have a 100% identified need for their "qualities" - what they do.
And if you compare something like this:
drop table t1;
drop table t2;
drop table t;
create table t ( x int primary key );
insert into t values ( 1 );
create table t1
( x int primary key deferrable,
y int unique deferrable,
z int check (z>0) deferrable,
a int references t deferrable
)
/
create table t2
( x int primary key ,
y int unique ,
z int check (z>0) ,
a int references t
)
/
set constraints all deferred;
@trace
insert into t1
select rownum, rownum, rownum, 1
from stage;
commit;
@tk "sys=yes"
@trace
insert into t2
select rownum, rownum, rownum, 1
from stage;
commit;
@tk "sys=yes"
(@trace enables sql trace, @tk finds the name of my sessions trace file, disconnects, runs tkprof on it)
You would see the deferrable constraints taking something like:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 1.60 3.95 15 3211 373820 72155
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 1.60 3.96 15 3211 373820 72156
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 93 0.00 0.00 0 0 0 0
Execute 109 0.01 0.01 0 44 38 15
Fetch 163 0.00 0.00 0 261 0 82
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 365 0.02 0.02 0 305 38 97
and the immediate constraints taking:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.52 0.52 0 3358 11262 72155
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.52 0.53 0 3358 11262 72156
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 77 0.00 0.00 0 0 0 0
Execute 93 0.02 0.02 3 45 322 15
Fetch 131 0.00 0.00 0 213 0 66
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 301 0.02 0.03 3 258 322 81
So I observed a large difference between the two in general, CPU wise and IO wise. It takes a lot more current mode gets to verify the constraints in deferrable mode.
Awesome
Marcus Rangel, May 19, 2011 - 10:08 am UTC
Now I got it, thank you very much.
Possible Oracle bug with deferrable FK constraints?
Martin Vajsar, March 06, 2012 - 2:44 am UTC
Hallo Tom,
currently we've run to a very strange issue on Oracle 11.2.0.1. There is a validated, enabled, deferrable initially immediate FK constraint (as seen in data dictionary); and a row that violates the constraint. We've internal audit tables that seem to indicate that the violating row has been created by a process which operates with that constraint in deferred mode, and that this occurred after the date the constraint was last modified.
I've found this page (
http://www.pythian.com/news/9881/deferrable-constraints-in-oracle-11gr2-may-lead-to-logically-corrupted-data/ ) indicating that there is a bug allowing deferred PK constraint to be violated in some circumstances. I was able to reproduce that on my system (different from the client's database).
The database where the FK violation has occured belongs to a client and we've got only limited access. I was not yet able to reproduce the FK violation on my system. We'll try to make them contact support on this, though it might be easier to persuade them with more details in hand. Don't you have some "insider" information regarding that bug, especially whether it affects FK constrains as well as PK constraints?
Thanks,
Martin Vajsar
March 06, 2012 - 6:55 am UTC
they will have to contact support for an issue like this.
Multiple foreign key
A reader, March 10, 2012 - 9:40 pm UTC
Hi Tom,
I need your suggestion for creating foreign key relation. Is it possible to have two different foreign keys on same tables? If one is not satisfied then second must be.. I mean any one of the two foreign keys must be satisfied.
Create Table Tbl1(t1_col1 numbrer, t1_col2 varchar2(10), t1_col3 varchar2(10));
create table tbl2(t2_col1 varchar2(10),t2_col2 varchar2(10));
now t1_col2 has foreign relation with t2_col1 and t1_col3 has with t2_col2..
Any of these two relation must be satisfied. Is it possible in foreign key or we need a trigger on table.
Please suggest.
March 12, 2012 - 7:38 am UTC
It is not clear when you say "now t1_col2 has foreign relation with t2_col1 and t1_col3 has with t2_col2..", but I'll assume the foreign key goes FROM Tbl1 TO tbl2.
ops$tkyte%ORA11GR2> create table tbl2
2 (t2_col1 varchar2(10) UNIQUE,
3 t2_col2 varchar2(10) UNIQUE
4 );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create Table Tbl1
2 (t1_col1 number,
3 t1_col2 varchar2(10) references tbl2(t2_col1),
4 t1_col3 varchar2(10) references tbl2(t2_col2),
5 check ( coalesce( t1_col2, t1_col3 ) is not null )
6 );
Table created.
Follow up
A reader, March 12, 2012 - 9:50 am UTC
One more question regarding the foreign keys. One of the requirements is to have kind of UPDATE CASCADE on foreign key. I read on your site that we should never update the Primary key. but in case I have to, then use DEFERRED CONSTRAINTS.
How can we use deferred constraint and update the subsequent child keys when Parent key changes? could you please provide me an example.
Thanks for the replies and support.
March 12, 2012 - 10:21 am UTC
make the foreign key constraints deferrable when you create them (just add that keyword)
then,
set constraints all deferred;
do your updates
set constraints all immediate;
commit;
Follow up
A reader, March 12, 2012 - 10:30 am UTC
Hi Tom,
Thanks for the idea. One question still remains.. Lets say I deferred the constraints while creating table. Now the application which is a web based tool, uses the parent table and updates the key there. Should I run any PL SQL procedure which will in turn update the child(foreign keys) of the primary key of parent table?
It is going to be used by many users through online web based interaction, so I still understand its a bad business requirement but in case you have any ideas, please let me know.
Thanks again for your time.
March 12, 2012 - 11:00 am UTC
you have to update the keys yourself. You would update the parent, then update the child records for that parent, their children and so on.
I would suggest not updating the primary key, if you feel you must, it is not the primary key - it is just a unique constraint.
check constraint in ('Y',NULL) - PUZZLE
Andrew, March 16, 2012 - 8:19 am UTC
Dear Tom
I have been presented with a puzzle
Create table T (fld varchar2(1));
Alter table T add CHECK (fld in ('Y', NULL));
Insert into T Values ('X');
All statements execute.
What causes this constraint to be inaffective..?
Regards
A
March 16, 2012 - 8:49 am UTC
is fld in (NULL) -
ops$tkyte%ORA11GR2> select * from dual where (dummy in (NULL));
no rows selected
ops$tkyte%ORA11GR2> select * from dual where NOT(dummy in (NULL));
no rows selected
ops$tkyte%ORA11GR2>
It is neither true, nor false - it is unknown.
A check constraint that evaulates to NULL is always "ok", it passes. A check constraint has to evaluate to FALSE in order to fail.
If you want to do that check, you would
check (fld = 'Y' or fld is NULL);
Inconsistent TRUE/FALSE evaluation
Andrew, March 22, 2012 - 9:16 am UTC
Dear Tom,
Thank you for this explanation -
However, I consider this inconsistent with TRUE/FALSE evaluation within a trigger:
Create table T1 (vx varchar2(1));
Create or replace trigger T1_TB2
before insert on T1
for each row
Begin
IF :new.vx in ('X', NULL)
Then
:new.vx := '*';
Else
:new.vx := '?';
END IF;
End;
/
Insert into T! Values ('A');
Insert into T1 Values ('X');
Select * from T1;
V
-
?
*
Same is the case when CASE instead IF statement is used
Now:
Insert into T1 Values ('')
Select * from T1;
V
-
?
*
?
I then changed the CASE statement into:
...
CASE
When :new.vx NOT in ('X', NULL)
Then
:new.vx := '#';
Else
:new.vx := '?';
END CASE;
And indeed ANY data entered evaluates into '?'
So indeed the NOT in clause evaluates to ELSE always which corresponds to your response earlier - but the same is not the case when an IN ('X',NULL) clause is used
I'd say it is at least confusing ... hence the best is NOT to use NULL in any clauses except (as you mentioned above) with "IS" i.e. "IS NULL" or "IS NOT NULL"
Otherwise it appears to be inconsistent with check constraints
Regards
A
March 22, 2012 - 10:34 am UTC
you used if/else
You need to use if/elsif/else
ops$tkyte%ORA11GR2> Create table T1 (a varchar2(1), b varchar2(20) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> Create or replace trigger T1_TB2
2 before insert on T1
3 for each row
4 Begin
5 IF :new.a in ('X', NULL)
6 Then
7 :new.b := 'it was in x,null';
8 elsif NOT(:new.a in ('X',null) )
9 then
10 :new.b := 'it was NOT in x,null';
11 else
12 :new.b := 'we do not know...';
13 end if;
14 End;
15 /
Trigger created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> Insert into T1(a) Values ('A');
1 row created.
ops$tkyte%ORA11GR2> Insert into T1(a) Values ('X');
1 row created.
ops$tkyte%ORA11GR2> select * from t1;
A B
- --------------------
A we do not know...
X it was in x,null
ops$tkyte%ORA11GR2> If you have three outcomes, you need at least two checks!
check constraint in ('Y',NULL) - PUZZLE
Andrew, March 27, 2012 - 6:11 pm UTC
Many thanks Tom
Please clarify
Siva, September 20, 2012 - 8:15 am UTC
Hi Tom,
I thought the hint would suppress constraint error and insert would finish with 1 row only.
SQL>
SQL> show rel
release 1102000200
SQL>
SQL> create table test_table(x int primary key);
Table created.
SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX */ into test_table
2 select 1 from obj where rownum<= 10
3 /
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX */ into test_table
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C00180765) violated
September 26, 2012 - 11:13 am UTC
SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(test_table,(x)) */ into test_table
2 select 1 from obj where rownum<= 10
3 /
1 row created.
SQL> select * from test_table;
X
----------
1
SQL>
tell us what you want to ignore
Why does Oracle add CHECK constraints to query predicates?
Matthew McPeak, December 19, 2012 - 2:53 pm UTC
Tom,
Can you explain these results? (The only difference in these two queries is the materialize hint)
WITH a AS (SELECT /*+ MATERIALIZE */
* FROM verpcust.verp_vps_zone_port_plan)
SELECT count(*)
FROM a
WHERE net_plan_quantity < 0
ORDER BY net_plan_quantity
==>
COUNT(*)
--------------------------------------
7
1 row(s) fetched
WITH a AS (SELECT
* FROM verpcust.verp_vps_zone_port_plan)
SELECT count(*)
FROM a
WHERE net_plan_quantity < 0
ORDER BY net_plan_quantity
==>
COUNT(*)
--------------------------------------
0
1 row(s) fetched
The table in question has a CHECK constraint on it, supposedly preventing negative values for NET_PLAN_QUANTITY.
Due to an 11.2.0.2 bug with the MERGE statement, we have some data in the table that violates this constraint.
However, I only see the bad records if I SELECT * from the table with no where clause. If I add in any kind of predicate, Oracle also adds in the check check constraint conditions, hiding the bad records.
Do you know where in the Oracle documentation I can read about this behavior, if it is in fact, what Oracle is expected to do?
Thanks,
Matt
December 20, 2012 - 5:01 pm UTC
Oracle uses constraints to rewrite queries. If the constraint exists and we think it is validated and enabled - we will use it to rewrite whenever we want to.
Usually this is ONLY a problem when the constraint is "disable rely novalidate"
http://asktom.oracle.com/Misc/stuck-in-rut.html but you have encountered a situation where we have a "enable validate" constraint that "isn't true".
You'll want to drop that constraint and recreate it as
norely enable novalidate
that will protect future inserts, allow the existing bad data, but not generate a plan that relies on the constraint being true.
Thanks!
Matthew McPeak, December 21, 2012 - 4:56 am UTC
Great answer, thanks!