Hacker News new | past | comments | ask | show | jobs | submit login

I'm working with a 3rd party database right that used more than a few natural keys and it's awful. The primary key for "users" is username so we can't rename our users to improve our company's username security policy. And that's just one of the problems. We'll be offline for a full 24 hours in April to handle changing the value (for necessary business reasons) of the natural primary key in the client table.

I'm all for constraints, especially uniqueness constraints, where appropriate. You can use unique indexes; you don't have to use natural keys as primary keys. Data is bound to change, so make that change possible.




A badly built database isn't the reason to toss out good practices.

It's actually quite obnoxious to see how bad many databases are, and this often causes a blow-back of tossing the baby out with the bathwater. It's a trap.

I'm not really sure what all the problems are, but if changing username to something else is causing a 24 shutdown, there are many deeper issues than a PK on username.


> A badly built database isn't the reason to toss out good practices.

It's a good reason to toss out bad practices like, for example, using natural keys as primary keys. Which is, almost universally, considered a bad practice. It is, in fact, the bad practice that causes the most pain in this bad database.

> if changing username to something else is causing a 24 shutdown, there are many deeper issues than a PK on username.

This is true; the software is so old it pre-dates most modern RDBMS features and that is a factor in the database design. And it contains a few decades worth of data. And the process to go through the all records and re-do every foreign and primary key value is expensive (through the app server) but is a well-tested path. None of this would be necessary, however, if they had used surrogate keys for these tables. They did use surrogate keys for other tables so it wasn't a technical limitation.


I'm curious what database this is. Even mysql supports ON UPDATE CASCADE back to 5.5 (or earlier?).




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: