복합키를 주키(Primary Key)로 사용한 테이블에 외래키(Foreign Key) 참조하는 방법 (재귀 참조)

복합키(복수개의 컬럼으로 구성된 키)를 주키로 사용한 테이블을 외래키로 참조할 경우, 해당 복합키 모두 참조해야만 합니다. 

그렇지 않고 주키 중 한개의 컬럼만 외래키(FK) 를 걸면 sql 에러가 난다. 해당 컬럼에 대해서 외래키를 걸려면 해당 부분키에 대해 인덱스를 생성후 걸어주면 된다.

다음과 같이 고치면 됩니다.

답변

CREATE TABLE child ( id INT NOT NULL, parent_no INT NOT NULL, parent_id INT NOT NULL, PRIMARY KEY(id), FOREIGN KEY(parent_no,parent_id) REFERENCES parent(no,id) ) ENGINE = InnoDB;

parent_no INT NOT NULL을 추가하고, 참조 Constraint 에도 FOREIGN KEY(parent_no,parent_id) REFERENCES parent(no,id)와 같이 변경.

수정답변

만약 child record가 parent 레코드 1개에 대해 참조하는 것이 아니라, 특정 부분키(복합키의 일부)만 참조하려고 한다면, 부분키에 대한 키(혹은 인덱스)를 생성해줘야만 합니다.

CREATE TABLE parent ( no INT AUTO_INCREMENT NOT NULL , id INT NOT NULL, PRIMARY KEY(no, id), INDEX idx_parent_id(id) ) ENGINE = InnoDB;

혹은

CREATE TABLE parent ( no INT AUTO_INCREMENT NOT NULL , id INT NOT NULL, PRIMARY KEY(no, id) ) ENGINE = InnoDB; CREATE INDEX idx_parent_id ON parent(id);

위와 같이 parent 테이블의 id에 대한 단독 인덱스(키)를 생성해준 후, 질문해주신 다음의 child 테이블과 같이 생성하면 됩니다.

CREATE TABLE child ( id INT NOT NULL, parent_id INT NOT NULL, PRIMARY KEY(id), FOREIGN KEY(parent_id) REFERENCES parent(id) ) ENGINE = InnoDB;