title: MySQL - on update cascade (multiple tables)
body: <p>First - I've been looking for an answer on this for the past few days with no luck. Meaning I've seen answers, tried them and still get errors. I'm to the point that looking at the code makes me sick. So any help is greatly appreciated.</p>
  
  <p>I have three tables CLIENTS, PROJECTS and PROJECT_NOTES. A project can only be assigned to one client, but clients can have multiple projects. A project can have multiple notes but that note can only be assigned to one project.</p>
  
  <p>What I'm looking to do is if I 'trash' a client then all projects associated with that client get 'trashed' as well. Then all project notes for those projects that were just trashed get 'trashed' as well.</p>
  
  <p>I can also just 'trash' a project which will 'trash' all associated project notes to be 'trashed' as well.</p>
  
  <p>I assume I need to use foreign keys and on update cascade - which I've been trying. I think I'm screwing something up with the way my primary keys are set up - but this is new to me so I could be wrong.</p>
  
  <p>I can create the tables with no problem. I can insert data into all tables without and problem. However, one I run an update query on either the CLIENTS or PROJECTS table I'm not longer able to insert data into any table except for CLIENTS.</p>
  
  <p>Here's the code used to create the tables:</p>
  
  <pre><code>CREATE TABLE clients (
    clientID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    companyName VARCHAR(128),
    clientTrash TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (clientID, clientTrash),
    INDEX (companyName)
  )ENGINE=INNODB;
  
  
  CREATE TABLE projects (
    projectID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    clientID INT UNSIGNED NOT NULL,
    projectTitle VARCHAR(128),
    projectTrash TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (projectID, projectTrash),
    INDEX (projectTitle),
    FOREIGN KEY (clientID, projectTrash) REFERENCES clients (clientID, clientTrash)
      ON DELETE CASCADE
      ON UPDATE CASCADE
  )ENGINE=INNODB;
  
  
  CREATE TABLE project_notes (
    projectNoteID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    projectID INT UNSIGNED NOT NULL,
    note TEXT,
    projectNoteTrash TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (projectNoteID, projectNoteTrash),
    FOREIGN KEY (projectID, projectNoteTrash) REFERENCES projects (projectID, projectTrash)
      ON DELETE CASCADE
      ON UPDATE CASCADE
  )ENGINE=INNODB;
  </code></pre>
  
  <p>(Hopefully that code was formatted correctly - It's my first time posting here).</p>
  
  <p>If there's a better (easier) method than using on update cascade please let me know. And again thank you for any and all help.</p>
  
  <p>Forgot to add: After running an update query to set clientTrash = 1 in the CLIENTS table. I'll try to insert values into PROJECTS and end up with error #1452:
  Cannot add or update a child row: a foreign key constraint fails (<code>_clientmanage</code>.<code>projects</code>, CONSTRAINT <code>projects_ibfk_1</code> FOREIGN KEY (<code>clientID</code>, <code>projectTrash</code>) REFERENCES <code>clients</code> (<code>clientID</code>, <code>clientTrash</code>) ON DELETE CASCADE ON UPDATE CASCADE)</p>