Can't create table `visit_plans` (errno: 150 "Foreign key constraint is incorrectly formed") - JPA에서 테이블 복사 후 외래키 에러 해결

Situation (상황)

테이블 이름 변경을 해야하는데 기존 서비스 호환성을 위해 franchise_crawling 테이블에 있던 데이터를 그대로 복사해서 stores 테이블을 하나 만들었습니다. 

CREATE TABLE stores AS
SELECT * FROM franchise_crawling;

 

Task (문제)

이렇게 하면 끝이라고 생각했는데, 오류가 발생하였습니다.

visit_plans 테이블에서 stores.id를 참조하는 외래키를 걸려고 하니, Hibernate 로그에서 이런 에러가 터졌습니다.

Can't create table `visit_plans` (errno: 150 "Foreign key constraint is incorrectly formed")

 

처음엔 단순히 타입 불일 치 문제로 생각되어 INT -> BIGINT로 맞춰주었지만 똑같은 오류가 나타났습니다.

 

원인을 다시 보니,

CREATE TABLE ... AS SELECT ... 구문은 데이터만 복사하고 PK, AUTO_INCREMENT, 인덱스, 유니크 키는 전혀 안 따라옵니다. 

원래 franchise_crawling 테이블에는

• id에 대한 PK (ix_franchise_crawling_id)
• (name, address)에 대한 복합 유니크 인덱스

이 조건들이 있었는데, stores 테이블에는 데이터만 복사되고 pk, idx, unique 설정등 구조는 복사되지 않았습니다.

 

예시

franchise_crawling 테이블이 이렇게 생겼다고 하면:

CREATE TABLE franchise_crawling (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL,
  keyword VARCHAR(100),
  UNIQUE KEY unique_restaurant_location (name, address)
);

위 쿼리(CREATE TABLE stores AS SELECT ...)를 실행하면 stores 테이블은:

CREATE TABLE stores (
  id BIGINT,
  name VARCHAR(255),
  address VARCHAR(255),
  keyword VARCHAR(100)
);

이렇게 생성됩니다. 보시다시피 PK, AUTO_INCREMENT, UNIQUE 제약조건 전부 사라진 상태입니다.

 

 

MySQL에서 외래키(FK)를 걸려면 참조 대상 컬럼이 반드시 PK나 Unique Key 여야 합니다.

그런데 CREATE TABLE ... AS SELECT ...로 만든 stores 테이블은:

• id 컬럼은 있었지만 PRIMARY KEY가 아니었음
• 따라서 id 컬럼에 인덱스도 없는 상태였음

즉, PK(=인덱스) 가 없으니 Hibernate가 FK를 걸려고 해도 MySQL이 거부한 것입니다.

 

Action (해결 과정)

해결은 간단했습니다. PK와 Unique 키를 다시 설정해주면 됐습니다.

ALTER TABLE stores
  MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (id);

ALTER TABLE stores
  ADD CONSTRAINT unique_restaurant_location UNIQUE (name, address);

 

이렇게 하고 나니 외래키 제약조건이 오류없이 정상적으로 생성되었습니다.

 

Result (결과)

이렇게 수정하니 Hibernate가 외래키를 정상적으로 생성했고, 더 이상 에러는 발생하지 않았습니다.

추가로 알게 된 건, 구조까지 같이 복사하려면 다음 구문을 쓰는 게 훨씬 낫다는 겁니다.

CREATE TABLE stores LIKE franchise_crawling;
INSERT INTO stores SELECT * FROM franchise_crawling;

이 방식이면 PK, 인덱스, 유니크 조건까지 그대로 따라와서 따로 ALTER를 하지 않아도 됩니다.

 

작은 실수였지만 덕분에 CREATE TABLE ... AS SELECT ...의 동작 차이를 확실히 알게 됐습니다.
데이터만 필요할 때와 구조까지 복사해야 할 때를 구분해서 써야 한다는 교훈을 얻습니다.

혹시 저처럼 테이블 복사 후 외래키 에러를 겪으셨다면, PK/Unique/인덱스 설정을 다시 확인해보시길 추천드립니다