Is it a bad idea to use a database's primary key as business object identifier?

Question

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?

Answer 1

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:

• ID's aren't natural to users. They are not talking about project '1474623', they are talking about project 'ABC'. They aren't talking about person '363528', they are talking about 'Patrick Hofman';
• ID's are fragile. You can't really rely on them not changing. What if you choose to move to another database platform, or a new version of the current platform, and you want to move all data using 'insert' statements, it is possible to loose the ID fields.

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.