MySQL Online DDL(7) [Foreign key Operations]

외래키 생성 및 삭제도 Online DDL을 지원한다.

지원되는 알고리즘은 다음과 같다.

 

이 때 이슈는 외래 키 생성 때 InPlace 함수는 사용하기 위해서는 foreign_key_checks 라는 변수가 0이어야 한다는 것이다.

foreign_key_checks는 외래키 생성 시 무결성을 체크할 것인지 여부이다.

다음과 같이 조회가 가능하다.

show global variables where Variable_name='foreign_key_checks';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| foreign_key_checks | ON    |

+--------------------+-------+

 

외래키 생성

다음과 같이 외래키가 설정될 Table의 데이터를 생성하고 외래키를 InPlace 알고리즘으로 생성한다.

외래 키 생성 시 유의점은 Mysql 외래키에 연결되는 Table에도 해당 컬럼에 Index가 필요하다

이 전 버전에서는 외래 키 생성 시 인덱스가 생성되었으나 8 버전에서는 다음과 같은 에러를

확인할 수 있다.

CREATE TABLE fk_test_tbl

(

  a int PRIMARY KEY

);

 

INSERT INTO fk_test_tbl (a) VALUES (0);

INSERT INTO fk_test_tbl (a) VALUES (1);

INSERT INTO fk_test_tbl (a) VALUES (2);

 

desc alter_test_tbl;

+-------+--------------+------+-----+---------+----------------+

| Field | Type         | Null | Key | Default | Extra          |

+-------+--------------+------+-----+---------+----------------+

| a     | int          | NO   | PRI | NULL    | auto_increment |

| b     | varchar(100) | YES  | MUL | NULL    |                |

| c     | varchar(100) | YES  |     | NULL    |                |

| d     | int          | NO   |     | 0       |                |

+-------+--------------+------+-----+---------+----------------+

 

SELECT d FROM alter_test_tbl GROUP BY d;

 

+---+

| d |

+---+

| 0 |

+---+

 

ALTER TABLE alter_test_tbl ADD CONSTRAINT fk_d FOREIGN KEY (d)

REFERENCES fk_test_tbl(a) ON UPDATE CASCADE;

ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'fk_d' in the referenced table 'fk_test_tbl'

 

위와 같은 에러가 발생되기 때문에 Index가 해당 컬럼에 존재하도록 해주어야 한다.

CREATE TABLE fk_test_tbl

(

  a int PRIMARY KEY

);

 

INSERT INTO fk_test_tbl (a) VALUES (0);

INSERT INTO fk_test_tbl (a) VALUES (1);

INSERT INTO fk_test_tbl (a) VALUES (2);

 

Index를 생성하였더라고 무결성 옵션이 켜져 있다면 InPlace 알고리즘 사용 시 오류를 발생 시킨다.

ALTER TABLE alter_test_tbl ADD CONSTRAINT fk_d FOREIGN KEY (d)

REFERENCES fk_test_tbl(a) ON UPDATE CASCADE,

ALGORITHM=INPLACE, LOCK=NONE;

 

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY.

 

따라서 COPY 알고리즘을 사용하거나 해당 변수를 변경해 주어야 한다.

SET SESSION foreign_key_checks = 0;

 

SHOW SESSION VARIABLES WHERE  variable_name='foreign_key_checks';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| foreign_key_checks | OFF   |

+--------------------+-------+

 

ALTER TABLE alter_test_tbl ADD CONSTRAINT fk_d FOREIGN KEY (d)

REFERENCES fk_test_tbl(a) ON UPDATE CASCADE,

ALGORITHM=INPLACE, LOCK=NONE;

 

외래키 삭제

다음과 같이 외래키가 설정될 Table의 데이터를 생성하고 외래키를 InPlace 알고리즘으로 생성한다.

ALTER TABLE alter_test_tbl DROP CONSTRAINT fk_d,

ALGORITHM=INPLACE, LOCK=NONE;

 

참조

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_foreign_key_checks

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html