0

During early development I tend to create tables using running numbers as Primary Key, although there is formally a real ID column for each table. I tend to forget mapping the relationship between table and if I'm not wrong, this can be added later even after the table is no longer empty. This is also true for setting Index column.

I know this is probably not the best way to setup a new database for application but is there any permanent pitfall where the damage done might be irreversible which makes this habit potentially "deadly"?

Thanks.

1 Answer 1

2

is there any permanent pitfall where the damage done might be irreversible which makes this habit potentially "deadly"?

Short answer: No.

Very little you can do to your database is "deadly".

That said, changing Primary and Foreign keys is a pain in most DBMSs and should be avoided, if possible. Also, you'll need indexes to support all of your new key fields (and the foreign key fields that reference them) and building all of those indexes is likely to take some time and may cause contention problems with a running application, so you'll probably want to do this in a Maintenance Window when you can keep everyone/everything else safely out of the way.

I tend to create tables using running numbers as Primary Key, although there is formally a real ID column for each table.

I'd say that was a Bad Idea.

If you have a Natural, Primary key already, then I'd recommend using it.
Why duplicate the effort to reinvent your own, surrogate key? Plus, you have to create extra, unique Indexes on the Natural key anyway, which you get "for free" with the Primary Key.

3
  • Upvote from me except I can imagine a scenario where you might not want to go with a natural key. Imagine that key is, I don't know, say 150 characters long. Why not use an auto increment integer instead? Makes ad-hoc SQL scripts a lot easier - and if people need to reference records, saying "hey, record 123,456 has issues" is going to be easier too. Oct 15, 2020 at 5:21
  • I would argue that these surrogate, auto-incremented "key" values should NEVER be seen outside of the database. Users will interact with our software using Natural Keys that represent Business-meaningful values. The fact that you've chosen to tie the records together internally using meaningless numbers should never even enter their consciousness. OK, you and I would be interested in those numbers and any error logs should include them, but Users shouldn't even know they exist.
    – Phill W.
    Oct 15, 2020 at 13:38
  • Fair enough - my examples were geared more toward support work. Oct 16, 2020 at 5:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.