I wonder, is it bad or good idea to use auto increment primary key as business entity identifier such as Partner Id
or Account Number
?
Also, what pitfalls I can face if I'll choose that approach?
Each table in a database has a unique identifier that helps you distinguish that table and the rows in it from another table. A primary key always acts as the unique identifier.
There are many times when a natural key is used as a primary key. There is, in general, no reason to hide a natural key, unless we are dealing with privacy issues. Your real question is, I think, about whether internal keys should be exposed to the users. The answer is, "it depends".
Every table can have (but does not have to have) a primary key. The column or columns defined as the primary key ensure uniqueness in the table; no two rows can have the same key. The primary key of one table may also help to identify records in other tables, and be part of the second table's primary key.
No, it is not required for every table to have a primary key. Whether or not a table should have a primary key is based on requirements of your database. Even though this is allowed it is bad practice because it allows for one to add duplicate rows further preventing the unique identification of rows.
I don't think everyone shares the same opinion, but I do think it is bad practice. Passing ID's to the user as the 'key' is bad in my opinion, for a number of reasons:
In our products, we always use a 'natural key', next to the primary key, a key that is understood by humans.
If there is no human understandable natural key available, for example when it is a logging table, you can revert to a artificial key.
There are at least three desirable characteristics you should keep in mind when choosing or designing keys: Simplicity, Stability and Familiarity. In practice people often find it simpler to remember and work with words and letters rather than just numbers and that is why alphanumeric identifiers are generally more common than numeric-only identifiers (examples of alphanumeric identifiers: car licence plates, airline flight numbers, seat reservation numbers, state and country codes, postal codes, email addresses). There are studies and annecdotal evidence to support the idea that alphanumeric keys are more usable than numbers alone. Also, alphanumeric identifiers can often be shorter than numeric ones. On the other hand, sequential numeric-only identifiers are very common for some applications (e.g. invoice numbers, bank account numbers). So I suggest that you should be guided by your users' / business needs when determining these things.
Note that DBMS engine-level sequence generators often come with limitations that make them unsuitable for some applications. For example it may not be easy to update them or to use them in a distributed database architecture. Another common limitation is that only one "auto incrementing" column may be permitted per table, which precludes their use as a business key if you also want a surrogate key for the same table.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With