On Thu, Jun 12, 2014 at 9:57 AM, Federico Razzoli <federico_raz@yahoo.it> wrote:
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.

Correct. Standard SQL behaviour is checking constraints at the end of a statement. The optional ability to defer a constraint check means that the check is done at the end of the transaction and not at the end of statement. (Postgers and Oracle can do that, SQL-Server does not.)  No duplicate data will be inserted either, in both cases, deferred or not constraints. Any inconsistent data is only visible during the transaction, by the transaction - with the MVCC model no other transaction can see them before that one is committed.

What MySQL and MariaDB does is different than standard/common SQL behaviour, it is checking the constraints after every row update. That is causing the problem that Roberto faces.

 

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;

This is fine - as long as there are not any NULL values already on the table in the id column. If there are, the 3rd UPDATE will convert all of them to 1. You can use negative values - of course if and only if all your ids are either positive or NULL:

UPDATE ... SET id = -2 WHERE id = 1;
UPDATE ... SET id = -1 WHERE id = 2;
UPDATE ... SET id = -id WHERE id < 0;

This can easily be extended for more complex updates and we only need two UPDATE statements - and I suppose better inside a transaction, for InnoDB engine:

UPDATE ...
SET id = CASE id
            WHEN 1 THEN -2
            WHEN 2 THEN -1
            WHEN ...
         END
WHERE id IN (1,2) ;

UPDATE ... SET id = -id WHERE id < 0;

Pantelis
 

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

Ciao
Federico


_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp