From time to time I find a situation where a developer believes he or she is better at enforcing referential integrity than the database. They code intricate sections generating unique IDs based on the maximum value of the id column. They then use this ‘unique’ key to also enforce referential integrity in child tables. Unfortunately referential integrity isn’t transactional, which is one reason these attempts fail. Let’s look at an example and see how the situation can be improved by using the built-in features of Oracle.
An example table was created and populated, then a process written to insert new unique values in the table by using the max() of the intended key column:
SQL>
SQL>
Table created.
SQL>
SQL>
SQL>
Table altered.
SQL>
SQL>
SQL>
2 into ins_test
3 (keycol, keyval)
4 values
5 (1, 'First value')
6 into ins_test
7 (keycol, keyval)
8 values
9 (2, 'Second value')
10 into ins_test
11 (keycol, keyval)
12 values
13 (3, 'Third value')
14 into ins_test
15 (keycol, keyval)
16 values
17 (4, 'Fourth value')
18 into ins_test
19 (keycol, keyval)
20 values
21 (5, 'Fifth value')
22 into ins_test
23 (keycol, keyval)
24 values
25 (6, 'Sixth value')
26 select * from dual;
6 rows created.
SQL>
SQL>
SQL>
Commit complete.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
MAXVAL
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
2 values (&next_key + 1, 'Next value in line');
old 2: values (&next_key + 1, 'Next value in line')
new 2: values ( 6 + 1, 'Next value in line')
1 row created.
SQL>
So far, so good, but let’s look at the second session:
SQL> @ins_w_var_second_sess.sql
MAXVAL
old 2: values (&next_key + 1, 'Next value in line')
new 2: values ( 7 + 1, 'Next value in line')
insert into ins_test (keycol, keyval)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.INS_TEST_PK) violated
PL/SQL procedure successfully completed.
Commit complete.
SQL>
The second session was waiting for the row-level lock to be released before processing the insert, which failed. This is the first reason performing such tasks in the application is a bad idea, it relies on transactions to generate key values, which as you can see, tried to generate the same key in a second session as was generated in the first. This is also the reason why referential integrity cannot be enforced in the application code — the current transaction can’t see beyond itself, so any other key inserts to the table won’t be recognized and child inserts can then fail. This also makes for a very poorly performing application.
Let’s try this again with Oracle at the helm:
SQL>
SQL>
SQL>
Table created.
SQL>
SQL>
SQL>
Table altered.
SQL>
SQL>
SQL>
2 start with 1
3 increment by 1
4 nomaxvalue
5 nocycle
6 order;
Sequence created.
SQL>
SQL> create or replace trigger pop_ins_test_pk
2 before insert on ins_test
3 for each row
4 begin
5 select ins_test_seq.nextval
6 into :new.keycol
7 from dual;
8 end;
9 /
Trigger created.
SQL>
SQL>
SQL>
2 into ins_test
3 (keyval)
4 values
5 ('First value')
6 into ins_test
7 (keyval)
8 values
9 ('Second value')
10 into ins_test
11 (keyval)
12 values
13 ('Third value')
14 into ins_test
15 (keyval)
16 values
17 ('Fourth value')
18 into ins_test
19 (keyval)
20 values
21 ('Fifth value')
22 into ins_test
23 (keyval)
24 values
25 ('Sixth value')
26 select * from dual;
6 rows created.
SQL>
SQL>
SQL>
Commit complete.
SQL>
SQL> insert into ins_test (keyval)
2 values ('Next value in line');
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> exec dbms_lock.sleep(60);
PL/SQL procedure successfully completed.
SQL>
Checking the second session we see it isn’t blocked and inserts a unique key value successfully:
SQL>
SQL>
SQL>
2 values ('Next value in line');
1 row created.
SQL>
SQL>
SQL>
Commit complete.
SQL>
Verifying that both inserts were successful and did, indeed, insert unique values:
> -- > -- ,
> -- >
.
>
> -- > --
> -- >
>
-- -- -- -- -- -- -- -- -- -- -- -- -- -- --
.
>
Using the built-in functionality of Oracle provided a solution that does not rely on a transaction completing and truly generates unique data. Moving on to the referential integrity part of the discussion you will need to successfully insert every parent key before you can reference it in the child tables. Using the transactional method this cannot occur and you will regularly see:
SQL> insert into fk_test
2 values (9, 'Next foreign key reference')
3 /
insert into fk_test
*
ERROR at line 1:
ORA-02291: integrity constraint (GRIBNAUT.FK_TEST_FK) violated - parent key
not found
SQL>
This is not an occurrence you want to witness in any environment, especially production. And no matter how hard the developers try to get it right with their own code, it’s going to fail at some point due to the concurrency mechanism Oracle employs or because of transaction locks blocking other sessions. As I said before, referential integrity isn’t transactional, which means any transactional method used to try and enforce it will eventually fail. This is why Oracle provides sequences and foreign key constraints, so that you won’t have to rely on the application trying to generate unique key values from a transactional operation. Maybe, just maybe, developers will stop trying to make that work in their code after reading this article.
Hope springs eternal.
See all articles by David Fitzjarrell
