如何在Snowflake中验证一组列是否符合主键要求，并在不符合时抛出错误？

Great question! Since Snowflake doesn’t enforce primary key or unique constraints at the database level, adding manual validation checks during data updates is critical. Here are a couple of concise, readable options using Snowflake’s built-in functions to throw an error when your columns fail primary key requirements (no duplicates, no NULLs):

Single Column Primary Key Check #

This one-liner compares total rows, distinct rows, and non-NULL rows to validate the key:

ASSERT (COUNT(*) = COUNT(DISTINCT PK) AND COUNT(PK) = COUNT(*)) 
    ERROR 'PK column contains duplicates or NULL values'
FROM PRIMARY_KEY_TEST;

• COUNT(*) = COUNT(DISTINCT PK) ensures no duplicate values exist in the column
• COUNT(PK) = COUNT(*) guarantees there are no NULL values in the PK column
• If either condition fails, your custom error message is thrown immediately.

Composite Primary Key Check #

If you’re validating multiple columns as a composite key, adjust the logic to check all columns and their combined uniqueness:

ASSERT (COUNT(*) = COUNT(DISTINCT (PK, TEXT)) AND COUNT(PK) = COUNT(*) AND COUNT(TEXT) = COUNT(*))
    ERROR 'Composite key (PK + TEXT) has duplicates or NULL values'
FROM PRIMARY_KEY_TEST;

• COUNT(DISTINCT (PK, TEXT)) verifies unique combinations of the two columns
• The additional COUNT checks ensure neither column contains NULL values.

Alternative: Explicitly Target Invalid Rows #

If you prefer to directly identify invalid rows before throwing an error, this version checks for NULLs or duplicates and errors if any exist:

ASSERT (SELECT COUNT(*) FROM PRIMARY_KEY_TEST WHERE PK IS NULL OR PK IN (
    SELECT PK FROM PRIMARY_KEY_TEST GROUP BY PK HAVING COUNT(*) > 1
)) = 0 ERROR 'PK column contains invalid values (NULLs or duplicates)'

All these approaches are lightweight, readable, and leverage Snowflake’s native functions to keep your validation logic tight.

内容的提问来源于stack exchange，提问作者Sam Fed