Unexpected Constraint Error on UPDATE Query Without Changing Primary Key

[ZhangZiFei]

I encountered an unexpected behavior while using DuckDB.NET with a specific table schema and an UPDATE query. The issue seems to be related to the existence of an index in conjunction with the primary key constraint.

Table Schema:

CREATE TABLE Tag (
  key VARCHAR(65535) NOT NULL,
  name VARCHAR(65535) NOT NULL,
  value VARCHAR(65535) NULL,
  PRIMARY KEY (key, name)
);

CREATE INDEX idx_name ON Tag(name);
CREATE INDEX idx_value ON Tag(value);

Query:

INSERT INTO Tag (key, name, value) VALUES (?, ?, ?);
UPDATE Tag SET value = ? WHERE key = ? AND name = ?; --Error

Error:

DuckDB.NET.Data.DuckDBException: Constraint Error: Duplicate key "key: key1, name: name1" violates primary key constraint.

Interestingly, the error disappears when I remove the index idx_value.

Is this behavior expected? Am I missing something here, or is this a bug?

[Giorgi]

Do you get the same error when you use the Duckdb cli or does this happen only from DuckDB.Net?

[ZhangZiFei]

I tested it with Python and encountered the same exception. My apologies, I must have asked in the wrong place.

[Giorgi]

Yes, it makes more sense to ask at https://github.com/duckdb/duckdb/ or their discord.