MySQL - on update cascade (multiple tables)

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 l开发者_JS百科ooking at the code makes me sick. So any help is greatly appreciated.

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.

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.

I can also just 'trash' a project which will 'trash' all associated project notes to be 'trashed' as well.

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.

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.

Here's the code used to create the tables:

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;

(Hopefully that code was formatted correctly - It's my first time posting here).

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.

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 (_clientmanage.projects, CONSTRAINT projects_ibfk_1 FOREIGN KEY (clientID, projectTrash) REFERENCES clients (clientID, clientTrash) ON DELETE CASCADE ON UPDATE CASCADE)

I think you are confused about the use and applicability of referential integrity (foreign keys, cascade updates and cascade deletes). Cascade updates and deletes are a way to maintain referential integrity in your related tables, so changes on the root/parent primary keys are cascaded down to the dependent/child rows, so that the relation is not broken if the parent Id changes. By the way, referential integrity is only enforced when using InnoDB engine in MySQL.

What you really want is a way to enforce a business rule, that cascades the state of an entity down to the dependent entities. The tool to use at the database level is a trigger, which is a special kind of stored routine that executes whenever a row is inserted, updated, or deleted. You can setup an AFTER UPDATE trigger on the clients and projects tables to cascade the trash status.

From a design and architecture point of view, this kind of behavior is typically handled on the business logic code, not at the database level.

Setting up your keys as id+trash means that you can have 2 of an item - one with id+trashed one with the same id+not_trashed.

Get the trashed out of the primary/foreign keys and that should fix your issues of editing.

I believe (could be wrong about this bit) what is happening is that when something gets trashed the primary key changes which breaks the foreign key relationship.

Example:
A (id:4 trashed:0) is connected to B (id:17, trashed:0, foreign(id:4: trashed:0)).
When A is "trashed" it's primary key is now (id:4 trashed:1).
B is still (id:17, trashed:0, foreign(id:4: trashed:0)).
B's foreign(id:4: trashed:0) no longer exists (or would no longer exist) which is bad and causes problems.

Another way of doing this (which may be easier) is to either set up a stored procedure or transaction in implementation that does what you want it to do in a series of smaller steps.

I think this is what you want, using triggers that cascade the 'trash=1' setting downward: if a client is moved to trash, so are the projects and the notes. However, if you resurrect a client from the trash, the notes are not automatically restored, and you can move a project to the trahs when a client is not.

(This only handles the update - you still need to ensure that one doesn't insert non-trashed projects for trashed clients. I am still struggling with that one. I'll post an update if I find it.)

DELIMITER //
CREATE TRIGGER trgClientToTrash 
    AFTER UPDATE ON clients 
    FOR EACH ROW 
    BEGIN 
        IF NEW.clientTrash = 1 THEN
            UPDATE projects SET projects.projectTrash = NEW.clientTrash 
                           WHERE projects.clientID = NEW.clientID;
        END IF;
    END;
 //

Edited to add: the insert trigger

delimiter //

create trigger insProjectTrash 
    before insert on projects 
    for each row
    begin
        declare trash numeric;
        select greatest(clientTrash, new.projectTrash) into trash 
               from clients where clientID = new.clientID;
        set new.projectTrash = trash;
    end; 
//

This handles inserting projects and updating clients, converting them to inserting project notes and updating projects should be similar.