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