사이트 내 검색:

MySQL FOREIGN KEY 설정 시 150 오류 발생

05 Mar 2014

안내

본 문서는 블로그의 운영자인 본인이 Stackoverflow에 올린 답변을 정리한 글입니다.

Stackoverflow URL

http://stackoverflow.com/questions/20364501/errno150-in-mysql-database/20364587

질문

다음과 같이 Records와 ProductsList 테이블이 존재한다.

CREATE TABLE IF NOT EXISTS Records
(
    record_id int(11) NOT NULL AUTO_INCREMENT,
    record_year year(4) NOT NULL,
    record_quarter int(1) NOT NULL,
    profit_tax int(11) NOT NULL,
    PRIMARY KEY (record_id, record_year, record_quarter),
    UNIQUE(record_year, record_quarter)
);
 
CREATE TABLE IF NOT EXISTS ProductsList
(
    product_id int(11) NOT NULL AUTO_INCREMENT,
    product_name varchar(24) NOT NULL,
    PRIMARY KEY (product_id)
);

Records와 ProductsList를 참조하는 RecordsProducts 테이블을 다음과 같이 생성하려고 한다.

CREATE TABLE IF NOT EXISTS RecordsProducts
(
    recordproduct_id int(11) NOT NULL AUTO_INCREMENT,
    ...
    ...
    PRIMARY KEY (recordproduct_id),
    FOREIGN KEY (record_id) REFERENCES Records (record_id),
    FOREIGN KEY (product_id) REFERENCES ProductsList (record_id)
);

그런데 앞의 CREATE 구문을 실행하면 150 오류가 발생한다. 이유가 무엇인가?

답변

MySQL 사용 중 불편한 점을 찾아보라면 FOREIGN KEY 관련된 오류 메시지가 불친절하다는 것을 뽑을 수 있다. FOREIGN KEY 오류를 발생시키는 원인은 여러 경우가 존재하는데 어떤 경우건 150 오류만 반환하다 보니 그 원인을 파악하기 어렵다. 질문자의 경우 RecordsProducts.product_id가 존재하지 않는 ProductsLists.record_id를 참조하고 있는데도 “컬럼이 존재하지 않는다”라는에러가 아니라 단순히 150 오류만 반환하고 있다.

흔히 FOREIGN KEY를 예로 들 때 Parent, Child 테이블을 예로 드는데 Child 테이블의 컬럼은 Parent 테이블을 참조한다(Referencing)고 이야기 하고 Parent 테이블을 Child 테이블에 의해 참조된다(Referenced)라고 한다. FOREIGN KEY 설정을 위해 Referencing 컬럼과 Referenced Column은 다음의 조건을 만족해야 한다.

  • Referenced 컬럼과 Referencing 컬럼은 데이터 타입이 동일해야 한다.
  • Referenced 컬럼은 Primary Key거나 UNIQUE INDEX이어야 한다.
  • Referencing 컬럼은 “ON DELETE SET NULL” 시 NOT NULL이면 안 된다.
  • Referencing 컬럼은 INDEX로 설정되어야 한다.

앞의 규칙만 잘 지킨다면 FOREIGN KEY 설정 오류를 줄일 수 있다. 본 책이 모두 정리되고 개정판이 나오게 된다면 그때는 Stackoverflow에 나오는 FOREIGN KEY 관련 오류 경우를 정리해서 실제 어떤 경우에 오류가 발생하고 어떻게 수정해야 하는지 예를 나열해 보고 싶다.

질문자의 경우 RecordsProducts 테이블의 구조를 완벽히 서술하지 않았기 때문에 어느 부분이 오류인지 정확히 알 수는 없다. 다음과 같이 생성된다면 FOREIGN KEY를 제대로 설정할 수 있다.

CREATE TABLE IF NOT EXISTS RecordsProducts
(
    recordproduct_id int(11) NOT NULL AUTO_INCREMENT,
 
    record_id INT,
    product_id INT,
    INDEX(record_id),
    INDEX(product_id),
 
    PRIMARY KEY (recordproduct_id),
    FOREIGN KEY (record_id) REFERENCES Records (record_id),
    FOREIGN KEY (product_id) REFERENCES ProductsList (product_id)
);

“MySQL” 카테고리의 추천 글