maria-discuss team mailing list archive
Mailing list archive
Re: newbie question
hi guys! thaks for explanations and ideas, well the first problem is ok,
change to -1 and -2 and after 2 and 1, that was what i'm considering at the
first time i see primary key duplicated, and since mysql don't suport
defered insert/update/delete, ok let's workaround :)
now the full problem....
think about a list with 'locked' and 'unlocked' positions, and important
fields that make that entry information unique...
every position (id column) must be >0, all positions <=0 i'm considering as
'inserted but not completed transition'
|id|locked|other important data|
my problem now is: change it 5 to position 2, and reposition the list,
position 2 will become position 4, and position 4 will become position 5,
position 3 will not change since it's locked='true'
i'm considering something like, change position 5 to -5, change position 4
to 5, change position 2 to 4, change -5 to 2, but how to do this only with
sql language? and the second problem is: change position 2 to position 5,
position 4 will become position 2, position 5 will become position 4
any other idea about how to do this? i think that's the easiest way,
considering that i will need a lock at positions 2,3,4,5 before start
updates, right? maybe i could do this with select * from test where id in
(2,3,4,5) for update, or lock table
ideas are wellcome
2014-06-12 8:32 GMT-03:00 Pantelis Theodosiou <ypercube@xxxxxxxxx>:
> On Thu, Jun 12, 2014 at 9:57 AM, 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.
> 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,
>> 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
> UPDATE ...
> SET id = CASE id
> WHEN 1 THEN -2
> WHEN 2 THEN -1
> WHEN ...
> WHERE id IN (1,2) ;
> UPDATE ... SET id = -id WHERE id < 0;
>> Or, if your case is more complex than this:
>> 1) lock the table
>> 2) drop the pk
>> 3) update
>> 4) unlock
>> 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
> 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
Eng. Automação e Controle