Never Use a Natural Primary Key

Well, you probably know already not to use natural primary keys in your database design. Here’s another story how ignoring this rule can and will bite you.

Breaking the Rule(s)

New to Android and SQLite I stumbled upon SQLite’s “UPSERT” ability: if a record exists, UPDATE it, otherwise INSERT a new one. The caveat is that the “UPSERT” consists of a DELETE followed by an INSERT which means the record will get a new ID if it comes from an PRIMARY KEY field. And of course it will lose any existing data that is not repeated in the “UPSERT”.

Both were not a problem for me as I got my data from a 3rd party web service that would give values for all columns anyway and only a natural key. So the SQL to create the table looked like this:

CREATE TABLE my_table (
  my_natural_key TEXT PRIMARY KEY ON CONFLICT REPLACE,
  my_value1 TEXT
);

The “UPSERT” looked like this – one could as well use insertWithOnConflict() to specify the conflict resolution strategy:

public void createOrUpdateMyData(String naturalKey, String value1) {
  // _dbHelper extends SQLiteOpenHelper and is created in the class' constructor
  SQLiteDatabase db = _dbHelper.getWritableDatabase();
  ContentValues contentValues = new ContentValues();
  contentValues.put("my_natural_key", naturalKey);
  contentValues.put("my_value1", value1);
  db.insert("my_table", null, contentValues);
} // createOrUpdateMyData()

Cool! Spares one from writing SQL like this – and no, SQLite doesn’t support stored procedures or MERGE :-):

IF EXISTS (SELECT 1 FROM my_table WHERE my_table.my_natural_key = naturalKey)
  UPDATE my_table SET my_value1 = value1 WHERE my_table.my_natural_key = naturalKey
ELSE
  INSERT INTO my_table (my_natural_key, my_value1) VALUES (naturalKey, value1)

And then it bites you …

Unfortunately, the program was stopped dead in it’s tracks as soon as it tried to create a SimpleCursorAdapter with a Cursor from SQL like SELECT * FROM my_table:

java.lang.IllegalArgumentException: column '_id' does not exist

Digging into CursorAdapter.java showed that init() looks for the hard coded column name “_id”.

And then it bites you again!

At the same time I found out that the easiest way to get the selections from a multiselect ListView and update the database accordingly is ListView.getCheckedItemIds() – and guess what that returns …

Lessons learned

“Never use a natural primary key”, of course. It would be possible to use something like this and still profit from SQLite’s conflict resolution strategies for my_natural_key:

CREATE TABLE my_table (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  my_natural_key TEXT UNIQUE,
  my_value1 TEXT
);

But you would still have issues from “UPSERT” changing _id values that might be referenced in other tables. And it’s better to use a pattern that works across all databases anyway.

By the way: intead of hard coding “_id” you can use BaseColumns._ID.

External References

Advertisements
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s