tag:blogger.com,1999:blog-141398222023-11-15T07:28:47.260-08:00RuleGenRandom thoughts on implementing data integrity constraints in OracleToon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-14139822.post-27324475136645833382012-01-29T08:09:00.000-08:002012-01-29T09:41:03.247-08:00Statement-level constraint validation: MERGE + MTIWhile working on release 3.1 of <a href="http://www.rulegen.com/" target="_blank">RuleGen</a> which will, among other things, support the MERGE and Multi-Table-Insert (MTI) statements, I decided to investigate how Oracle deals with validating declarative constraints during execution of these two types of DML statements. I discovered, what I consider bugs in this area, which I will demonstrate in this post. But before I do that, I'd like to discuss another phenomena first: how triggers fire during MERGE and MTI. Row triggers fire as expected: whenever a row is inserted, updated or deleted, the respective before and after row triggers will fire for the row. Statement triggers however fire in what may seem somewhat strange manner. But on second thought is actually the way you would want them to fire.<br />
<br />
<span style="font-size: large;"><b>Statement triggers firing during MERGE</b></span><br />
<br />
Here's the syntax for MERGE:<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">MERGE INTO [destination] a</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> USING [some source query] drv</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> ON ([join-conditions])</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN MATCHED THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> UPDATE SET ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> DELETE WHERE ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN NOT MATCHED THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INSERT ... VALUES ...;</span><br />
<div>
<br />
As of 10G the various sections above are optional. This gives us five scenarios to consider.</div>
<div>
<br /></div>
<div>
<b>Scenario 1: the merge statement only has the 'WHEN NOT MATCHED' section, like this:</b></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">MERGE INTO [destination] a</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> USING [some source query] drv</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> ON ([join-conditions])</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN NOT MATCHED THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INSERT ... VALUES ...;</span></div>
<div>
<br />
In this case only the before and after <i>INSERT</i> statement-level triggers on the destination table will fire. By the way this is irrespective of whether the MERGE will actually insert rows into that table or not. Oracle will determine at parse time which sections are present in the MERGE statement text and from that ensure that these triggers are fired.</div>
<div>
<br /></div>
<div>
<div>
<b>Scenario 2: the merge statement has both the 'WHEN NOT MATCHED' and 'WHEN MATCHED' (without the DELETE) sections, like this:</b></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">MERGE INTO [destination] a</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> USING [some source query] drv</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> ON ([join-conditions])</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN MATCHED THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> UPDATE SET ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN NOT MATCHED THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INSERT ... VALUES ...;</span></div>
<div>
<br />
In this case the before and after <i>INSERT</i> and <i>UPDATE</i> statement-level triggers will fire.</div>
</div>
<div>
<br /></div>
<div>
<div>
<div>
<b>Scenario 3: the merge statement has both the 'WHEN NOT MATCHED' and 'WHEN MATCHED', with the DELETE sections, like this:</b></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">MERGE INTO [destination] a</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> USING [some source query] drv</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> ON ([join-conditions])</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN MATCHED THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> UPDATE SET ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> DELETE WHERE ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN NOT MATCHED THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INSERT ... VALUES ...;</span></div>
<div>
<br />
In this case all three before and after statement-level triggers will fire: <i>INSERT</i>, <i>UPDATE</i> and <i>DELETE</i>.</div>
</div>
</div>
<div>
<br /></div>
<div>
<b>Scenario 4: the merge statement only has 'WHEN MATCHED' (without the DELETE) section, like this:</b></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">MERGE INTO [destination] a</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> USING [some source query] drv</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> ON ([join-conditions])</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN MATCHED THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> UPDATE SET ...</span><span style="font-family: 'Courier New', Courier, monospace;">;</span></div>
<div>
<br />
In this case only the before and after <i>UPDATE</i> statement-level trigger will fire.</div>
<br />
<div>
<b>Scenario 5: the merge statement only has 'WHEN MATCHED' with the DELETE section, like this:</b></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">MERGE INTO [destination] a</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> USING [some source query] drv</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> ON ([join-conditions])</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN MATCHED THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> UPDATE SET ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> DELETE WHERE ...;</span></div>
<div>
<br />
In this case the before and after <i>UPDATE</i> and <i>DELETE</i> statement-level triggers will fire.</div>
<br />
<div>
So there they are: five possible combinations of statement-level triggers firing during a MERGE statement. All directly dependent upon which sections appear in the statement.</div>
<div>
<br /></div>
<div>
Note that when you've defined a combined statement-level trigger (one created using 'after/before update or delete or insert'), this trigger potentially fires one, two, or three times. Due to a bug however (bug 9865211 see MOS) the three known booleans INSERTING, UPDATING and DELETING that you can inspect to find out why the combined statement-level trigger is firing, all return FALSE. This is supposedly fixed though in the recent 11.2.0.3 patchset. But even then you really cannot tell the difference between whether for instance the insert trigger is firing due to a MERGE or due to a regular INSERT statement. A fourth boolean, say MERGING, would be convenient for that.</div>
<div>
<br /></div>
<div>
<b><span style="font-size: large;">Statement triggers firing during MTI</span></b></div>
<div>
<br />
Here's the syntax for Multi-Table-Insert:<br />
<span style="font-family: 'Courier New', Courier, monospace;">INSERT ALL|FIRST</span></div>
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN [condition] THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INTO [table] VALUES ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN [condition THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INTO [table] VALUES ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">[some query];</span><br />
<br />
As with the MERGE statement above, Oracle will determine during parse time which before and after statement level INSERT triggers will fire. Suppose you've written an MTI statement like this:<br />
<span style="font-family: 'Courier New', Courier, monospace;">INSERT ALL|FIRST</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN [condition] THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INTO EMP VALUES ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN [condition] THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INTO DEPT VALUES ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">[some query];<br />
<br />
</span>Then the before insert statement triggers of both tables EMP and DEPT will fire, as well as the after insert statement triggers of tables EMP and DEPT. Not too surprising so far. However we can also write an MTI statement like this (two sections insert into EMP, one into DEPT):<br />
<span style="font-family: 'Courier New', Courier, monospace;">INSERT ALL|FIRST</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN [condition] THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INTO EMP VALUES ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN [condition] THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INTO EMP VALUES ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> WHEN [condition] THEN</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> INTO DEPT VALUES ...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">[some query];</span><br />
<br />
Note that in this case Oracle will fire the before and after statement INSERT trigger of the EMP table twice each!<br />
So timewise something like this will happen:<br />
<span style="font-family: 'Courier New', Courier, monospace;">before-insert-statement trigger EMP fires.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">before-insert-statement trigger EMP fires again.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">before-insert-statement trigger DEPT fires.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">MTI-executes.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">after-insert-statement trigger EMP fires.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">after-insert-statement trigger EMP fires again.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">after-insert-statement trigger DEPT fires.</span><br />
<br />
Bug or feature?<br />
Anyway using this 'feature' we can write an MTI-statement that will fire the before and after INSERT triggers on a table, any number of times: just add more WHEN branches for that table. And the order in which the triggers fire seem to be determined by the order in which the tables appear inside the MTI statement.<br />
<br />
<span style="font-size: large;"><b>Statement-level constraint consistency</b></span><br />
<br />
RuleGen implements integrity constraints by generating the necessary triggers for them. One of the goals of RuleGen, is that integrity constraints implemented by triggers, should have the same 'features' as declarative constraints have. That is: you should be able to enable/disable them, have them validate immediately or deferred. And in the case of immediate checking, be immune to the order in which rows happen to get changed by the triggering DML statement.<br />
<br />
So, having investigated the way triggers fire for these two types of statements, and thinking about how to make this all work correctly and efficiently when generating code with RuleGen, I was struck with the thought of how Oracle deals with key (primary/unique) and foreign key validation during merge and mti.<br />
<br />
As some of you might know, when we execute the three regular DML statement types (insert, update and delete) Oracle works as expected: intermediate violations during statement execution are allowed, as long as they are 'fixed' at completion of the DML execution. Here's an example.<br />
<br />
Let's setup a simple table with a few constraints:<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">create table p</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">(i number not null</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">,j number not null</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">,k number not null</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">,primary key(i)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">,foreign key (j) references p(i)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">,unique(k));</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into p values(1,1,1);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into p values(2,2,2);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into p values(3,3,3);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">commit;</span><br />
<div>
Using an update statement we can switch key-values (primary or unique) without being confronted with the intermediate key-violations that is introduced during the statement execution. See below, we are switching key values 1 and 2 of both the primary key and the unique key:</div>
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">SQL> update p set i = case i</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 2 when 1 then 2</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 3 when 2 then 1</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 4 end</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 5 where i in (1,2)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 6 /</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />2 rows updated.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />SQL> update p set k = case k</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 2 when 1 then 2</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 3 when 2 then 1</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 4 end</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 5 where k in (1,2)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 6 /</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />2 rows updated.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />SQL></span><br />
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select * from p;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">More...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /> I J K</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 2 1 2</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 1 2 1</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />3 rows selected.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />SQL></span><br />
<br />
In a similar way Oracle allows intermediate foreign key violations. Below we've contrived an update statement that introduces a new key value of 4 but will first change a foreign key value to 'point to' this new key value.<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">SQL> rollback;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />Rollback complete.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />SQL> select * from p;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">More...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /> I J K</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 1 1 1</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 2</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />3 rows selected.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />SQL> update (select * from p order by i desc)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 2 set j = case j</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 3 when 1 then 4</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 4 when 3 then 4</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 5 else j</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 6 end</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 7 ,i = case i</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 8 when 3 then 4</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 9 else i</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 10 end</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 11 /</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />3 rows updated.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />SQL> select * from p;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">More...</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br /> I J K</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 1 4 1</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 2</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 4 4 3</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />3 rows selected.</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"><br />SQL></span><br />
<div>
<br />
And again Oracle allows this: we are not confronted with an intermediate foreign key violation due to the order in which we've designed the above update statement to modify the rows.</div>
<div>
<br /></div>
<div>
So what about this 'statement-level constraint consistency' feature when we deal with merge and mti statements?</div>
<div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> rollback;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">Rollback complete.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select * from p;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">More...</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> I J K</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 1 1 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">3 rows selected.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> merge into p</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 using (select 1 as x from dual union all</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 select 2 as x from dual) drv</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 on (drv.x = p.i)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 5 when matched then</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 6 update set i = case i</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 7 when 1 then 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 8 when 2 then 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 9 end</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 10 /</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">on (drv.x = p.i)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> *</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">ERROR at line 4:</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">ORA-38104: Columns referenced in the ON Clause cannot be updated: "P"."I"</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> merge into p</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 using (select 1 as x from dual union all</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 select 2 as x from dual) drv</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 on (drv.x = p.i)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 5 when matched then</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 6 update set k = case k</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 7 when 1 then 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 8 when 2 then 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 9 end</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 10 /</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">2 rows merged.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select * from p;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">More...</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> I J K</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 1 1 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">3 rows selected.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL></span></div>
</div>
<div>
<br />
So apart from the <span style="font-family: 'Courier New', Courier, monospace;">ORA-38104</span> which exhibits an obvious restriction of the merge statement, Oracle allows us to switch key values with merge.</div>
<div>
<br /></div>
<div>
And what about foreign key statement level consistency? Here's a merge that introduces a new parent key value 4 and updates an existing foreign key value to 4. The first merge processes the rows in a different order as does the second merge:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> rollback;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">Rollback complete.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select * from p;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">More...</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> I J K</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 1 1 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">3 rows selected.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> merge into p</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 using (select 1 as x from dual union all</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 select 4 as x from dual</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 order by x asc) drv</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 5 on (drv.x = p.i)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 6 when matched then</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 7 update set j = 4</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 8 when not matched then</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 9 insert values(4,4,4)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 10 /</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">merge into p</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">*</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">ERROR at line 1:</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">ORA-02291: integrity constraint (WORK.SYS_C0019049) violated - parent key not found</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> merge into p</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 using (select 1 as x from dual union all</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 select 4 as x from dual</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 order by x desc) drv</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 5 on (drv.x = p.i)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 6 when matched then</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 7 update set j = 4</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 8 when not matched then</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 9 insert values(4,4,4)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 10 /</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">2 rows merged.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select * from p;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">More...</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> I J K</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 1 4 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 4 4</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">4 rows selected.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL></span></div>
</div>
<div>
<br /></div>
<div>
Again: bug or feature? The first merge introduces a temporary violation in that it updates an fk-value to 4 prior to the insertion of parent 4. As you can see with the second merge processing the insert before the update, all goes well. Row processing order dependent behavior is not what we want (sounds a lot like why we have the mutating table error, right?).</div>
<div>
<br /></div>
<div>
So it's a bug if you ask me.</div>
<div>
<br /></div>
<div>
Now what about the multi-table-insert? Does it allow intermediate foreign key violations? Here's an example where we add two rows to table p, twice, with different row-orders.</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> rollback;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">Rollback complete.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select * from p;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">More...</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> I J K</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 1 1 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">3 rows selected.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> insert all</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 into p values(5,4,5)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 into p values(4,4,4)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 select dummy</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 5 from dual</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 6 /</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">2 rows created.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> rollback;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">Rollback complete.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> insert all</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 into p values(4,4,4)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 into p values(5,4,5)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 select dummy</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 5 from dual</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 6 /</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">2 rows created.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select * from p;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">More...</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> I J K</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 1 1 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 4 4</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 5 4 5</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">5 rows selected.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL></span></div>
</div>
<div>
<br /></div>
<div>
The former MTI statement introduces a child row under key 4 prior to inserting parent 4. Oracle allows it. To me even more proof that the issue shown with the merge statement is indeed a bug.</div>
<div>
<br /></div>
<div>
But wait a minute what about a foreign key between two different tables? Let's introduce a second table called c.</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">create table c</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">(l number not null primary key</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">,i number not null references p(i))</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">/</span></div>
</div>
<div>
<br /></div>
<div>
And write an MTI statement that introduces parent 4 and child 4 in table c:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> rollback;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">Rollback complete.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select * from p;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">More...</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> I J K</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 1 1 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">3 rows selected.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> insert all</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 into c values(1,4)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 into p values(4,4,4)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 select dummy</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 5 from dual</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 6 /</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">insert all</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">*</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">ERROR at line 1:</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">ORA-02291: integrity constraint (WORK.SYS_C0019053) violated - parent key not found</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> rollback;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">Rollback complete.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> insert all</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 into p values(4,4,4)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 into c values(1,4)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 select dummy</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 5 from dual</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 6 /</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">2 rows created.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL> select * from p;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">More...</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> I J K</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">---------- ---------- ----------</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 1 1 1</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 2 2 2</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 3 3 3</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> 4 4 4</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">4 rows selected.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">SQL></span></div>
</div>
<div>
<br /></div>
<div>
Ah! Got you again. Bug or feature? Definitely a bug IMHO.</div>
<div>
<br /></div>
<div>
So much for the fun that I've had the past week investigating how to support Merge and Multi Table Insert with RuleGen.</div>Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com21tag:blogger.com,1999:blog-14139822.post-31564040966923984162009-06-14T09:54:00.001-07:002009-07-08T04:14:34.513-07:00Two example constraintsIn <a href="http://thehelsinkideclaration.blogspot.com/2009/06/continuing-with-part-2-of-helsinki.html">a post over at the Helsinki declaration blog</a>, I've introduced two rather simple constraints in a two table database design holding a BOOK and ORDERLINE table.<br /><ul><li>A user cannot have more than a (sum) total of fifty books in ORDERLINE.</li><li>A title cannot be ordered more than a (sum) total of eighty in ORDERLINE.<br /></li></ul>As assertions we would specify these as follows:<br /><pre name="code" class="Sql">create assertion no_more_than_50_per_user as<br />check(not exists(select 'a user with more than 50 books'<br /> from (select username,sum(amount) as total<br /> from orderline<br /> group by username)<br /> where total > 50)<br />);<br /><br />create assertion no_more_than_80_per_title as<br />check(not exists(select 'a title with more than 80 ordered'<br /> from (select ean,sum(amount) as total<br /> from orderline<br /> group by ean)<br /> where total > 80)<br />);<br /></pre>Implementing these two constraints with the RuleGen framework is fairly straightforward. Let's start with the first one, no_more_than_50_per_user. What we first need to tell RuleGen is <span style="font-style: italic;">when</span> this constraints needs to be validated:<br /><ul><li>on inserts?</li><li>on deletes?</li><li>on updates?</li></ul>Now obviously whenever an ORDERLINE row is deleted, there is no need to validate this constraint. When we insert an ORDERLINE the constraint must be validated, as the insert could introduce a total of more than fifty books for a user. Likewise when we update the AMOUNT column and increase its current value, the constraint must be validated too.<br /><br />The way we tell RuleGen this is by specifying (in this case) two queries:<br /><pre name="code" class="Sql">select username<br />from inserted_rows;<br /><br />select new_username as username<br />from updated_rows<br />where new_amount > old_amount;</pre>The objects selected from by these queries (inserted_rows and updated_rows) are maintained by the framework and will hold the rows inserted/updated by the current transaction. We select the username value in above queries to tell RuleGen that this constraint is to be validated <span style="font-style: italic;">per username</span>. RuleGen will execute these queries, and upon rows fetched from them, continue with the next step.<br /><br />Next we need to tell RuleGen <span style="font-style: italic;">how</span> this constraint is to be validated. This is done by specifying yet another query. A query on the tables that are constrained. The goal of this query is to seek a violation of the constraint. Here it is.<br /><pre name="code" class="Sql">select 'Cannot have more than fifty books ordered for user '||p_username||'. Found: '||TOTAL||'.' as MSG<br />from (select sum(AMOUNT) as TOTAL<br /> from ORDERLINE<br /> where USERNAME = p_username)<br />where TOTAL > 50;</pre>When this query fetches a row, RuleGen will use the MSG-value to raise an exception, signalling that the constraint is violated.<br /><br />The second constraint is very much like the one demonstrated above. I'll leave it up to the reader to specify the RuleGen <span style="font-style: italic;">when</span> and <span style="font-style: italic;">how</span> for that one.Unknownnoreply@blogger.com0