MySQL - 更新级联（多个表）

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.

首先 - 过去几天我一直在寻找一个没有运气的答案。意思是我已经看到了答案,尝试过它们但仍然会出错。我注意到查看代码会让我感到恶心。所以任何帮助都非常感谢。

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.

我有三个表CLIENTS,PROJECTS和PROJECT_NOTES。项目只能分配给一个客户,但客户可以有多个项目。项目可以有多个注释,但该注释只能分配给一个项目。

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.

我可以毫无问题地创建表格。我可以将数据插入到所有表中而没有问题。但是,我在CLIENTS或PROJECTS表上运行更新查询,我不再能够将数据插入除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)

忘记添加:运行更新查询后,在CLIENTS表中设置clientTrash = 1。我将尝试将值插入PROJECTS并最终出现错误#1452:无法添加或更新子行:外键约束失败(_clientmanage.projects,CONSTRAINT projects_ibfk_1 FOREIGN KEY(clientID,projectTrash)REFERENCES clients(clientID,clientTrash) )ON UPATE CASCADE ON UPDATE CASCADE)

3 个解决方案

#1

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.

我认为您对引用完整性(外键,级联更新和级联删除)的使用和适用性感到困惑。级联更新和删除是一种在相关表中维护参照完整性的方法,因此根/父主键的更改将级联到依赖/子行,以便在父ID更改时不会破坏关系。顺便说一句,只有在MySQL中使用InnoDB引擎时才会强制执行引用完整性。

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.

您真正想要的是一种强制执行业务规则的方法,该规则将实体的状态级联到依赖实体。在数据库级别使用的工具是一个触发器,它是一种特殊的存储例程,只要插入,更新或删除行就会执行。您可以在客户端和项目表上设置AFTER UPDATE触发器以级联垃圾状态。

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.

从设计和体系结构的角度来看,这种行为通常在业务逻辑代码上处理,而不是在数据库级别处理。

#2

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.

将您的密钥设置为id + trash意味着您可以拥有2个项目 - 一个ID为id + trashed,其中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.

示例:A(id:4 trashed:0)连接到B(id:17,trashed:0,foreign(id:4:trashed:0))。当A被“删除”时,它的主键现在是(id:4 trashed:1)。 B仍然是(id:17,trashed:0,foreign(id:4:trashed:0))。 B的外来(id:4:trashed:0)不再存在(或不再存在),这是不好的并且会导致问题。

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.

另一种方法(可能更容易)是在实现中设置存储过程或事务,在一系列较小的步骤中执行您希望它执行的操作。

#3

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.

我认为这就是你想要的,使用向下级联'trash = 1'设置的触发器:如果客户端被移动到垃圾箱,那么项目和笔记也是如此。但是,如果您从垃圾箱中恢复客户端,则不会自动恢复注释,并且当客户端没有时,您可以将项目移动到trahs。

(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.

这处理插入项目和更新客户端,将它们转换为插入项目注释和更新项目应该是类似的。

#1