@MichaelGreen I love this part: "After creating a table with primary key or unique constraint in dedicated SQL pool, users need to make sure all values in those columns are unique. A violation of that may cause the query to return inaccurate result."
WHICH IS WHY UNIQUE AND FK CONSTRAINTS SHOULD BE ENFORCED ALWAYS AND BY THE DATABASE
screams in Codd
I honestly don't understand where these DW technologies think they're saving time/energy by not enforcing PKs/FKs, because some sucker (usually me) will have to either spend a decent amount of time coding to ensure PK/FK integrity.
OR worse, someone (usually me) will notice a cardinality error then instantly distrust all results returned by the database until I'm reasonably certain the overall impact isn't material to what I'm doing.
@bbaird "After creating a table with primary key or unique constraint in dedicated SQL pool, users need to make sure all values in those columns are unique. A violation of that may cause the query to return inaccurate result." - crazy stuff! A bit like the classic MySQL - CHECK constraints are parsed but not enforced... - a complete breach of the principle of least surprise and that you should fail early and often rather than accepting misleading input -
principles espoused by Eric Raymond in his TAOUP book. Whatever about his other writings... that one is quite good! I won't touch MyRocks or TiDB - from TiDB -
this gem: However, TiDB does not perform constraint checking on foreign keys in DML statements. For example, even if there is no record with id=123 in the users table, the following transactions can be submitted successfully. So why allow them to be declared in the first place?
@ypercubeᵀᴹ Exactly. MySQL is the truculent teenager wearing earbuds. Synapse is the infant with fingers in her ears singing "I CAN'T HEAR YOU, I CAN'T HEAR YOU".
4
I understand how the optimizer can make better choices given this information, and so why MS would implement it. I find it quite disappointing that it can lead to incorrect results, however.
@MichaelGreen It's the same in Snowflake, Netezza, and a million other warehousing-oriented "databases" - ostensibly for the sake of insert performance, neglecting the fact to properly enforce data integrity and guard against ETL bugs, one has to read and join the entirety of the database to verify the constraints via code which then prevents any sort of optimizations from the DB engine itself.
Like, you can modify how transactions are committed or violations are handled without throwing out the entire concept of relational integrity