php - MySQL - 有没有办法保持数据集的引用完整性？

Question

采用以下数据集:

Users
(int)   (varchar)       (varchar)     (varchar)
id    |   username   |   password   | stuff   |
1         john           cat          stuff
2         jack           dog          stuff
3         bill           bird         stuff
.
.
.
n         userN          lol          stuff

和

Colors
(int)          (varchar)
userid     |    color
1               green
2               green
3               yellow
.               .
.               .
.               .
n               purple

现在我们显然可以看到颜色和用户是通过用户 ID 链接起来的。我们正确的用户删除脚本会从颜色表中删除所有匹配的用户 ID，然后删除用户表中的用户。假设我们有一个懒惰的管理员，他手动通过 SSH 连接到服务器并从用户表中删除用户。我们假设“john”已被删除。

当我们的颜色检索脚本运行时，我们遇到了一个问题(为了简单起见，使用 PHP):

<?
$query = "SELECT * FROM colors WHERE color = 'green'";
$result = mysql_query($query);

while ($row = mysql_fetch_asoc($result)) {
     //The code bricks here.
     $query2 = "SELECT * FROM users WHERE id = " . $row['id']. " LIMIT 1";
     $result2 = mysql_query($query2);
     $row2 = mysql_fetch_asoc($result2);
     echo $row2['username'] . "<br />"
}

?>

MySQL 本身是否有一个概念，如果该用户 ID 在颜色表中，则不允许管理员删除用户表中的条目。请原谅我的无知，我不是 DBA。

Answer 1

您想要的是带有ON DELETE CASCADE的FOREIGN KEY。 FOREIGN KEY 约束将要求 Colors 表上的 userid 引用另一个表上的值。阅读它们 here .

创建表时，您可以像这样指定 key :

CREATE TABLE users (
    id BIGINT,
    (other columns etc)
);

CREATE TABLE colors (
    userid BIGINT,
    (other columns etc),
    FOREIGN KEY `userid_fk` (`userid`) REFERENCES users(`id`) ON DELETE CASCADE
 );

该行表示的含义:您需要在 userid 列上使用名为 userid_fk 的外键。 userid 列中的任何值都必须存在于 users 表的 id 列中。这样，即使有人删除了users行，删除也会CASCADE，这意味着它也会删除colors表上的所有相关行。