Oracle Tables: Natural Keys or Substitute Keys
On some occasions, I have come across databases in which the rule always applies to a surrogate key when designing a new table, even though there is a perfectly applicable natural key. When I asked about the reason for creating such substitute keys, the reason has almost always been to increase the efficiency of the database by eliminating the possibility of having to link two tables using more than one column.
First I will clarify, for those who are not clear, the concept of substitute key or surrogate key. A substitute key is nothing more than an internal key, a unique identifier, which has no business meaning, and which uniquely identifies a record in a database table.
In my opinion, if we have a natural key when defining a table, which obviously must be unique and immutable, we should use it to the detriment of the substitute key. The argument that using substitute keys will improve database performance is wrong, and natural keys make it easier to write our SQL queries or PL / SQL code.
For example, if we have a pair of tables, the table PRODUCTS and the table VERSION_PRODUCT, it would be logical to use the field product_id as the primary key for the first table, a field that could be a substitute key populated by a sequence, and use as a primary key of the second table a combination of product_id and number_version. The tables could be defined as follows:
CREATE TABLE products
(product_id NUMBER PRIMARY KEY,
other data... );
CREATE TABLE version_product
(product_id REFERENCES products,
version_number NUMBER,
other data...
CONSTRAINT ver_pro_pk PRIMARY KEY
(product_id, number_version));
In our example, using a substitute key would add extra work to any INSERT operation that should generate the substitute key, in addition to doing the same for the natural key. My opinion is that if the natural key is immutable and reasonable, then we should use it. In this specific case, by reasonable I mean that the key does not need to use fifteen fields to be generated, but only requires two to five columns.
Unfortunately, it is not always easy to guarantee the immutability of data, verbigracia the number of identity documents is a natural key for the identification of people, if embargoes errors in the fingering or in the worst case errors of the entity that issues the document make it necessary to update this data. If the people table has many foreign relationships, updating the identity document becomes very cumbersome.
The main advantages of always using a sequential primary key that does not mean anything are:
- Unification of the primary key criteria for all the tables of the model.
- Simplification of all queries since JOINs between tables are made very simple.
- Possibility to establish a criterion for the names of the fields that are PK so that you don’t even have to see them to know what the PK should be called in each table.
- Total independence of business logic, regardless of whether tomorrow it is required to store a duplicate code or modify the true key of the table.
- Simplicity and reduction of the number of parameters for procedures since with 1 parameter per table I can already obtain all the associated data.
There are many more advantages, but I think you are with the main features of the same.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
