3 ms·
That behavior is similar to a number of analytic databases. It's expensive to maintain constraints in large distributed datasets. Referential integrity checks a
by hodgesrm 6y ago
That behavior is similar to a number of analytic databases. It's expensive to maintain constraints in large distributed datasets. Referential integrity checks are also not meaningful in denormalized fact tables. Redshift [1] and ClickHouse [2] work this way as well. If things like duplicates are an issue, you can remove them by choosing query sort orders carefully, for example.
[1] https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_co...
[2] https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/ https://clickhouse.tech/docs/en/engines/table-engines/merget...
- cnmjbm 6y agoYeah. I knew that, but removing them afterwards seems "a big chunk of work" and never being a satisfactory solution, eg: [1] https://community.snowflake.com/s/question/0D50Z00007Ft37mSAB/how-can-i-delete-duplicate-records-from-snowflake-tablegiven-that-a-column-name-is-unique-but-does-not-have-the-constraint-as-cte-is-not-fully-supported https://community.snowflake.com/s/question/0D50Z00007Ft37mSA... [2] https://stackoverflow.com/questions/35372889/a-simpler-more-efficient-way-to-remove-duplicate-rows-from-an-sql-server-table https://stackoverflow.com/questions/35372889/a-simpler-more-...