4 ms·
It’s at its core a case by case decision, I think FK are also a net negative in data ingestion scenari where the data set is big enough. Trying to make sure ev
by hrktb 7y ago
It’s at its core a case by case decision, I think FK are also a net negative in data ingestion scenari where the data set is big enough.
Trying to make sure everything is where it needs to be at any given time, everything is inserted in the right order and the data is always consistent brings exponential amount of conplexity when it could all be checked at the end and pruned for invalid data. And usually DB integrity will not be enough, you’ll want business level validation that all is OK, so there will be app level checks anyway.
- joevandyk 7y agoPostgreSQL supports deferring constraints. https://begriffs.com/posts/2017-08-27-deferrable-sql-constraints.html https://begriffs.com/posts/2017-08-27-deferrable-sql-constra...
- hrktb 7y agoThis looks nice, it’s still limit to a single commit though. That’s where it’s a PITA for anything that won’t (or we don’t want to) fit a single commit. In particular splitting commits allows to ingest data in parralel (for instance if we import stores and store owners, both could be ingested separately without caring at first if each references a valid entity)
- JoshuaDavid 7y agoAt least with mysql, and probably with postgres as well, you can temporarily turn off foreign key checks for a set of statements. So you can still get the benefits of foreign key constraints by default but when they do more harm than good you can turn them off. With the added benefit that turning off FK constraints screams "I am doing something unusual and dangerous - this requires extra caution."
- sturgill 7y agoApp level data integrity is usually critical. Ingestion into a reporting database is an entirely different construct (where I agree FK constraints are burdensome).