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> -- Create test table SQL> -- SQL> create table ins_test(keycol number, keyval varchar2(20)); Table created. SQL> SQL> -- SQL> -- Add primary key constraint SQL> -- SQL> alter table ins_test add constraint ins_test_pk primary key (keycol); Table altered. SQL> SQL> -- SQL> -- Insert data SQL> -- SQL> insert all 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 changes SQL> -- SQL> commit; Commit complete. SQL> SQL> -- SQL> -- Start questionable process to SQL> -- insert more data based upon SQL> -- current max(keycol) value SQL> -- SQL> SQL> -- SQL> -- Set a variable to reuse the returned SQL> -- max value SQL> -- SQL> -- SQL> -- Rather dumb idea as the max() from SQL> -- any query will miss any new values SQL> -- inserted by other sessions and not SQL> -- yet committed SQL> -- SQL> column maxval new_value next_key SQL> SQL> -- SQL> -- Return current max(keycol) SQL> -- SQL> -- Not reliable in the least SQL> -- unless there is only one user SQL> -- ever on the system, and it's SQL> -- you SQL> -- SQL> select max(keycol) maxval from ins_test; MAXVAL -- -- -- -- -- 6 SQL> SQL> -- SQL> -- Use the saved value for the next insert SQL> -- SQL> -- Not a good idea SQL> -- SQL> -- Using a sequence and a trigger is far SQL> -- more reliable and recommended SQL> -- SQL> insert into ins_test (keycol, keyval) 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 -- -- -- -- -- 7 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> -- Create test table SQL> -- SQL> create table ins_test(keycol number, keyval varchar2(20)); Table created. SQL> SQL> -- SQL> -- Add primary key constraint SQL> -- SQL> alter table ins_test add constraint ins_test_pk primary key (keycol); Table altered. SQL> SQL> -- SQL> -- Add a sequence, and a trigger, to populate the key column SQL> -- SQL> create sequence ins_test_seq 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> -- Insert data SQL> -- SQL> insert all 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 changes SQL> -- SQL> commit; Commit complete. SQL> SQL> insert into ins_test (keyval) 2 values ('Next value in line'); 1 row created. SQL> SQL> -- SQL> -- Wait for a minute so a second insert of this SQL> -- same type can occur from a different session 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> -- Using a sequence and a trigger is far SQL> -- more reliable and recommended SQL> -- SQL> insert into ins_test (keyval) 2 values ('Next value in line'); 1 row created. SQL> SQL> -- SQL> -- Commit the changes, or try to SQL> -- SQL> commit; Commit complete. SQL>
Verifying that both inserts were successful and did, indeed, insert unique values:
SQL> -- SQL> -- Commit the changes, or try to SQL> -- SQL> commit; Commit complete. SQL> SQL> -- SQL> -- Select the data and verify keys are unique SQL> -- SQL> SQL> select * From ins_test; KEYCOL KEYVAL -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 8 Next value in line 1 First value 2 Second value 3 Third value 4 Fourth value 5 Fifth value 6 Sixth value 7 Next value in line 8 rows selected. SQL>
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.