MySQL — есть ли способ сохранить ссылочную целостность моего набора данных?

Question

MySQL — есть ли способ сохранить ссылочную целостность моего набора данных?

Возьмите следующий набор данных:

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

Теперь мы можем видеть, что цвета и пользователи связаны идентификатором пользователя. Наш правильный скрипт удаления пользователя отправляет и удаляет все совпадающие идентификаторы пользователей из таблицы цветов, а затем удаляет пользователя из таблицы пользователей. Допустим, у нас есть ленивый администратор, который вручную подключает SSH к серверу и удаляет пользователя из таблицы users. Давайте предположим, что «Джон» был удален.

Затем у нас возникает проблема, когда работает наш скрипт поиска цвета (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 концепция, которая не позволит администратору УДАЛИТЬ запись в таблице пользователей, если этот ИД пользователя находится в таблице цветов. Прошу прощения за мое невежество, я не администратор баз данных.

0

database-administration mysql php referential-integrity

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Что вы хотите FOREIGN KEYс ON DELETE CASCADE, FOREIGN KEY ограничение потребует, чтобы userid на Colors таблица ссылается на значение в другой таблице. Читайте о них Вот.

При создании таблицы вы можете указать свой ключ следующим образом:

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_fk на userid колонка. Любое значение в userid столбец должен существовать в users таблицы id колонка. При этом, даже если кто-то удаляет users строка, удаление будет CASCADEЭто означает, что он также удалит все соответствующие строки в colors Таблица.

0