Primary key and unique constraint enforcement in Redshift

Amazon Redshift does not enforce uniqueness or primary key constraints during data loads or inserts. The only constraint Redshift actively enforces is NOT NULL.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY, -- Informational only!
    email VARCHAR(100) UNIQUE,   -- Informational only!
    name VARCHAR(100) NOT NULL   -- Enforced on insert!
);

Why constraints exist if they are not enforced

Constraints are informational metadata used by the query optimizer. The query planner assumes your tables have unique values on primary key columns. For example, if it knows a key is unique, it can eliminate unnecessary subqueries or convert outer joins into inner joins.

If you load duplicate keys into a table with an unenforced primary key, queries can return incorrect aggregations, duplicate rows, or inconsistent join results.

The idempotent staging pattern

Because Redshift does not reject duplicate keys, your pipeline must enforce uniqueness before committing rows to the production table. The industry standard pattern uses a staging table inside a single transaction:

BEGIN TRANSACTION;

-- Delete matching records from target table
DELETE FROM target_customers
USING staging_customers
WHERE target_customers.customer_id = staging_customers.customer_id;

-- Insert fresh records from staging
INSERT INTO target_customers
SELECT * FROM staging_customers;

COMMIT;

Running both statements inside a single transaction ensures that if the pipeline crashes midway, uncommitted changes roll back cleanly.