Add and drop hybrid table constraints online in Snowflake

With this update, managing constraints on transactional tables is easier. Previously, modifying constraints on a hybrid table required recreating and reloading the entire table. Now, the operation runs online and in the background, keeping the table active for queries and writes.

Use cases

Imagine you need to add a foreign key to a production table handling thousands of concurrent requests without causing downtime.

• Add UNIQUE or FOREIGN KEY constraints to existing tables.
• Drop constraints without interrupting active applications.
• Validate new integrity rules without taking down the operational workflow.

How to use

Run the ALTER TABLE command to add the desired constraint:

ALTER TABLE player ADD CONSTRAINT fk_player_team
  FOREIGN KEY (team_id) REFERENCES team (team_id);

What happens in this code:

1. Snowflake starts building the required index in the background.
2. The command returns before full completion to avoid blocking the workflow.

To track the progress of the index build, use the SHOW INDEXES command:

SHOW INDEXES;

What happens in this code:

1. Snowflake lists the table indexes.
2. The status column displays BUILD IN PROGRESS while the process runs and ACTIVE when the constraint is ready.

Limitations and requirements

Generally available for accounts in AWS and Microsoft Azure commercial regions. If validation fails on existing rows during constraint addition, the statement succeeds, but the background build reports a validation failure status, enforcing the rule only for new writes.