Foreign Key 생성 시 Index 생성

[문제점]

MariaDB에서는 FK 생성 시에 참조 키를 빠르게 확인하고 테이블 스캔을 하지 않으려고 인덱스를 생성함.

[테스트]

1. 스크립트

CREATE DATABASE ADB_TEST ;

-- ADS_기업
CREATE TABLE `ADB_TEST`.`ADS_CO` (
  `CO_NO`             DECIMAL(9)   NOT NULL, -- 기업번호
  `CO_NM`             VARCHAR(100) NULL,     -- 기업명
  `HDOF_ADDR`         VARCHAR(500) NOT NULL, -- 본사주소
  `DLG_TEL_NO`        VARCHAR(20)  NULL,     -- 대표전화번호
  `DLG_DRTR_NM`       VARCHAR(100) NOT NULL, -- 대표이사명
  `ESTB_DT`           VARCHAR(8)   NULL,     -- 설립일자
  `SALES_AMT`         DECIMAL(20)  NULL     COMMENT '매출금액|매출액(단위 : 원)||', -- 매출금액
  `SALES_AMT_BASE_DT` VARCHAR(8)   NULL,     -- 매출금액기준일자
  `CO_FORM_CODE`      VARCHAR(6)   NOT NULL COMMENT '기업형태코드|기업형태코드||', -- 기업형태코드
  `EMP_CNT`           DECIMAL(9)   NOT NULL, -- 사원수
  `REG_DTTM`          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시|최초 등록일시를 기록한다.||', -- 등록일시
  `ALT_DTTM`          DATETIME     NULL     COMMENT '수정일시|수정일시를 기록한다.||' -- 수정일시
)
DEFAULT CHARACTER SET = 'utf8'
DEFAULT COLLATE = 'utf8_bin'
ENGINE = InnoDB
COMMENT 'ADS_기업|기업정보||';
 
-- ADS_기업
ALTER TABLE `ADB_TEST`.`ADS_CO`
  ADD CONSTRAINT `ADS_CO_PK` -- ADS_기업 기본키
    PRIMARY KEY (
      `CO_NO` -- 기업번호
    );
     
-- ADS_채용
CREATE TABLE `ADB_TEST`.`ADS_RECT` (
  `RECT_NO`         DECIMAL(9)   NOT NULL, -- 채용번호
  `RECT_TITLE`      VARCHAR(100) NOT NULL, -- 채용제목
  `PSNCH_NM`        VARCHAR(100) NULL,     -- 담당자명
  `RECT_START_DTTM` DATETIME     NOT NULL, -- 채용시작일시
  `RECT_END_DTTM`   DATETIME     NOT NULL, -- 채용종료일시
  `LWST_SALARY_AMT` DECIMAL(20)  NOT NULL COMMENT '최저급여금액|최저 급여 금액(단위 : 원)||', -- 최저급여금액
  `BEST_SALARY_AMT` DECIMAL(20)  NOT NULL COMMENT '최고급여금액|최고 급여 금액(단위 : 원)||', -- 최고급여금액
  `CO_NO`           DECIMAL(9)   NOT NULL, -- 기업번호
  `REG_DTTM`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시|최초 등록일시를 기록한다.||', -- 등록일시
  `ALT_DTTM`        DATETIME     NULL     COMMENT '수정일시|수정일시를 기록한다.||' -- 수정일시
)
DEFAULT CHARACTER SET = 'utf8'
DEFAULT COLLATE = 'utf8_bin'
ENGINE = InnoDB
COMMENT 'ADS_채용|채용 정보||';
 
-- ADS_채용
ALTER TABLE `ADB_TEST`.`ADS_RECT`
  ADD CONSTRAINT `ADS_RECT_PK` -- ADS_채용 기본키
    PRIMARY KEY (
      `RECT_NO` -- 채용번호
    );
 
