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 或 null)

我如何使用 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 语句。