All Primary Keys are Unique, but Not All Unique Keys are Primary: Don’t Borrow Primary Keys
I have encountered several times that database designers borrow primary keys (The term borrow refers reusing a primary key defined in some context as your context’s primary key.) from other systems. A major reason for that is,maybe, related with the way today’s enterprise applications are written: They are not monolitic but rather modular (each party implements some part of the whole system and they communicate with each other using popular techniques. Such as web services, servlets, EJBs, etc.).
A module written before other dependents defines a primary key in its context and propagates it in order to identify an entity within its context. The problem starts at this point if the dependent applications adopt the primary key of the caller application and reuse it as its own primary key.
Here is a scenario which I will refer throughout the post:
- We have an infrastructure module I defined to let other parties to create instances of services available in your company.
- We have another hub module H responsible with gathering requests from actual clients and enrich them before sending requests to I.
- Both of those isolated systems have their own databases and they communicate via web services.
- Module H maintains primary key request# for each service definition request given by the actual client in order to refer to a request whenever needed.
- Database designers of H and I sit together and since application H is written before I and since it has already defined a primary key within its context, designer of H recommends to reuse request# within the context of I at this time. In that way they will guarantee that whenever they refer to some request# they will mean the same thing and module I will become a natural extension of module H.
Since we clearly set our toy environment let’s now start to elaborate potential risks in the final decision module H and module I designers came.
Primary Keys are updated
Although it deserves a deep discussion, we can simply say that people do update primary keys although they shouldn’t. Since there are very very few legitimate cases in which you can update primary keys, we can conclude with a high degree of confidence that:
If you need to update a primary key, it is an indication of poor database design.
But the question in here is that as the designer of module I how can you protect yourself from a possible request# update at module H site (In this context protection means no primary key changes at your site). If you share the primary key of module H, you simply can’t.
Bottom line is that don’t borrow your primary keys from other databases/applications because what you borrow may be subject to change.
Limiting the variety of applications implicitly
During the database design, designer mind is usually busy with the details of functionality requirements of the module and makes a silent assumption that applications accessing his/her module will be constant in time.
However this is usually not true. When new modules start to access the module you implement, are you sure that they will be using the same primary key as the first module does ? If not the first work-around attempt by the designer is to do semantic overloading (Semantic Overloading refers to meaning two different things by using the same notation and this usually yields ambiguity) if possible (Due to incompatible types overloading may not be possible). If overloading is not possible they will try to clone majority of the data model and have two models stemmed from the same point but divided into two just because uncommon primary keys.
Bottom line is that don’t borrow your primary keys from other databases/applications because what you borrow may not be valid for some other application.
It may even not be unique at all
The last but not the least assume the following scenario. Module H1, using request# as its primary key, was the only hub module accessing your I module. But another module group decide on implementing a new hub module H2 with different capabilities. Since they share the same capabilities with H1 at the layer they communicate with module I, they simply clone that part from H1. But unfortunately they recreate the sequence generating request#s in H2′s database because its brand-new.
I believe you got the point. They even can not create a new record in your module due to primary key conflict because (invoke#,moduleName) tuple is the unique thing indeed.
Bottom line is that don’t borrow your primary keys from other databases/applications because what you borrow as unique may not be unique in a later point in time.
I’ve tried to to explain the drawbacks of borrowing primary keys from other systems. As a rule of thumb (although there are few exceptions to that)
Never let some other application to generate primary keys for you and use an isolated database sequence to generate your primary key values.
One final thing to tie post content to its title is that you are free to define unique indexes on top of primary keys of other systems (or use them as a part of a composite unique index in your module) if they deliver them to you and you need to ensure their uniqueness in your module. But the point is that making them unique does not make them primary.