-- ADS_채용
ALTER TABLE `ADB_TEST`.`ADS_RECT`
  ADD CONSTRAINT `ADS_RECT_FK01` -- ADS_기업 -> ADS_채용
    FOREIGN KEY (
      `CO_NO` -- 기업번호
    )
    REFERENCES `ADB_TEST`.`ADS_CO` ( -- ADS_기업
      `CO_NO` -- 기업번호
    );

2. 인덱스 확인

select    * 
from      mysql.innodb_index_stats  a
where     a.database_name = 'ADB_TEST'
and       a.table_name = 'ADS_RECT'  ;

  - FK에 대해서 인덱스가 생성된 것을 확인

3. FK 컬럼으로 인덱스 생성

CREATE INDEX ADS_RECT_IX01 ON ADS_RECT(CO_NO);

4. 인덱스 확인

select    * 
from      mysql.innodb_index_stats  a
where     a.database_name = 'ADB_TEST'
and       a.table_name = 'ADS_RECT'  ;

  - FK 컬럼 그대로 인덱스를 생성하면 기존 FK 인덱스가 생성한 인덱스로 변경

5. 인덱스 삭제 시도

ALTER TABLE ADS_RECT DROP INDEX ADS_RECT_IX01;

에러 발생: Cannot drop index 'ADS_RECT_IX01': needed in a foreign key constraint

  - FK 인덱스 대신 사용하는 인덱스이기 때문에 삭제 불가

6. FK 삭제

ALTER TABLE ADS_RECT DROP FOREIGN KEY ADS_RECT_FK01;

7. 인덱스 확인

select    * 
from      mysql.innodb_index_stats  a
where     a.database_name = 'ADB_TEST'
and       a.table_name = 'ADS_RECT'  ;

  - FK 대신 사용하는 인덱스이더라도 FK삭제 시에 해당 인덱스는 삭제되지 않음.

8. 인덱스 삭제

ALTER TABLE ADS_RECT DROP INDEX ADS_RECT_IX01;

9. 인덱스 확인

select    * 
from      mysql.innodb_index_stats  a
where     a.database_name = 'ADB_TEST'
and       a.table_name = 'ADS_RECT'  ;

10. FK 생성

ALTER TABLE `ADB_TEST`.`ADS_RECT`
  ADD CONSTRAINT `ADS_RECT_FK01` -- ADS_기업 -> ADS_채용
    FOREIGN KEY (
      `CO_NO` -- 기업번호
    )
    REFERENCES `ADB_TEST`.`ADS_CO` ( -- ADS_기업
      `CO_NO` -- 기업번호
    );

  - 다시 FK를 생성

11. 인덱스 확인

select    * 
from      mysql.innodb_index_stats  a
where     a.database_name = 'ADB_TEST'
and       a.table_name = 'ADS_RECT'  ;

12. FK 삭제

ALTER TABLE ADS_RECT DROP FOREIGN KEY ADS_RECT_FK01;

  - FK 삭제

13. 인덱스 확인

select    * 
from      mysql.innodb_index_stats  a
where     a.database_name = 'ADB_TEST'
and       a.table_name = 'ADS_RECT'  ;

  - FK를 삭제하더라도 FK 인덱스는 삭제되지 않음.

14. FK 컬럼 변경

ALTER TABLE ADB_TEST.ADS_RECT ADD CO_NM VARCHAR(100);

CREATE UNIQUE INDEX ADS_CO_UK01 ON ADS_CO(CO_NM);

ALTER TABLE `ADB_TEST`.`ADS_RECT`
  ADD CONSTRAINT `ADS_RECT_FK01` -- ADS_기업 -> ADS_채용
    FOREIGN KEY (
      `CO_NM` 
    )
    REFERENCES `ADB_TEST`.`ADS_CO` ( -- ADS_기업
      `CO_NM` 
    );

에러 발생: Duplicate key name 'ADS_RECT_FK01'

  - FK가 존재하지 않아도 FK 인덱스가 존재하기 때문에 FK 생성 시 에러가 발생함.

결론: FK는 삭제 시에도 인덱스로 남아있기 때문에 필요한 인덱스라면 FK가 아닌 인덱스를 생성하는 것이 좋을 것 같음.