Morning all.

There's one twenty-fourth of a Steward badge up for grabs, if anyone's keen!

@MaxVernon At least Azure Synapse has the courtesy to require the NOT ENFORCED clause.

@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 Amen brother.

@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?

@MichaelGreen how is that any different from mysql's not enforcing check constraints?

oh, you have to state NOT ENFORCED every time you declare a "constraint"

... which isn't a constraint, just a hint to the optimizer.

> Hint: assume there is a constraint when making plans. No guarantee that data align with the "constraint"

@bbaird @ypercubeᵀᴹ - in fairness, they are now enforced - it only took them ~ 27 years from the SQL 1992 standard...

@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".

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