E0035 duplicate-insert-value

Duplicate primary key or unique constraint values within one INSERT ... VALUES list.

What it reports

An INSERT ... VALUES statement that provides duplicate literal values for a primary key or unique constraint across multiple rows.

Two rows of the same statement colliding on a primary key or unique constraint will always fail at runtime with a unique constraint violation error (or, in PostgreSQL, ON CONFLICT DO UPDATE command cannot affect row a second time).

This error commonly occurs in seed files, fixtures, and data migrations loading reference data.

Example

CREATE TABLE users (
  id INT PRIMARY KEY,
  email TEXT UNIQUE
);

INSERT INTO users (id, email) VALUES
  (1, 'alice@example.com'),
  (1, 'bob@example.com');

error[E0035]: Duplicate primary key value 1 in INSERT VALUES list for table 'users'
  --> q.sql:3:4
    |
  3 |   (1, 'bob@example.com');
    |    ^
    = help: Two rows in the same INSERT statement specify the same primary key value. Remove the duplicate row, or specify conflict handling (e.g. ON CONFLICT DO NOTHING)

The same applies to unique columns:

INSERT INTO users (id, email) VALUES
  (2, 'carol@example.com'),
  (3, 'carol@example.com');

error[E0035]: Duplicate key value 'carol@example.com' violates unique constraint on (email) for table 'users'
  --> q.sql:3:7
    |
  3 |   (3, 'carol@example.com');
    |       ^^^^^^^^^^^^^^^^^^^
    = help: Two rows in the same INSERT statement specify the same unique key value. Remove the duplicate row, or specify conflict handling (e.g. ON CONFLICT DO NOTHING)

When it is not reported

Nothing is reported when:

• The statement specifies conflict resolution that skips conflicting rows: ON CONFLICT DO NOTHING (targeting the key, bare, or ON CONSTRAINT ... DO NOTHING), MySQL INSERT IGNORE, or SQLite INSERT OR IGNORE.
• The statement specifies replacement or update on duplicate where the database accepts later rows: MySQL ON DUPLICATE KEY UPDATE, REPLACE INTO, SQLite INSERT OR REPLACE, or SQLite ON CONFLICT (col) DO UPDATE.
• One of the key values is NULL (in standard SQL and PostgreSQL, NULL values do not violate unique constraints unless NULLS NOT DISTINCT is defined).
• The key column uses a non-literal expression (such as a function call gen_random_uuid(), parameter $1, or subquery).

Configuring

# sqlsift.toml
[rules]
duplicate-insert-value = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0035.