我读过类似的问题,但没有关于使用 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 来做到这一点 - 或者 - 我应该采取什么程序来实现我想在这里做的事情?
感谢您的阅读。
在外键规范中指定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语句。