Constraints in SQL

The usual SQL constraints are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT. In a traditional database like Postgres or MySQL the engine enforces them and rejects bad writes. In many cloud warehouses most of them are only declared, not enforced.

What each constraint says

• NOT NULL: column must have a value.
• UNIQUE: no two rows share these values.
• PRIMARY KEY: unique plus not null, identifies the row.
• FOREIGN KEY: value must exist in another table.
• CHECK: a condition such as amount >= 0.
• DEFAULT: value used when none is supplied (not a rule, just a convenience).

What cloud warehouses actually enforce

• Snowflake (standard tables): enforces only NOT NULL. Primary key, unique and foreign key can be declared, but they are informational.
• BigQuery: primary and foreign keys can be declared with NOT ENFORCED. NOT NULL is enforced.
• Redshift: NOT NULL is enforced. Primary key, unique and foreign key are information for the planner and are not enforced.
• Databricks (Delta): NOT NULL and CHECK constraints are enforced on write. Primary and foreign keys are informational.

Why declare them if they are not enforced

The optimizer can use them as hints. A declared primary key tells the planner a join will not fan out, which allows some joins to be skipped or simplified. BI tools also read them to draw relationships. The catch: if the declared key is a lie, the optimizer may return wrong results.

So who checks the data

You do. A warehouse is loaded in bulk from many sources, and enforcing a foreign key on billions of rows would slow every load. The trade is speed for responsibility. In practice that means tests that run after the load, such as dbt unique, not_null and relationships tests, or checks in the pipeline. If an interviewer asks "how do you stop duplicate primary keys in Snowflake", the answer is "constrain nothing at the database, test in the pipeline, and make loads idempotent with MERGE".