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| |1|false|blabla| |2|false|blabla2| |3|true|blabla3| |4|false|blabla4| |5|false|blabla5| |6|true|blabla6| 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 <yperc...@gmail.com>: > > > > On Thu, Jun 12, 2014 at 9:57 AM, Federico Razzoli <federico_...@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 >> > > > _______________________________________________ > 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 > > -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
_______________________________________________ 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
