[mysql] 외래 키 제약 조건 : ON UPDATE 및 ON DELETE 사용시기

데이터베이스에 제한을 두는 것을 망설이지 마십시오. 일관성있는 데이터베이스가 있어야하며 이것이 데이터베이스를 사용해야하는 좋은 이유 중 하나입니다. 특히 여러 응용 프로그램이 요청하는 경우 (또는 하나의 응용 프로그램이지만 직접 소스와 다른 소스를 사용하는 배치 모드).

MySQL을 사용하면 postgreSQL에서와 같이 고급 제약 조건이 없지만 적어도 외래 키 제약 조건은 상당히 고급입니다.

이 회사의 사람들을 포함하는 사용자 테이블이있는 회사 테이블을 예로 들어 보겠습니다.

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT,
     user_name VARCHAR(50),
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

ON UPDATE 절을 살펴 보자 .

• ON UPDATE RESTRICT : 기본값 : COMPANY 테이블에서 company_id를 업데이트하려고하면 한 명의 사용자가이 회사에서 최소한 링크하면 엔진이 조작을 거부합니다.
• 업데이트시 조치 없음 : RESTRICT와 동일합니다.
• ON UPDATE CASCADE : 가장 좋은 방법 : COMPANY 테이블 행에서 company_id를 업데이트하면 엔진은이 COMPANY를 참조하는 모든 USER 행에서 그에 따라 업데이트합니다 (그러나 USER 테이블에서 활성화 된 트리거는 없습니다). 엔진이 변경 사항을 추적합니다. 좋습니다.
• ON UPDATE SET NULL : COMPANY 테이블 행에서 company_id를 업데이트하면 엔진은 관련 USERs company_id를 NULL로 설정합니다 (USER company_id 필드에서 사용 가능해야 함). 업데이트와 관련하여 흥미로운 일을 볼 수는 없지만 잘못되었을 수 있습니다.

그리고 이제 ON DELETE 쪽에서 :

• ON DELETE RESTRICT : 기본값 : COMPANY 테이블에서 company_id ID를 삭제하려고하면 한 명의 사용자가이 회사의 링크를 하나 이상 연결하면 생명을 구할 수있는 경우 엔진이 작업을 거부합니다.
• 삭제 조치 없음 : RESTRICT와 동일
• ON DELETE CASCADE : 위험 : COMPANY 테이블에서 회사 행을 삭제하면 엔진과 관련 사용자가 삭제됩니다. 이것은 위험하지만 보조 테이블에서 자동 정리를 수행하는 데 사용할 수 있습니다 (따라서 원하는 것이 될 수는 있지만 COMPANY <-> USER 예에서는 확실히 아닙니다)
• ON DELETE SET NULL : handful : COMPANY 행을 삭제하면 관련 USER는 자동으로 NULL과 관계가 있습니다. Null이 회사가없는 사용자에게 귀하의 가치 인 경우, 이는 좋은 행동이 될 수 있습니다. 예를 들어, 일부 컨텐츠의 작성자로서 사용자를 애플리케이션에 유지해야하지만 회사를 제거하는 것은 문제가되지 않습니다.

일반적으로 내 기본값은 ON DELETE RESTRICT ON UPDATE CASCADE 입니다. 일부 ON DELETE CASCADE는 트랙 테이블 (모든 로그가 아닌 로그 등)과 ON DELETE SET NULL마스터 테이블이 USER 테이블의 JOB 테이블과 같이 외래 키를 포함하는 테이블의 ‘간단한 속성’인 경우입니다.

편집하다

내가 쓴지 오래되었습니다. 이제 중요한 경고 하나를 추가해야한다고 생각합니다. MySQL에는 캐스케이드와 관련하여 문서화 된 한 가지 큰 제한이 있습니다. 캐스케이드는 트리거를 발생시키지 않습니다 . 따라서 엔진을 사용하여 트리거를 사용할 정도로 확신이 많으면 계단식 제약 조건을 피해야합니다.

• http://dev.mysql.com/doc/refman/5.6/en/triggers.html

MySQL 트리거는 SQL 문으로 테이블을 변경 한 경우에만 활성화됩니다. 뷰의 변경이나 SQL 문을 MySQL 서버로 전송하지 않는 API에 의한 테이블 변경에 대해서는 활성화되지 않습니다.

• http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

==> 마지막 수정 사항을 아래에서보십시오.

외래 키 동작으로 트리거가 활성화되지 않습니다.

그리고 나는 이것이 언젠가 고쳐질 것이라고 생각하지 않습니다. 외래 키 제약 조건은 InnoDb 스토리지에서 관리하고 트리거는 MySQL SQL 엔진에서 관리합니다. 둘 다 분리되어 있습니다. Innodb는 제약 조건 관리 기능이있는 유일한 스토리지입니다. 언젠가 스토리지 엔진에 트리거를 직접 추가 할 수도 있습니다.

그러나 가난한 트리거 구현과 매우 유용한 외래 키 제약 조건 지원 중에서 어떤 요소를 선택해야하는지에 대한 의견이 있습니다. 데이터베이스 일관성에 익숙해지면 PostgreSQL을 좋아할 것입니다.

12 / 2017-MySQL에 대한이 편집 업데이트 :

의견에 @IstiaqueAhmed가 언급 했듯이이 주제에 대한 상황이 변경되었습니다. 따라서 링크를 따라 가서 실제 상황을 확인하십시오 (나중에 다시 변경 될 수 있음).

---

답변