Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted October 10, 2013

Are You Better at Enforcing Referential Integrity than Oracle?

By David Fitzjarrell

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM