How to add PRIMARY KEY in Snowflake

Why add a PRIMARY KEY in Snowflake?

PRIMARY KEY constraints guarantee that each row has a unique, non-null identifier. They improve data quality, simplify joins, and help other teams understand table intent, even though Snowflake does not physically enforce uniqueness.

How do I declare a PRIMARY KEY at table creation?

Add a PRIMARY KEY clause directly after the column list. This is ideal when defining a new dimension or fact table.

CREATE TABLE Customers (
id NUMBER NOT NULL,
name STRING,
email STRING,
created_at TIMESTAMP,
PRIMARY KEY (id)
);

How do I add a PRIMARY KEY to an existing table?

Use ALTER TABLE ADD PRIMARY KEY. Confirm the data is unique before running the command to avoid silent data issues.

ALTER TABLE Orders
ADD CONSTRAINT pk_orders PRIMARY KEY (id);

Can I create a composite PRIMARY KEY?

Yes—list multiple columns inside the parentheses. Composite keys are common on bridge tables such as OrderItems (order_id, product_id).

ALTER TABLE OrderItems
ADD CONSTRAINT pk_orderitems PRIMARY KEY (order_id, product_id);

How do I drop and recreate a PRIMARY KEY?

First drop the existing constraint, then add the new one. This pattern is useful when switching from a surrogate to a natural key.

ALTER TABLE Products DROP CONSTRAINT pk_products;
ALTER TABLE Products
ADD CONSTRAINT pk_products_new PRIMARY KEY (name);

Best practices for Snowflake PRIMARY KEYs

1) Name constraints consistently with a pk_ prefix. 2) Validate uniqueness before applying. 3) Document intent in column comments. 4) Use surrogate keys (NUMBER, SEQUENCE) for large fact tables to keep joins fast.

Common use cases

• Enforce unique customer IDs.
• Prevent duplicate order lines.
• Support referential constraints when loading data into BI tools.

What if Snowflake does not enforce the key?

Snowflake stores PRIMARY KEY metadata but does not block duplicate inserts. Pair the constraint with upstream data checks or periodic deduplication queries.