MySQL FK on update, on delete

CREATE TABLE `tb_t1` (
	`id` INT(11) NOT NULL,
	`key` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
ENGINE=InnoDB;

CREATE TABLE `tb_t2` (
	`id` INT(11) NOT NULL,
	`some_value1` INT(11) NOT NULL,
	`some_value2` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	PRIMARY KEY (`id`) USING BTREE,
	CONSTRAINT `FK__tb_t1` FOREIGN KEY (`id`) REFERENCES `settlement`.`tb_t1` (`id`)
)
ENGINE=InnoDB;

위 쿼리의 테이블에 상위 테이블(T1 데이터를 지울 때 아래 쿼리 실행 시 아래 에러를 반환합니다.

DELETE t1, t2
FROM tb_t1 t1
INNER JOIN tb_t2 ON t1.id = t2.id
WHERE t2.key;

FK on update, on delete를 명시 하지 않을 경우 NO ACTION을 기본값으로 사용하게 되지만, mysql의 경우 RESTRICT으로 생성됩니다.

RESTRICT로 FK가 생성된 경우 상위 테이블에 대한 작업을 할 수 없어 FK on delete 옵션을 CASCADE로 변경해 사용하도록 합니다.

ALTER TABLE `tb_t2`
	DROP FOREIGN KEY `FK__tb_t1`;
ALTER TABLE `tb_t2`
	ADD CONSTRAINT `FK__tb_t1` FOREIGN KEY (`id`) REFERENCES `settlement`.`tb_t1` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;

reference option

RESTRICT : 상위 테이블에 대한 삭제 또는 업데이트 작업을 거부합니다.

 

NO ACTION : 표준 SQL의 키워드입니다. MySQ는 RESTRICT 사용.

MySQL은 참조 테이블에 관련 외래 키 값이 있는 경우 상위 테이블에 대한 삭제 또는 업데이트 작업을 거부합니다.

MySQL은 외래 키 제약 조건을 즉시 확인하므로 NO ACTION은 RESTRICT와 동일합니다.

일부 데이터베이스 시스템에는 지연 검사가 있으며 이는 NO ACTION지연 검사입니다. 

 

CASCADE : 상위 테이블에서 행을 삭제, 업데이트하고 하위 테이블에서 일치하는 행을 자동으로 삭제, 업데이트합니다. 

 

SET NULL : 상위 테이블에서 행을 삭제, 업데이트하고 하위 테이블의 외래 키 열을 NULL로 설정합니다.

 

SET DEFAULT : 이 작업은 MySQL 구문 분석기에 의해 인식되지만, InnoDB 와 NDB는 테이블 정의를 거부합니다.

 

 

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