Are You Better at Enforcing Referential Integrity than Oracle?

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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles