title: mysql - foreign key cascade update
body: <p>I have a question about how to update a field on cascade with a second field as constraint.</p>
  
  <p>The structure is this (I removed the unnecessary columns):</p>
  
  <p>Table <code>nodes</code> with columns <code>idNode</code> and <code>idDimension</code> (together they form the primary key).</p>
  
  <p>Table <code>forces</code> with columns <code>idForce</code> (PK), <code>idNode</code> (foreign key to <code>nodes</code>.<code>idNode</code>) and <code>idDimension</code>.</p>
  
  <p>Cascade update and delete on everything.</p>
  
  <p>The problem in this structure that it seems to appear is this:</p>
  
  <p>If in <code>nodes</code> I have an entry like (1, 1) and one like (1, 2) and in <code>forces</code> (1, 1, 1) and (1, 1, 2) and I update or delete first entry from <code>nodes</code> both entries in <code>forces</code> will be affected.</p>
  
  <p>I need to affect only the one that also has the corresponding idDimension. How can I modify current structure to do that?</p>
  
  <p>Edit: Tables - Nodes:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `nodes` (
    `idNode` varchar(11) NOT NULL,
    `idDimension` int(10) unsigned NOT NULL,
    `idNetwork` int(10) unsigned NOT NULL DEFAULT '0',
    `level` int(11) unsigned NOT NULL DEFAULT '0',
    `energy` bigint(20) DEFAULT NULL,
    `resources` bigint(20) unsigned NOT NULL DEFAULT '0',
    `x` int(11) NOT NULL,
    `y` int(11) NOT NULL,
    `name` varchar(20) DEFAULT NULL,
    `order` tinyint(3) DEFAULT '0' COMMENT 'energy 0\nassemble 1\nupgrade 2',
    `core` tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`idNode`,`idDimension`),
    KEY `network_dimension` (`idDimension`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Node table';
  </code></pre>
  
  <p>Forces:</p>
  
  <pre><code> CREATE TABLE IF NOT EXISTS `forces` (
    `idForce` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
    `idNode` varchar(11) NOT NULL,
    `idDimension` int(10) unsigned NOT NULL,
    `drones` bigint(20) DEFAULT NULL,
    `stance` tinyint(3) DEFAULT NULL COMMENT '0 - defense\n1 - neutral\n2 - attack      \n\nIf planet is parano and you are not allied to owner you can only be in attack.\n\nIf owner is allied you can only be in defense or neutral.\n\nIf you are owner you can only be in defense.',
     `order` tinyint(3) DEFAULT '0' COMMENT 'extract energy 1\nbuild node 2\nreplicate 3\nmove 4',
    `value` text,
     PRIMARY KEY (`idForce`),
     KEY `idNode` (`idNode`,`idDimension`),
     KEY `idDimension` (`idDimension`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
  </code></pre>
  
  <p>--</p>
  
  <h2>-- Constraints for table <code>forces</code></h2>
  
  <pre><code>  ALTER TABLE `forces`
      ADD CONSTRAINT `forces_ibfk_2` FOREIGN KEY (`idDimension`) REFERENCES `nodes`  (`idDimension`) ON DELETE CASCADE ON UPDATE CASCADE,
      ADD CONSTRAINT `fk_forces_nodes1` FOREIGN KEY (`idNode`, `idDimension`) REFERENCES `nodes` (`idNode`, `idDimension`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      ADD CONSTRAINT `forces_ibfk_1` FOREIGN KEY (`idNode`) REFERENCES `nodes` (`idNode`) ON DELETE CASCADE ON UPDATE CASCADE;
  </code></pre>
  
  <p>My constraints are not working as I would like so feel free to ignore them :).</p>