Have a primary key, for Christ's sake!

This posting is a venting of my frustration.

Never ever agree to have a table that doesn't have a primary key. There may be forces of nature that might attempt to convince you to have one, like:

  • Your peers might say you don't need to perform an UPDATE in that table, now or ever
  • You just need that table only for audit or reporting purposes
  • We store data in that table just to go back and refer later which may never happen, so don't bother
  • You may be under a gun where the big boss says "you don't need a primary key, because I say so!"
For Christ's sake, don't yield to any of these things. I was in a limbo recently where I had to make use of one of the legacy tables that didn't have a primary key. This table was once thought as insignificant, but now has suddenly become an important table. The issue is that, for me to perform the operations that I want to perform, I need a primary key.

The issue is that this monster table has more than 40 million rows. Eventually I needed to create a primary key (ID) and back fill all the rows using a sequence. Had to add a trigger to make sure the ID column is populated every time when the application INSERTs a row into that table in future. It was unnecessary hassle.

So, my friend, here are my two cents:
  • Always make sure your table has a primary key. It is very cheap to have one right from the beginning, and populate it using AUTO_INCREMENT (MySQL) or triggers (Oracle), rather than attempting to fix later.
  • If you will perform UPDATEs in a table, make sure you have VERSION column. This is important since it will help you in performing optimistic locking. And if you have more than one instance of application running, it definitely helps. I have seen people using LAST_UPDATE_TIME for optimistic locking. Though that works, it is error prone.
Versioning is an important aspect, which gets easily overlooked. Today you may have only one Tomcat (or any application server) running. Say your product becomes a hit and you need to double the number of application servers. Versioning helps you to perform optimistic locking, and ensure you don't screw up the integrity of data.

Comments

Post a Comment

Popular posts from this blog

The mysterious ORA-03111 error

Recently one of the applications that I developed started throwing exceptions, that had the following message: SQL state [72000]; error code [1013]; ORA-03111: break received on communication channel When I googled around, I couldn't come across anything useful. Sadly enough most of the sites just showed the documentation for that error, without any explanation from anyone experiencing that issues. So here you go, with the best possible explanation that I could come up with. My application sets two things on the connection that is throwing this exception: It sets the fetchSize to be 2500 rows It sets the query timeout to be 10 seconds The database server and the application are separated over a long latency network (actually there is a NetEm box that emulates the long latency between these two boxes) which has a latency characteristic of 50+/-5 milliseconds. This is the whole setup. It is important to understand how the timeout is handled by the Oracle client (in my case JDBC clien...
Read more

Note on allocationSize parameter of @SequenceGenerator while using JPA

I ran into what I thought as an issue while I was using the sequence ID generation strategy in JPA. The JPA provider I am using is Hibernate. I think sharing my experience will save someone some time. To use a sequence (For e.g. Oracle sequence) to generate values and assign them to the ID field of your persistence object, you will following something like this: @Id @Column(name = "ITEM_ID") @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="ItemIdSeqGenerator") @SequenceGenerator(name="ItemIdSeqGenerator", sequenceName="ITEM_ID_SEQ", allocationSize=1) private long itemId; This means the following things: The @Id annotation says that the field itemId is a primary key. The @Column annotation says that the corresponding column in the database is ITEM_ID. The @GeneratedValue says that the value that needs to be populated in the itemId should be generated, while that object is persisted. The strategy to generate the value is to...
Read more

xVM's vboxmanage.exe command

You can view and edit the configuration of your guest OSs using the vboxmanage command. To view the list of guest OSs you have, give the following command: C:\Program Files\Sun\xVM VirtualBox>VBoxManage.exe list vms VirtualBox Command Line Management Interface Version 2.2.2 (C) 2005-2009 Sun Microsystems, Inc. All rights reserved. "MySolaris" {dc6dc85f-5583-4a1b-bf3e-969941a2cd91} "Ubuntu" {eb973bbf-d86e-4579-85eb-6ea2cd12bf95} "Debian" {3e784597-89d8-4f17-90cb-63e866c651a3} "openSUSE" {6862884e-60e1-4c65-8aab-b57ec38a3922} "Debian-Lenny" {4b561432-abac-4a27-a501-b42af956b96b} To view the specific guest, you can use the UUID of the guest. For e.g. C:\Program Files\Sun\xVM VirtualBox>VBoxManage.exe showvminfo eb973bbf-d86e-4579-85eb-6ea2cd12bf95 VirtualBox Command Line Management Interface Version 2.2.2 (C) 2005-2009 Sun Microsystems, Inc. All rights reserved. Name: Ubuntu Guest OS: Ubuntu UUID: eb973bb...
Read more