Is UPDATE a non-primary-key column in a table that has a primary key possible at all?

[edyu]

I read that if a column has an index, an UPDATE becomes INSERT + DELETE which could violate primary key constraint.
However, it seems that any UPDATE statement on a table that has a primary key even if you are updating other columns would fail.

Can someone show me how to update other columns in a table that has a primary key without violating the contraint?

For example, here are the table that I'm having problem UPDATING.
CREATE TABLE servers(id varchar(64) primary key DEFAULT uuid(), invite_code varchar(64) DEFAULT uuid(), created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now());

And if I run the following, it would either complain of foreign key constraint or duplicate key constraint.
UPDATE servers SET invite_code = uuid(), updated_at = now() WHERE id = '{s}' RETURNING invite_code;

[edyu]

I'm using the c/cpp API to run the query/update not sure if that would cause the problem.

[edyu]

Also, can you please try updating the invite_code with SET invite_code = uuid() to see whether your test passes? Thank you. I'm at lost at why my instance would fail.

[Tishj]

This sounds like it's related to:
https://duckdb.org/docs/sql/indexes#index-limitations

(Constraints create implicit Indexes)

[edyu]

I'm curious what is the implicit index. Is it the index on the primary key?

[edyu]

Thank you. Did you do everything in one session? Would it work if you use different thread? Mine just complains about foreign key constraints because I have another table referencing the server.id as a foreign key. If I remove the foreign key reference, it would complain duplicate key 'id' even though I'm not changing the 'id' itself.

[edyu]

Here is the error I'm getting. Constraint Error: Violates foreign key constraint because key "server_id: 086e3a1f-0e25-41c4-a873-f83e35c374f4" is still referenced by a foreign key in a different table.
The weird part is that I'm not changing the server id itself but the invite_code and updated_at columns.
And if I remove the foreign reference to the server table. It would complain that I have duplicate id. This is very likely the reference set by @Tishj.
My question is why is @l1t1 not encountering this same problem. Unless this only happens when different threads are involved so everything is wrapped in a transaction?

[edyu]

UPDATE servers SET invite_code = uuid(), updated_at = now() WHERE id = 'a0df0d8a-6a91-4d65-a005-dea4305bff1c' RETURNING invite_code;
Constraint Error: Violates foreign key constraint because key "server_id: a0df0d8a-6a91-4d65-a005-dea4305bff1c" is still referenced by a foreign key in a different table

[edyu]

If I remove the foreign key constraints from the other tables, then I get the following error:

UPDATE servers SET invite_code = uuid(), updated_at = now() WHERE id = '5d7739ff-012f-4368-ad52-d6a6a58f2699' RETURNING invite_code;
Constraint Error: Duplicate key "id: 5d7739ff-012f-4368-ad52-d6a6a58f2699" violates primary key constraint. If this is an unexpected constraint violation please double check with the known index limitations section in our documentation (docs - sql - indexes).

[edyu]

Looks like the problem is 'RETURNING invite_code;" portion.

[ruomad]

I got the same problem, and I think the messqge is misleading. In my cqse the probleö was that the column I was trying to updqte was incuded in an index. Destroying this index, then committing, then updating and recreating the index made things work. A bit of a hassle though
Is there an index on your column ?

[edyu]

nope mine doesn't

[edyu]

#7215 talks about this problem.

[taniabogatsch]

I'll close this discussion as the remaining issues should've been resolved by #15092.