Easily alter table data to add foreign keys without restraints

Question

I'm trying to add foreign keys to a testing database seeded with a bunch of dummy data. I could go outside MySQL, export a list of missing IDs, and generate a bunch of insert statements in Python but I'm wondering if there is a way to handle this in MySQL

ALTER TABLE `kalos`.`award` 
CHANGE COLUMN `awardee_user_id` `awardee_user_id` INT(11) NULL ;
ALTER TABLE `kalos`.`award` 
ADD CONSTRAINT `fk_award_3`
  FOREIGN KEY (`awardee_user_id`)
  REFERENCES `kalos`.`user` (`user_id`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

I've tried messing with the ON DELETE and ON UPDATE properties but it definitely won't let me apply it because there are user ID's in the award table that are not present in the user table (only about 200 out of 3000). Like I said it would be fairly simple to do this in Python but I've run into this issue a few times and am really curious to see if MySQL can handle something like this on it's own.

Somewhat related: https://stackoverflow.com/q/5014700/2422776 — Mureinik, Apr 27 '18 at 15:13

score 0 · Accepted Answer · answered Apr 30 '18 at 15:26

I figured this out and it was extremely simple.

DELETE FROM t1 
WHERE
    t1.id NOT IN (SELECT 
        id
    FROM
        (SELECT 
            tmp.id AS id
        FROM
            t1
        INNER JOIN t2 ON t1.id = t2.id) as tmp)

This will delete any row from t1 that does not have a corresponding record in t2

Easily alter table data to add foreign keys without restraints

1 Answers1