maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

Duplicate data cannot be inserted in MariaDB, in no cases.

PostgreSQL has a different behaviour: transactions can optionally be "deferred", which means that integrity checks will be done on commit. This means that you can temporarly insert inconsistent data, and fix them before commit.The same option is available for foreign keys and other features that I don't remember.

But we are talking about MariaDB, so... what you can do, in you example, is:
UPDATE ... SET id = NULL WHERE id = 1; -- does not exist, right?
UPDATE ... SET id = 1 WHERE id = 2; -- change other values
UPDATE ... SET id = 1 WHERE id IS NULL;

Or, if your case is more complex than this:
1) lock the table
2) drop the pk
3) update
4) unlock

Ciao
Federico