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!
- nice theory... 