PHP，PDO，MySQL和InnoDB，如何使用FK更新/删除记录？

Question

我读过类似的问题，但没有关于使用ON DELETE和ON UPDATE的非常详细的解释，所以我很难理解这个问题，以及当您想要删除或更新使用FK约束进行记录，但在引用时不会删除其他表中的记录，而是将这些引用设置为null或0或其他表中的某些内容。

背景

我试图创造的功能，这个数据库（电子购物车系统），一库，数据库可以通过管理员（如CMS）来管理。

我选择了InnoDB引擎来利用外键和事务。

我也使用PHP PDO对象而不是标准mysql_-函数来使用参数化查询。

这是这些表的SQL创建代码：

# TBL_MENU_CATEGORY 

DROP TABLE IF EXISTS tbl_menu_category; 
CREATE TABLE tbl_menu_category(
    id INT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY(id), 
    name VARCHAR(50) 
) ENGINE = InnoDB; 

# TBL_PRODUCT_CATEGORY 

DROP TABLE IF EXISTS tbl_product_category; 
CREATE TABLE tbl_product_category(
    id INT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY(id), 
    name VARCHAR(50) 
) ENGINE = InnoDB; 


# TBL_MENU_CAT_BASKET 

DROP TABLE IF EXISTS tbl_menu_cat_basket; 
CREATE TABLE tbl_menu_cat_basket(
    menu_id INT, 
    FOREIGN KEY(menu_id) REFERENCES tbl_menu_category(id), 
    cat_id INT, 
    FOREIGN KEY(cat_id) REFERENCES tbl_product_category(id) 
) ENGINE = InnoDB; 


# TBL_PRODUCT_CAT_BASKET 

DROP TABLE IF EXISTS tbl_product_cat_basket; 
CREATE TABLE tbl_product_cat_basket(
    cat_id INT, 
    FOREIGN KEY(cat_id) REFERENCES tbl_product_category(id), 
    product_id INT, 
    FOREIGN KEY(product_id) REFERENCES tbl_product(id) 
) ENGINE = InnoDB; 

这是在库中的函数将子菜单添加到菜单（菜单>子菜单>产品）。

function addSubmenu($menu_id, $sub_name) { 
    $success = false; 
    if (dbTransaction()) { 
     $sql = "INSERT INTO tbl_product_cat(name) VALUES ('?');"; 
     dbQuery($sql, array($sub_name)); 
     $rows = dbRowsAffected(); 
     if ($rows == 1) { 
      $cat_id = dbLastInsertId(); 
      $sql = "INSERT INTO tbl_menu_cat_basket(menu_id, cat_id) VALUES ('?','?');"; 
      dbQuery($sql, array((int)$menu_id, (int)$cat_id)); 
      $rows = dbRowsAffected(); 
      if ($rows == 1) { 
       $success = true; 
      } 
     } 
     if ($success) 
      dbCommit(); 
      return "Add submenu successful."; 
     else 
      dbRollback(); 
      return "Add submenu failed."; 
    } 
} 

查询是这样执行的：

require_once "pdo.php"; # Creates PDO object '$db' 
$query; 

function dbQuery($sql, $data) { 
    global $db, $query; 
    $query = $db->prepare($sql); 
    $query->execute($data); 
} 

# other PDO & PDOStatement methods like this 
# (rowCount, lastInsertId, fetchAll, transaction-stuff). 

问题

我甚至不知道这是正确的。我遵循正确的道路还是错误地做事？

现在我被困在创建类似的功能来更新也有外键的记录。我知道InnoDB会抛出一个错误，即记录无法更新，因为它有引用（我认为？）。

我需要知道正确的程序更新记录和我这样做可用的功能。

我想知道ON DELETE和ON UPDATE约束，但我似乎无法找到任何好的新手友好的教程（MySQL文档非常不友好，甚至不突出语法）。

我想做

当更新我想级联更新，以便所有引用更新的记录什么。

当删除记录时，我只想删除已删除的记录，但是可以重置对它的任何引用（如果删除了某个产品类别，则不应该删除该产品;​​如果删除了该产品的所有订单不应删除，但产品价值设置为0或空）

我怎么能做到ON DELETE和ON UPDATE - 或者 - 我应该采取什么程序来实现我想在这里做的事情？

谢谢您的阅读。

Answer 1

在你的外键规范中指定ON UPDATE CASCADE ON DELETE SET NULL应该有诀窍。例如FOREIGN KEY(product_id) REFERENCES tbl_product(id) ON UPDATE CASCADE ON DELETE SET NULL。尽管我不得不想知道在哪里可以预见改变主键值的情况。

您可能会感兴趣的MySQL Workbench - 它可以帮助你直观地设计架构和显示相应的DDL语句。