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

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

---

MySQL Workbench를 사용하여 데이터베이스 스키마를 설계하고 있습니다. 다이어그램을 수행하고 변환 할 수 있기 때문에 매우 좋습니다 .P

어쨌든, 나는 외래 키 지원 때문에 InnoDB를 사용하기로 결정했습니다. 그래도 한 가지 주목할 점은 외래 키에 대해 업데이트시 및 삭제 옵션을 설정할 수 있다는 것입니다. 간단한 예에서 "제한", "캐스케이드"및 널 설정 위치를 설명 할 수 있습니까?

예를 들어, user을 포함하는 테이블이 있다고 가정 해보십시오 userID. 그리고 message2 개의 외래 키 ( 테이블 userID에서 동일한 기본 키를 참조)가있는 다 대다 메시지 테이블 이 있다고 가정 해보십시오 user. 이 경우 업데이트시 및 삭제시 옵션을 설정하는 것이 유용한가요? 그렇다면 어느 것을 선택합니까? 이것이 좋은 예가 아니라면, 이것이 어떻게 유용 할 수 있는지 보여주는 좋은 예를 제시해 주시겠습니까?

감사

---

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

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가 언급 했듯이이 주제에 대한 상황이 변경되었습니다. 따라서 링크를 따라 가서 실제 상황을 확인하십시오 (나중에 다시 변경 될 수 있음).

---

Addition to @MarkR answer - one thing to note would be that many PHP frameworks with ORMs would not recognize or use advanced DB setup (foreign keys, cascading delete, unique constraints), and this may result in unexpected behaviour.

For example if you delete a record using ORM, and your DELETE CASCADE will delete records in related tables, ORM's attempt to delete these related records (often automatic) will result in error.

---

You'll need to consider this in context of the application. In general, you should design an application, not a database (the database simply being part of the application).

Consider how your application should respond to various cases.

The default action is to restrict (i.e. not permit) the operation, which is normally what you want as it prevents stupid programming errors. However, on DELETE CASCADE can also be useful. It really depends on your application and how you intend to delete particular objects.

Personally, I'd use InnoDB because it doesn't trash your data (c.f. MyISAM, which does), rather than because it has FK constraints.

참고URL : https://stackoverflow.com/questions/6720050/foreign-key-constraints-when-to-use-on-update-and-on-delete