Mysql — добавить ограничение внешнего ключа в таблицу
У меня есть две таблицы с именами users, user_friends. user имеет более 10 миллионов записей, а user_friends — более 600 миллионов записей. Проблема в том, что у него нет ограничений. то есть, когда пользователь удаляется, его список друзей остается в базе данных. все работало нормально. но теперь мы реализовали новую функциональность с user_social_friends. Я применил в нем все ограничения.
теперь я хочу применить ограничение к таблице user_friends вот так
SQL query:
ALTER TABLE `user_friends` ADD CONSTRAINT `user_id_key_contstraint` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`callerap_finder`.`#sql-d9fad_24e`, CONSTRAINT `user_id_key_contstraint` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
Мой вопрос заключается в том, как я могу применить ограничение к таблице user_friends, чтобы в таблице user_friends все записи удалялись или устанавливались на ноль, чей пользователь удален.
Ответы 2
Ошибка, с которой вы столкнулись, предполагает, что в таблице user_friends существуют записи с идентификаторами userId, которых нет в таблице user. Чтобы иметь возможность добиться того, чего вы хотите, сначала убедитесь, что к таким пользователям прикреплены действительные идентификаторы пользователей, или присвойте таким пользователям нулевое значение.
Вот что вы можете сделать.
- Найдите недопустимые ссылки user_id в user_friends.
// mysql
SELECT user_friends.user_id
FROM user_friends
LEFT JOIN user ON user_friends.user_id = user.id
WHERE user.id IS NULL;- Обновите недопустимые ссылки user_id на NULL или действительный идентификатор пользователя. Например, установив для них значение NULL.
// mysql
UPDATE user_friends
LEFT JOIN user ON user_friends.user_id = user.id
SET user_friends.user_id = NULL
WHERE user.id IS NULL- После очистки таблицы user_friends со значениями, установленными для userId, вы сможете без ошибок добавить ограничение внешнего ключа.
существует более 1000 миллионов записей. ну это займет много времени.
Извините, нет лучшего способа, когда дело касается MySQL. Предлагается автоматизировать этот процесс с помощью сценария или задания cron и всегда выбирать максимальное количество элементов для выполнения этой проверки и выполнять эту операцию или устанавливать для нее значение по умолчанию. И убедитесь, что сценарий начнет выбор следующего пакета с начала последнего выбора.
Если я правильно понимаю, у вас есть записи в user_friends, которые нарушают ограничение внешнего ключа, которое вы пытаетесь добавить.
вы можете выполнить следующие действия, чтобы избежать этой ошибки
1. Сначала удалите или обнулите эти записи в таблице user_friends.
DELETE FROM user_friends WHERE user_id NOT IN (SELECT id FROM user);
2. Теперь запустите запрос, чтобы добавить ограничения.
ALTER TABLE `user_friends`
ADD CONSTRAINT `user_id_key_constraint`
FOREIGN KEY (`user_id`)
REFERENCES `user`(`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;
Но, как вы сказали, у вас очень большой объем данных, я думаю, вам нужно создать еще одну таблицу, которая берет действительные идентификаторы пользователей из таблицы user и на основе этого вы можете удалять или обновлять записи в таблице user_friends.
Например
Первый шаг
CREATE TEMPORARY TABLE valid_user_ids AS
SELECT DISTINCT id FROM user;
Шаг второй
Создайте индекс, если у вас его еще нет в таблице user_id в user_friends.
CREATE INDEX idx_user_id ON user_friends (user_id);
Шаг третий
Теперь все, что вам нужно сделать, это удалить или обнулить записи, а затем добавить ограничения для удаления, вы можете использовать что-то вроде этого
DELETE FROM user_friends
WHERE user_id NOT IN (SELECT id FROM valid_user_ids);
Шаг четвертый
Теперь просто добавьте ограничения с помощью вышеупомянутого запроса в пункте 2.
Кончик:-
Например, вы можете получать данные пакетно и обновлять их соответствующим образом.
SELECT id
FROM user_friends
LIMIT ${batchSize}
OFFSET ${offset};
Этот запрос вернет количество строк, которые вам нужно сопоставить, и получит идентификаторы таких строк const userIDs = rows.map((row) => row.id).join(','); после этого измените свой StepThree вот так
DELETE FROM user_friends
WHERE user_id NOT IN (SELECT id FROM valid_user_ids) AND id IN
(${userIDs});
Прежде чем вы сможете добавить ограничение, связи между данными должны быть удовлетворены. Добавление ограничения не удаляет строки и не изменяет значения на NULL. Вам нужно сделать это, используя DELETE или UPDATE, прежде чем пытаться добавить ограничение.