What are the main disadvantages of adding relationship between tables at later date?

Question

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.

Answer 1

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.