In-database archiving and foreign keys

In-database archiving is a neat, new 12c-functionality that lets you logically “delete” rows by changing a flag in a column: A “soft-delete”. How does this functionality work in relation to foreign keys?

If your’e unfamiliar with in-database archiving, you might check out the 12.1 documentation and/or Tim Hall’s blog on the subject.

I thought that I had to check out how in-database archiving  behaves when you have foreign keys enabled in the system. My expectation is that fk’s won’t be respected since the rows are still there, but I wondered if the bug if encountered around ANSI re-architecture might indicate something fancy rewrite-logic in the background.

So here’s my little test case that shows that in-database archiving does not respect foreign keys. I.e. you might end up with a dataset that’s not consistent even though you might have foreign keys declared as “on delete cascade”. I also found that the outer join bug extends to full outer joins and switching off ANSI re-architecture doesn’t work as a workaround.

First I create a couple of tables with a foreign key between them, and then enable row archival.

SQL> SELECT banner FROM V$VERSION;
BANNER                                                                                              
--------------------------------------------------------------------------------                    
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production                        
PL/SQL Release 12.2.0.1.0 - Production                                                              
CORE	12.2.0.1.0	Production                                                                          
TNS for Linux: Version 12.2.0.1.0 - Production                                                      
NLSRTL Version 12.2.0.1.0 - Production                                                              

SQL> CREATE TABLE t1 (id PRIMARY KEY)
  2  AS
  3  SELECT rownum
  4  FROM dual
  5  CONNECT BY level <= 5;
Table created.

SQL> CREATE TABLE t2
  2  ( id PRIMARY KEY,
  3    fk_id)
  4  AS
  5  SELECT rownum, rownum
  6  FROM dual
  7  CONNECT BY level <= 3;
Table created.

SQL> ALTER TABLE t2 ADD CONSTRAINT t2_t1_fk
  2  FOREIGN KEY (fk_id) REFERENCES t1 (id)
  3  ENABLE VALIDATE;
Table altered.

SQL> SELECT * FROM t1;
        ID                                                                                          
----------                                                                                          
         1                                                                                          
         2                                                                                          
         3                                                                                          
         4                                                                                          
         5                                                                                          

SQL> SELECT * FROM t2;
        ID      FK_ID                                                                               
---------- ----------                                                                               
         1          1                                                                               
         2          2                                                                               
         3          3                                                                               

SQL> DELETE t1 WHERE id = 3;
DELETE t1 WHERE id = 3
*
ERROR at line 1:
ORA-02292: integrity constraint (VEI.T2_T1_FK) violated - child record found

The foreign key prevents us from deleting a row in the master table when there’s a child-record present.

Next we enable row archival and see what happens:

SQL> ALTER TABLE t1 ROW ARCHIVAL;
Table altered.

SQL> ALTER TABLE t2 ROW ARCHIVAL;
Table altered.

SQL> UPDATE t1 SET ora_archive_state = 'Deleted' WHERE id = 3;
1 row updated.
SQL> COMMIT;
Commit complete.

No errors, so that means that we now have a dataset that is inconsistent in respect to what you would expect from the enabled foreign key, since we have a child record pointing to a master record that is “deleted”:

A full outer join makes no sense due to the mentioned bug, the query returns 6 rows instead of 5:

SQL> SELECT /*full*/ t1.id T1_id, t2.id T2_id, t2.fk_id
2 FROM t1
3 FULL OUTER JOIN t2 ON t1.id =t2.fk_id;
     T1_ID      T2_ID      FK_ID
---------- ---------- ----------
         1          1          1
         2          2          2
         3
         4
         5
                    3          3
6 rows selected.

And switching off ANSI re-architecture doesn’t work either:

ALTER SESSION SET "_optimizer_ansi_rearchitecture"=FALSE;
Session altered.

SQL> SELECT /*full*/ t1.id T1_id, t2.id T2_id, t2.fk_id
2 FROM t1
3 FULL OUTER JOIN t2 ON t1.id =t2.fk_id;
     T1_ID      T2_ID      FK_ID
---------- ---------- ----------
         1          1          1
         2          2          2
         3
         4
         5
                    3          3
6 rows selected.

Therefore I`ll just list the tables separately:

SQL> SELECT * FROM t1;
        ID                                                                                          
----------                                                                                          
         1                                                                                          
         2                                                                                          
         4                                                                                          
         5                                                                                          

SQL> SELECT * FROM t2;
        ID      FK_ID                                                                               
---------- ----------                                                                               
         1          1                                                                               
         2          2                                                                               
         3          3                                                                               

SQL> INSERT INTO t2 (id, fk_id)
  2  VALUES (4, 3);
1 row created.

SQL> INSERT INTO t2 (id, fk_id)
  2  VALUES (6, 6);
INSERT INTO t2 (id, fk_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (VEI.T2_T1_FK) violated - parent key not found 

What happens when we create a new constraint – on what dataset is it validated? Turns out – it does not care about soft-deleted rows:

SQL> ALTER TABLE t2 DROP CONSTRAINT t2_t1_fk;
Table altered.

SQL> ALTER TABLE t2 ADD CONSTRAINT t2_t1_fk
  2  FOREIGN KEY (fk_id) REFERENCES t1 (id)
  3  ON DELETE CASCADE
  4  ENABLE VALIDATE;
Table altered.

With an on-delete-cascade fk – does it by any chance do something (delete/tag as deleted) about the child row?

SQL> ALTER TABLE t2 ADD CONSTRAINT t2_t1_fk
  2  FOREIGN KEY (fk_id) REFERENCES t1 (id)
  3  ON DELETE CASCADE
  4  ENABLE VALIDATE;
Table altered.

A regular delete also removed the child row:

SQL> DELETE t1 WHERE id = 2;
1 row deleted.
SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM t1;

        ID                                                                                          
----------                                                                                          
         1                                                                                          
         4                                                                                          
         5                                                                                          

SQL> SELECT * FROM t2;
        ID      FK_ID                                                                               
---------- ----------                                                                               
         1          1                                                                               
         3          3                                                                               
         4          3                                                                               

SQL> UPDATE t1 SET ora_archive_state = 'Deleted' WHERE id = 1;
1 row updated.
SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM t1;
        ID                                                                                          
----------                                                                                          
         4                                                                                          
         5

As expected, the child row is still there:

SQL> SELECT * FROM t2;
        ID      FK_ID                                                                               
---------- ----------                                                                               
         1          1                                                                               
         3          3                                                                               
         4          3

Turning on visibility of all rows, checking and cleaning up.

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
Session altered.

SQL> SELECT * FROM t1;
        ID                                                                                          
----------                                                                                          
         1                                                                                          
         3                                                                                          
         4                                                                                          
         5                                                                                          

SQL> DROP TABLE t2;
Table dropped.

SQL> DROP TABLE t1;
Table dropped.

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
Session altered.
SQL> ALTER SESSION SET "_optimizer_ansi_rearchitecture"=TRUE;
Session altered.

So – as expected the soft-delete of in-database archiving does not respect foreign keys, so you will have to handle the related tables by yourself. But it is still a useful technique, as long as you’re aware of it’s limitations. And – beware of the outer/full outer join bug!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s