Search code examples
mysqlforeign-keys

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.

Solution

  • 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});