The power of RDB$DB_Key

I was recently make aware of an undocumented feature that's been in InterBase for more than 2 decades. It's the fact that every row in every table has a unique identifier. I think it is best thought of as a Raw Record position. This field is called RDB$DB_KEY. RDB$DB_KEY is an array of 16 bytes. Array's are handled differently with different drivers so you have to cautious about how you use this data type. However, RDB$DB_KEY provides the fastest access to uniquely identify a row in a table, so could have some unique applications. In fact, it's used by some our drivers today.

It should be noted the using RDB$KEY is singularly an InterBase concept. So by using it you are uniquely tying yourself to InterBase. I think that in most cases it is best used by drivers, which are tied to InterBase anyway. Also, because it is not documented, you are not promised a lifetime of usage. However, because there are drivers that use it today, it is not likely to go away any time soon.

Because RDB$DB_Key is a Raw Record position, it's value could theoretically could change between transactions and will almost certainly change after a backup and restore. So, you should only depend on it's values in the context of the current transaction.

Let's look at a couple simple examples. First, let's use the employee sample database included with InterBase. Say, I execute the following in isql:

select rdb$db_key, country.* from country;

What I get is:

RDB$DB_KEY       COUNTRY         CURRENCY
================ =============== ==========

0000008000000001 USA             Dollar
0000008000000002 England         Pound
0000008000000003 Canada          CdnDlr
0000008000000004 Switzerland     SFranc
0000008000000005 Japan           Yen
0000008000000006 Italy           Euro
0000008000000007 France          Euro
0000008000000008 Germany         Euro
0000008000000009 Australia       ADollar
000000800000000A Hong Kong       HKDollar
000000800000000B Netherlands     Euro
000000800000000C Belgium         Euro
000000800000000D Austria         Euro
000000800000000E Fiji            FDollar

Note that in the select statement you have include the table name alias when referring to the table. Not doing so will result in an error.

Now, you might think that the following would select the first row:

select * from country where rdb$db_key='0000008000000001'

However, it will not as RDB$DB_KEY is not a CHAR or a VARCHAR field. What isql is displaying is how an array of bytes is displayed, which comes out in hex.

Now consider that the country table has a primary key of country. What most drivers do is query for the primary key, then issue a where clause based on it for any updates or deletes. So, if the USA Dollar were to become the USA Whamo, most drivers would do this:

update country set currency='Whamo' where country='USA';

However, what would be more efficient is if the RDB$DB_KEY value was used instead. In this case, the driver would store the RDB$DB_Key value into a variable when the row was fetched, then use a parameterized query for the update. For example, something like:

update country set currency='Whamo' where rsb$db_key=:key


Now, let's consider a table with no key. Let's say you did this:

create table somenames (thename varchar(10));
insert into somenames values ('Quinn');
insert into somenames values ('Quinn');
insert into somenames values ('Quinn');

Now, I don't know why you would want to do this, but that's a different matter. However, with straight SQL, there is no way to update or delete just 1 or 2 of these rows. However, if you have access RDB$KEY you can. If you try this example with IBConsole you should note that individual rows can be deleted, thereby showing that IBConsole uses RBD$DB_KEY when it does a delete.

Next, let's consider a query with multiple tables in it. As you might expect, you can access the RDB$DB_KEY value from each table. For example:

select a.rdb$db_key, b.rdb$db_key, last_name, old_salary, new_salary
from employee a inner join salary_history b on a.emp_no=b.emp_no
where change_date='12/20/2012' rows 1;

returns

RDB$DB_KEY                      0000008300000013
RDB$DB_KEY                      0000008700000027
LAST_NAME                       Steadman
OLD_SALARY                      120000.00
NEW_SALARY                      116100.0000000000

Next, let's consider a view.  Views get a RDB$DB_KEY value that is a combination of all tables in the view. So:

create view test1 as
select last_name, old_salary, new_salary
from employee a inner join salary_history b on a.emp_no=b.emp_no
where change_date='12/20/2012';

select rdb$db_key, test1.* from test1 rows 1;

results in:

RDB$DB_KEY                      00000087000000270000008300000013
LAST_NAME                       Steadman
OLD_SALARY                      120000.00
NEW_SALARY                      116100.0000000000