我刚刚用PHP完成了我的第一个站点,现在我刚刚注意到一个问题。我的许多动态内容都"链接"到其他表中的行。因此,当其他表中的那些行被删除时,显然会给我一个错误。
例如,我有一个表"book_groups",它从表"books"、"hosts"answers"locations"中提取数据。因此,在我查询某个book_group后,我会根据与该组一起存储的book_id、host_id和location_id对其他表进行更多查询。
我显然计划得不够好。因为如果一本书被意外删除,然后重新添加,id就会改变,它就会被破坏。
我的问题是,既然我几乎已经全部完成了,解决这个问题的最佳方法是什么?我可以返回到每个查询并添加一个if($query_result!=false)来查看该行是否仍然存在。但是,伙计,有很多查询,因为我有很多不同类型的组和相关的表。
为了将来参考,有没有更好的方法可以在MySQL中链接表,而不仅仅是存储相关表行的id?
您的主题是参考完整性。在MySQL中,您可以通过数据库触发器强制执行这种完整性。
例如,您可以定义一个触发器,该触发器在父记录上发生delete
之前执行:
CREATE TRIGGER trg_books_bef_delete
BEFORE DELETE
ON books
FOR EACH ROW
BEGIN
DELETE FROM book_groups WHERE book_id = old.book_id;
END;
此特定触发器将删除book_groups
中引用要删除的book
记录的任何从属记录。
您可以决定以不同的方式响应,并在删除具有从属记录的book
记录时引发错误。从MySQL 5.5开始,您可以这样做:
CREATE TRIGGER trg_books_bef_delete
BEFORE DELETE
ON books
FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM book_groups WHERE book_id = old.book_id) > 0
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Foreign Key Constraint Violated!';
END IF;
END;
在这种情况下,如果发现引用被删除的book
记录的book_groups
记录,则引发严重异常,并且取消delete
操作。
您可以在此处阅读有关MySql
引用完整性的信息。
您可以在PHP
中做类似的事情,但当通过其他工具(如phpAdmin
)连接时,可以绕过这些代码。触发器的优点是,它们的执行独立于连接的位置,并且与主操作在同一事务中执行。缺点是您需要维护php
代码旁边的另一层代码。并且php
代码必须意识到这些触发器可能对其他表产生的副作用。
在php
:中对book
进行受控delete
的一些想法
$confirmed = false;
if (isset($_POST["confirm"])) {
$confirmed = true;
}
$book_id = $_POST["book_id"]);
$con = mysqli_connect(...);
// Check for dependent records that would also be deleted:
if ($stmt = mysqli_prepare($con,
"SELECT COUNT(*) AS cnt FROM book_groups WHERE book_id = ?")) {
mysqli_stmt_bind_param($stmt, "i", $book_id);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $cnt);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);
if ($cnt > 0 && !$confirmed) {
?>
<html>
<body>
<form action="" method="post">
<input type="hidden" name="book_id" value="<?=$book_id?>"/>
This book is categorised in groups,
are you sure to delete that information?
<input type="submit" value="confirm" name="confirm"/>
</form>
</body>
</html>
<?php
exit;
}
}
// continue to delete book below...