Saturday, October 31, 2020
dbametrix
More
    Home Sql Tutorial What are Natural Keys or Substitute Keys?

    What are Natural Keys or Substitute Keys?

    Blog post explains about Natural Keys or Substitute Keys in Oracle tables with advantages.

    Oracle Tables: Natural Keys or Substitute Keys

    On some occasions I have come across databases in which the rule of always applies 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.

    - Advertisement -
    dbametrix

    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 a 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));

    - Advertisement -
    dbametrix

    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, with 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 a data, verbigracia the number of identity document 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 same.

    When you want to make 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 be keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in 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 be enjoy more advance topics from our partner resource.

    Consider reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Recent Comments