maria-discuss team mailing list archive

[Pantelis Theodosiou <ypercube@xxxxxxxxx>]

On Thu, Jun 12, 2014 at 9:57 AM, Federico Razzoli <federico_raz@xxxxxxxx>
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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>