MySQL - 更新级联(多个表)

Question

首先 - 过去几天我一直在寻找这方面的答案，但没有成功。这意味着我已经看到答案，尝试过它们但仍然会出错。我已经到了看到代码让我恶心的地步。因此，非常感谢任何帮助。

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

我想要做的是，如果我“破坏”了一个客户，那么与该客户相关的所有项目也会被“破坏”。然后，那些刚刚被丢弃的项目的所有项目注释也会被“丢弃”。

我也可以只“丢弃”一个项目，该项目将“丢弃”所有相关的项目注释以进行“丢弃”。

我假设我需要使用外键和更新级联——我一直在尝试。我认为我的主键设置方式搞砸了 - 但这对我来说是新的，所以我可能是错的。

我可以毫无问题地创建表格。我可以毫无问题地将数据插入所有表。但是，如果我在 CLIENTS 或 PROJECTS 表上运行更新查询，我将无法再将数据插入到除 CLIENTS 之外的任何表中。

这是用于创建表的代码:

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;

(希望代码格式正确 - 这是我第一次在这里发帖)。

如果有比使用更新级联更好(更简单)的方法，请告诉我。再次感谢您提供的所有帮助。

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

Answer 1

我认为您对参照完整性(外键、级联更新和级联删除)的使用和适用性感到困惑。级联更新和删除是一种在相关表中保持引用完整性的方法，因此根/父主键的更改会级联到从属/子行，因此如果父 ID 更改，关系不会中断。顺便说一句，仅当在 MySQL 中使用 InnoDB 引擎时才强制执行引用完整性。

您真正想要的是一种执行业务规则的方法，将实体的状态级联到相关实体。在数据库级别使用的工具是触发器，它是一种特殊的存储例程，只要插入、更新或删除行就会执行。您可以在 clients 和 projects 表上设置一个 AFTER UPDATE 触发器来级联 trash 状态。

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