Question 260353 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql – add foreign key constraint to a table

I have two tables name users , user_friends. user has over 10 million records and user_friends has more then 600 million records. The problem is that it does not have a constraint . i.e when a user is deleted their friends list is their in the database. it was working fine. but now we have implement a new functionality that has user_social_friends . I have applied all constraint in it.

now I want to apply constraint on the user_friends table like this

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)

my question is that how can I apply a constraint on the user_friends table so in the table user_friends all those record delete or set to null who’s user is deleted.

Tags:

2

Answers


  1. 0

    The error you’re facing suggest that there are existing records in the user_friends table with userId’s that doesn’t exist in the user table. To be able to achieve what you want to do, first make sure that the valid userId’s are attached to such users, or assign a null value to such users.

    Here is what you can do.

    1. Find invalid user_id references in 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;
    1. Update invalid user_id references to NULL or a valid user ID For example, setting them to 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
    1. After the clean up in the user_friends table with the values set for the userId, you should be able to add the foreign key constraint without any errors.
    Login or Signup to reply.
  2. 0

    If i understand correctly that you have records in user_friends that violate the foreign key constraint you’re trying to add.
    you can follow these steps to avoid this error

    1.First delete or set null those records in user_friends table.

    DELETE FROM user_friends WHERE user_id NOT IN (SELECT id FROM user);

    2.Now run your query to add constraints

    ALTER TABLE `user_friends`
ADD CONSTRAINT `user_id_key_constraint`
FOREIGN KEY (`user_id`)
REFERENCES `user`(`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

    But as you said you have very large amount of data then i think you have to make another table which takes the valid user ids from user table and based on this you can delete or update records in user_friends table.

    For example

    StepOne

    CREATE TEMPORARY TABLE valid_user_ids AS
SELECT DISTINCT id FROM user;

    StepTwo

    Create index if you don’t have already on user_id in user_friends table.

     CREATE INDEX idx_user_id ON user_friends (user_id);

    StepThree

    Now all you have to do is delete or null the records and then add you constraints to delete you can use something like this

    DELETE FROM user_friends
WHERE user_id NOT IN (SELECT id FROM valid_user_ids);

    StepFour

    Now just add constraints with above mention query in point 2.

    Tip:-

    You can fetch data in batches and update them accordingly for example

    SELECT id
FROM user_friends
LIMIT ${batchSize}
OFFSET ${offset};

    This query will return numbers of rows you have to map and get ids from rows like this const userIDs = rows.map((row) => row.id).join(','); after that change your StepThree like this

    DELETE FROM user_friends
WHERE user_id NOT IN (SELECT id FROM valid_user_ids) AND id IN 
(${userIDs});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search