Primary Key Education

Primary Key Education

Bizarre Behaviour

I had an interesting instant message last night while I was preparing a demonstration for today and watching Mr Bean with my youngest children.  One of my friends and colleague, Sanjeev, couldn't understand why he was submitting a query through the database adapter and getting 3 entries back (as expected) that were all identical (not expected).
The table being queried looked a little like this
SystemID
Date
Message
22222
24-MAY-2007
Disk at 90% Utilisation
22222
29-MAY-2007
Disk at 98% Utilisation
22222
2-JUNE-2007
Error Writing to Disk
33333
1-JUNE-2007
Out of Squirrel Memory
Sanjeev had used the DB adapter to query the table.  Effectively he was running a query like:
SELECT SystemID,Date,Message FROM EventTable WHERE SystemID = %1
Running this in BPEL with an input of 22222 gave him a document like this:
<EVENTS>
<EVENT>
<SYSTEMID>22222</SYSTEMID>
<DATE>24-MAY-2007</DATE>
<MESSAGE>Disk at 90% Utilisation</MESSAGE>
</EVENT>
<EVENT>

<SYSTEMID>22222</SYSTEMID>

<DATE>24-MAY-2007</DATE>

<MESSAGE>Disk at 90% Utilisation</MESSAGE>


</EVENT>


<EVENT>

<SYSTEMID>22222</SYSTEMID>

<DATE>24-MAY-2007</DATE>

<MESSAGE>Disk at 90% Utilisation</MESSAGE>


</EVENT>


</EVENTS>
Notice all three records are the same.
Running this query from SQL Plus gave him the following
22222, 24-MAY-2007, Disk at 90% Utilisation
22222, 29-MAY-2007, Disk at 98% Utilisation
22222, 2-JUNE-2007, Error Writing to Disk
In the database it seems that all three records are different.
As you can imagine this was pretty baffling.

A Solution

I told him to rerun the wizard, it is re-entrant so previous settings are saved for you, and select all the columns as the primary key in the mapping portion of the wizard.  He did this and voila, all behaved as expected.

The Reason Why

So what was going on?
The table had no primary key constraint on it (sounds of outrage from Mr Codd) and hence effectively had a primary key consisting of all the columns in the table.  When Sanjeev created the database mapping the wizard asked him for a primary key and so he chose the ItemID column.
Under the covers the database adapter uses TopLink for persistence.  TopLink requires a primary key to be used so that it can identify tuples uniquely and manage the object mapping of tuples avoiding duplicates.  To be more efficient with large data sets TopLink only load the primary key fields on a query and then loads the objects as needed based on primary key.
Of course in this case the 'primary key' was the same for all tuples returned and so toplink thought aha! I can save myself some time here and only load the object once and repeat it three times because it is the same object.
Hence changing the mapping to tell the database adapter and hence TopLink that the primary key was really all the columns in the database allowed TopLink to realise that the three entries returned by the query were really three distinct entries and load them as such.

Many Morals

This is a story of many morals.
  1. All tables should really have an explicit primary key constraint, it makes the intent clearer.
  2. Don't accept default settings without giving some thought to what you are doing.
  3. It often helps to understand what is going on under the covers.
  4. Getting an Instant Message from a friend during Mr Bean can lead to a whole set of intersting avenues.

Comments:

The unspoken moral is that it is a functonality reduction on Interconnect so by using Toplink we now have to determine Primary Keys . You may not consider it a reduction but I do. for example a simple data logger transferring events now needs to understand all about toplink [ and therefore Java and all the associated unnecessary palaver. ] . A case can therefore be made for using a simple interface and allowing toplink when a complex interface is needed.

Posted by Chris S on August 09, 2007 at 04:24 AM MDT #

Post a Comment:
Comments are closed for this entry.
About

Musings on Fusion Middleware and SOA Picture of Antony Antony works with customers across the US and Canada in implementing SOA and other Fusion Middleware solutions. Antony is the co-author of the SOA Suite 11g Developers Cookbook, the SOA Suite 11g Developers Guide and the SOA Suite Developers Guide.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today