91–100 of 251 posts
Mainly to cascade delete though, since we were robust enough to insert proper data.
This was spurred on in new projects because in a very very large older project with many many tables we had problems with orphaned records.
(PostgreSQL)
FK maintains your database integrity. If you care about data, just use it !
I use FKs for most things in an RDBMS... but not for all things. For example audit logs get no FKs, when a delete happens the audit logs about the deletion shouldn't be deleted. I always FK a large table (millions or more rows) to a small table (tens to hundreds of rows). But I will pause and ask hard questions about FK a large table to a large table... will this impact migrations? Do I need this FK? Is data integrit…
FK based on the possible size of a table rather than the current size of the table.
FKs are incredibly performance on a near-empty local dev database ;)
I've found that proponents of this usually either don't fully understand the feature in question or made some major mistake in the implementation, which in turn causes problems that manifest themselves when they try to use said feature.
Everyone here seems to be super diligent about FKs. I wonder if that's a sampling bias.
I've worked on a few projects where they were considered an unnecessary hassle, especially when the RDMS had some performance or functionality limitations when using them. Also "on delete cascade" seems scary. Plenty of systems even just set deleted=true instead of actually deleting (at least before GDPR).
Not every CRUD app treats data integrity as a holy grail. A social network for hamsters can lose a comment, no big deal. Some applications threat databases as a bag of key-value pairs, and the inconvenience of migrations ends up with a JSON in an "everything_else" column. Move fast and break relational integrity.
"It's never gone wrong yet" is a good way to get yourself electrocuted when you're changing a light bulb.
In fact, Snowflake really really doesn't care to the extent that I've seen non unique primary keys in tables.
Earlier quoted context omitted.
> Much better than ORMs I recently migrated to EntityFramework Core (from the non-core version) and I’m actually impressed. Most SQL is pretty much what I’d write by hand. Now granted, if there are complex joins, subqueries and stuff, I don’t even try wrangling the ORM to somehow give me that output, but still. I feel more comfortable just using EF than I used to.
My main problem with Entity Framework is the magic underneath. Like simple operation x = Ef.Find(xid) x.Name = "something" y = Ef.Find(xid) what is y.Name ? Even though you didn't save anything to the database yet ? And the second Find didn't actually refresh from the database ? Oh and the random bugs where people improperly include related entities but it somehow ends up working because they are automatically added…
In this case the important part to know is that the DbContext represents the unit of work and "knows" Entities you previously queried on it. That's very useful, but also can hide bugs like you mentioned with the Includes. I do wish that you'd get more obvious errors if you forget an include, this can be really annoying to debug especially if you're new to EF Core. For read queries I mostly use Select instead of Include, which I find easier and more straightforward in most cases.
ORMs are really useful for making very common operations easy and for making stuff composable. They're also very complex and to make the best use of them you do need to understand both SQL and some basics on how your specific ORM generates this SQL.
Earlier quoted context omitted.
> At the volumes my organization works with, it is very difficult to write performant SQL queries that use JOINs and other relationships as a developer - even as a DBA! - and often much easier, for me, to write performant application code. How can this possibly be true? Won't that result in sending unnecessary data over the wire, stressing network and SQL buffer? What are these queries and what are these volumes? I j…
Joins are severely misunderstood and often incorrectly used. I've seen code that had the wrong join and would return 1,000s of rows which then had to be programatically squished down into the data we actually wanted. Some hand crafted SQL usually fixes this Also some ORMs write dreadful SQL where it comes to joins A badly written join (or collection of joins) will take a longer time to run that will, when the system…