It seems the central argument here is NoSQL doesn't force you into good design habits so it's overrated. I'll concede this is partially true from my perspective because much of my work involves trying to sanitize, transform, and otherwise refactor poorly structured or designed NoSQL datasets.
But I've also seen my fair share of SQL databases which are poorly designed, don't use features which are meant to benefit developers (I haven't seen a Foreign Key smartly implemented in a LONG time). It's not really fair to say NoSQL has encouraged poor design practices; from my experience it seems like data model implementation is given little effort in general.
NoSQL takes the 'training wheels' off data model implementation where SQL is like keeping them on, but even with them you can still fall off the bike if you aren't careful though it's much harder.
This has also been my experience as a web developer mostly. I rarely see any applications that make use of foreign keys constraints supplied by the database server. Usually I see relations being handled by code on the application side.
Even when I build apps that are using SQL, I always implement this stuff in code instead of FKs even though I know how they work and when designing a database schema in an app like (for example) mysql workbench, I have the option to add them easily. But I always use the "ignore foreign keys" option and then implement the constraints in code. I just find it a bit more sane to have all of the logic inside the app.
I know I'm probably "doing it wrong" but would love to hear some other opinions about this from the hn crowd that does web development. I'm guessing that more "enterprisey" apps like CRM's and ERP's will probably use more of the native database stuff.
For example: transaction X deletes a parent row and its child rows while a concurrent transaction Y inserts a new child node. Without a FK, this can leave an orphaned child row.
For similar reasons, SELECT before INSERT is generally not a safe replacement for a UNIQUE constraint.
You don't even need that high a user volume to see these errors in practice.
It's a shame that ORMs don't tend to encourage the use of database-level constraints, but even if I just want to build something quickly in e.g. Rails, I still add the constraints as an extra check because I'd rather get the occasional 500 than an inconsistent database.
The moment you or your customers need business intelligence and complex queries, you quickly realize how important FKs and indexes actually are. I firmly believe that you should always have FKs and indexes, referential integrity at the db level is a great thing to have and is cheap and easy to implement.
Of course you're probably thinking that it's no big deal when you're writing the app and the database at the same time. I will sort of concede that point (although I think constraints, FKs, etc... should all be used unless later performance testing shows they must be removed in certain cases). But, data nearly always outlives the original application. When it's time to add to/update/rewrite the original application code is when you'll be ecstatic to have the business rules enforced in the db.
I frequently get the self-awareness that there is a lot of sql functionality that I'm probably missing, misusing or completely ignorant of.
Particularly, the fk example just stood out because even though it's usually an integral part of database design i don't think enough importance is given to them "overall".
As @mpartel stated, as most orm's have similar functionality built in, their usage is just basically ignored. Also good example about the transactions stuff, I rarely see the use of foreign keys encouraged (and on your example pretty much almost required) in online tutorials and docs when discussing transactions.
More of a rant really, thanks again for the input guys! +1
But either way, I can't imagine any reasons why you wouldn't want constraints to ensure you database is in a consistent state. There can always be bugs in you application code, manual db updates, etc.