[MySQL]테이블 생성 후 FK 설정

문제상황

channel 테이블과 channel_history 테이블 사이에 channel_id로 FK를 설정해야 하는데, 테이블 생성 당시 안 하고 데이터가 이미 들어간 상황에서 FK 설정을 해야 한다.

바로 쿼리를 날려보았다.

ALTER TABLE channel_history ADD FOREIGN KEY (channel_id) REFERENCES channel(channel_id);

그 결과,

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`[DB명]`.`#sql-2d3_367d`, CONSTRAINT `channel_history_ibfk_1` FOREIGN KEY (`channel_id`) REFERENCES `channel` (`channel_id`))

라는 에러가 나왔다.

상황 파악

# channel 테이블, channel_history 테이블 row 확인
SELECT COUNT(*) FROM channel;          # 6417
SELECT COUNT(*) FROM channel_history;  # 6408
# inner join 후 row 확인
SELECT COUNT(*) FROM channel c
INNER JOIN channel_history ch
ON c.channel_id = ch.channel_id;       # 6405

테이블 생성 당시에는 FK 설정이 되어있었지만, 데이터를 적재하는 과정에서 잠시 해제하고 insert 했더니 두 테이블이 완전히 일치하진 않아, FK 설정이 되지 않았다.

어떤 테이블의 어떤 데이터가 서로 부족하고 넘치는지 확인하기 위해 쿼리를 날려보았다.

SELECT COUNT(*) FROM channel c
LEFT JOIN channel_history ch 
ON c.channel_id = ch.channel_id
WHERE ch.channel_id IS NULL
UNION
SELECT COUNT(*) FROM channel c
RIGHT JOIN channel_history ch 
ON c.channel_id = ch.channel_id
WHERE c.channel_id IS NULL; 

문제 해결

결과를 보니, channel 테이블에 있는데 channel_history 테이블에 없는 데이터가 12건, 반대의 경우가 3건이 있었다. 우선 꼭 필요한 데이터들은 아니기에 보간하지 않고 데이터를 삭제하기로 했다.

DELETE c
FROM channel c
LEFT JOIN channel_history ch ON c.channel_id = ch.channel_id
WHERE ch.channel_id IS NULL;

DELETE ch
FROM channel_history ch
LEFT JOIN channel c ON c.channel_id = ch.channel_id
WHERE c.channel_id IS NULL;

다시 한 번 쿼리 하니 깔끔하게 겹치지 않는 데이터가 사라진 것을 볼 수 있었다.
그 뒤, 외래키 설정을 해주었더니 성공🎆🎉