I am not sure I understand. If I make the autoincrement column as part
of the primary key as (rev + cluster + file), how do I ensure that a
reset of the revision number is done as soon as (cluster + file)
combination changes? It looks like I need to do the following to mimic
the same behavior as that of an autoincrement column in MyISAM
SELECT @id := IFNULL(MAX(rev), 0) FROM table WHERE cluster='clusterA'
AND file='fileA' ;
SET @id := @id + 1;
INSERT INTO table (cluster, file, rev) VALUES ('clusterA', 'fileA', @id);
Additionally I guess the above needs to be encapsulated in a transaction
to ensure atomic updates to the 'rev' number for a given cluster and
file combination. Any thoughts?
Thanks
Aveek
Johan De Meersman wrote:
You can't, iirc - if you add an autoincrement to InnoDB it MUST be the
primary key.
You *can*, however, add that, set it as PK and stick a unique index on
(cluster, file) instead. Behaviour will be identical, but be aware
that there will be some performance implications - you will now have
to do an extra primary key lookup every time you select based on the
(cluster,file) key.
On Thu, Apr 22, 2010 at 7:03 AM, Aveek Misra <ave...@yahoo-inc.com
<mailto:ave...@yahoo-inc.com>> wrote:
I have a InnoDB table which contains columns named 'cluster' and
'file' ('cluster' + 'file' is a primary key). I want to add a new
column that tracks the revision number of a file for a given
cluster and a file. The situation is tailor made for a MyIsam
table where I can add a new Auto Increment column as a secondary
column in a multiple column index. How can I get the same behavior
in an InnoDB table? Given below is a view of how the records will
look like
--------------------------------------------------------
| Cluster | File | Rev |
--------------------------------------------------------
| clusterA | fileA | 1 |
--------------------------------------------------------
| clusterA | fileA | 2 |
---------------------------------------------------------
| clusterB | fileA | 1 |
--------------------------------------------------------
| clusterB | fileB | 1 |
--------------------------------------------------------
Thanks
Aveek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
