13.1.17.2 외래 키 제약 조건 사용

MySQL은 관련 데이터 테이블에 걸쳐 상호 참조를 가능하게하는 외부 키와이 분산 된 데이터의 무결성을 유지하는 데 도움 외래 키 제약 조건 을 지원합니다. CREATE TABLE 또는 ALTER TABLE 문에서 외래 키 제약 조건을 정의하기위한 기본적인 구문은 다음과 같습니다.

 [CONSTRAINT [ symbol ]] FOREIGN KEY
     [ index_name ( index_col_name , ...)
     REFERENCES tbl_name ( index_col_name , ...)
     [ON DELETE reference_option ]
     [ON UPDATE reference_option ]

 reference_option :
     RESTRICT | CASCADE | SET NULL | NO ACTION

index_name 은 외부 키 ID를 나타냅니다. 외부 키를 지원하는 자식 테이블에 명시 적으로 정의 된 인덱스가 이미 존재하는 경우 index_name 값은 무시됩니다. 그렇지 않으면, MySQL은 다음 규칙에 따라 이름이 붙은 외부 키의 인덱스를 암시 적으로 만듭니다.

외부 키 정의는 다음 조건에 따릅니다.

참조 액션

이 섹션에서는 외래 키가 참조 무결성 보장에 어떻게 도움이되는지에 대해 설명합니다.

외부 키를 지원하는 스토리지 엔진에서 상위 테이블에 일치하는 후보 키 값이 존재하지 않는 경우, MySQL은 자식 테이블에 외부 키 값을 만들려고하는 모든 INSERT 또는 UPDATE 작업을 거부합니다.

UPDATE 또는 DELETE 조작이 자식 테이블에 일치하는 행을 가진 부모 테이블의 키 값에 영향을 미칠 경우, 그 결과는 FOREIGN KEY 절의 ON UPDATE 및 ON DELETE 하위 절을 사용하여 지정된 참조 작업에 따라 다릅니다. MySQL은 수행되는 작업에 관련한 다음의 5 가지 옵션을 지원합니다.

지정되지 않은 ON DELETE 또는 ON UPDATE 에서는 기본 동작은 항상 RESTRICT 입니다.

MySQL은 하나의 테이블에있는 컬럼과 다른 컬럼 사이의 외래 키 참조를 지원하고 있습니다. (있는 컬럼이 자체에 대한 외래 키 참조를 가질 수 없습니다.)이 경우 "자식 테이블의 레코드 '는 실제로 같은 테이블에 의존 레코드를 참조합니다.

외부 키 어구의 예

단일 컬럼 외부 키를 사용하여 parent 와 child 테이블을 연결할 간단한 예를 보여줍니다.

 CREATE TABLE parent (
     id INT NOT NULL,
     PRIMARY KEY (id)
 ) ENGINE = INNODB;

 CREATE TABLE child (
     id INT, 
     parent_id INT,
     INDEX par_ind (parent_id)
     FOREIGN KEY (parent_id) 
         REFERENCES parent (id)
         ON DELETE CASCADE
 ) ENGINE = INNODB;

product_order 테이블에 다른 두 테이블에 외부 키가 더 복잡한 예. 1 개의 외부 키가 product 테이블의 두 컬럼의 인덱스를 참조합니다. 다른 외부 키는 customer 테이블의 단일 컬럼 인덱스를 참조합니다.

 CREATE TABLE product (
     category INT NOT NULL, id INT NOT NULL,
     price DECIMAL,
     PRIMARY KEY (category, id)
 ) ENGINE = INNODB;

 CREATE TABLE customer (
     id INT NOT NULL,
     PRIMARY KEY (id)
 ) ENGINE = INNODB;

 CREATE TABLE product_order (
     no INT NOT NULL AUTO_INCREMENT,
     product_category INT NOT NULL,
     product_id INT NOT NULL,
     customer_id INT NOT NULL,

     PRIMARY KEY (no)
     INDEX (product_category, product_id)
     INDEX (customer_id)

     FOREIGN KEY (product_category, product_id)
       REFERENCES product (category, id)
       ON UPDATE CASCADE ON DELETE RESTRICT,

     FOREIGN KEY (customer_id)
       REFERENCES customer (id)
 ) ENGINE = INNODB;

외부 키 추가

ALTER TABLE 을 사용하여 기존 테이블에 새로운 외래 키 제약 조건을 추가 할 수 있습니다. 이 문 외부 키에 관련한 구문을 보여줍니다.

 ALTER TABLE tbl_name
     ADD [CONSTRAINT [ symbol ]] FOREIGN KEY
     [ index_name ( index_col_name , ...)
     REFERENCES tbl_name ( index_col_name , ...)
     [ON DELETE reference_option ]
     [ON UPDATE reference_option ]

외래 키는 자체 참조하는 (같은 테이블을 참조) 할 수 있습니다. ALTER TABLE 을 사용하여 테이블에 외래 키 제약 조건을 추가하려면 먼저 필요한 인덱스를 작성하는 것을 잊지 마십시오.

외부 키 삭제

다음 구문을 사용하여 ALTER TABLE 에서 외부 키를 제거 할 수 있습니다.

 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol ;

외부 키를 생성 할 때 FOREIGN KEY 절에 CONSTRAINT 이름이 포함 된 경우 해당 이름을 참조하여 외부 키를 삭제할 수 있습니다. 그렇지 않으면 외부 키가 생성 될 때 fk_symbol 값이 내부적으로 생성됩니다. 외부 키를 제거 할 때 심볼 값을 찾으려면 다음과 같이 SHOW CREATE TABLE 문을 사용합니다.

 mysql> SHOW CREATE TABLE ibtest11c\G
 *************************** 1. row ******************** *******
        Table : ibtest11c
 Create Table : CREATE TABLE`ibtest11c` (
   `A` int (11) NOT NULL auto_increment,
   `D` int (11) NOT NULL default '0'
   `B` varchar (200) NOT NULL default '',
   `C` varchar (175) default NULL,
   PRIMARY KEY (`A`,`D`,`B`)
   KEY`B` (`B`,`C`)
   KEY`C` (`C`)
   CONSTRAINT`0_38775` FOREIGN KEY (`A`,`D`)
 REFERENCES`ibtest11a` (`A`,`D`)
 ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT`0_38776` FOREIGN KEY (`B`,`C`)
 REFERENCES`ibtest11a` (`B`,`C`)
 ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE = INNODB CHARSET = latin1
 1 row in set (0.01 sec)

 mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;

MySQL 5.6.6 이전에는 동일한 ALTER TABLE 문에서 외부 키의 추가 및 삭제는 문제가 발생할 수 있기 때문에 지원되지 않습니다. 작업에 대해 별도의 문을 사용하도록하십시오. MySQL 5.6.6의 시점에서는 동일한 ALTER TABLE 문에서 외부 키의 추가 및 삭제는 ALTER TABLE ... ALGORITHM=INPLACE 에서 지원되지만 ALTER TABLE ... ALGORITHM=COPY 에서는 지원되지 않는 상태입니다.

MySQL 5.6.7 이전 버전에서는 ALTER TABLE 을 사용하여 foreign 키 컬럼의 정의를 변경하면 참조 무결성이 손실 될 수있었습니다. 예를 들어, NULL 값을 포함한 외부 키 컬럼을 NOT NULL 이되도록 변경하면 NULL 값이 빈 문자열이되었습니다. 마찬가지로 부모 테이블에서 행을 삭제하는 ALTER TABLE IGNORE 의해 참조 무결성이 손상 될 수있었습니다.

5.6.7의 시점에서는 참조 무​​결성이 손실 될 수있는 foreign 키 컬럼에 변경이 서버에 의해 금지되어 있습니다. 해결 방법으로 컬럼 정의를 변경하기 전에 ALTER TABLE ... DROP FOREIGN KEY 를 사용하고 나중에 ALTER TABLE ... ADD FOREIGN KEY 를 사용합니다.

외래 키 및 기타 MySQL 문

FOREIGN KEY ... REFERENCES ... 어구의 테이블과 컬럼 식별자는 역 따옴표 ( ` )로 묶을 수 있습니다. 또는 ANSI_QUOTES SQL 모드가 활성화되어있는 경우 큰 따옴표 ( " )를 사용할 수 있습니다. 또한 lower_case_table_names 시스템 변수의 설정도 고려합니다.

SHOW CREATE TABLE 문 출력의 일부로 자식 테이블의 외부 키 정의를 볼 수 있습니다.

 SHOW CREATE TABLE tbl_name ;

INFORMATION_SCHEMA.KEY_COLUMN_USAGE 테이블을 쿼리하여 외부 키에 대한 정보를 얻을 수 있습니다.

INNODB_SYS_FOREIGN 및 INNODB_SYS_FOREIGN_COLS 테이블, 심지어 INFORMATION_SCHEMA 데이터베이스의 InnoDB 테이블에서 사용되는 외부 키에 대한 정보를 검색 할 수 있습니다.

mysqldump는 테이블의 올바른 정의 (자식 테이블에 외래 키 포함)를 덤프 파일에 생성합니다.

외부 키 관계를 가진 테이블의 덤프 파일의 장전을 쉽게하기 위해 mysqldump는 foreign_key_checks 를 0으로 설정하면 문을 덤프에 자동으로 포함됩니다. 이렇게하면 덤프가 리로드 될 때 특정 순서로 리로드해야하는 테이블에 대한 문제가 해결됩니다. 또한이 변수를 수동으로 설정할 수도 있습니다.

 mysql> SET foreign_key_checks = 0;
 mysql> SOURCE dump_file_name ;
 mysql> SET foreign_key_checks = 1;

이로 인해 외부 키에 대해 제대로 정렬되지 않은 테이블이 덤프 파일에 포함되어있는 경우에도 해당 테이블을 임의의 순서로 가져올 수 있습니다. 또한 가져 오기 작업 속도가 빨라집니다. foreign_key_checks 를 0으로 설정하는 것은 LOAD DATA 및 ALTER TABLE 작업 동안 외래 키 제약 조건을 무시하는 데에도 도움이 될 수 있습니다. 그러나 foreign_key_checks = 0 의 경우 MySQL은 컬럼이 일치하지 않는 컬럼 형을 참조하는 외래 키 제약 조건의 작성은 허용되지 않습니다. 또한 테이블에 외래 키 제약 조건이 존재하는 경우, ALTER TABLE 을 사용하여 테이블을 다른 스토리지 엔진을 사용하도록 변경할 수 없습니다. 스토리지 엔진을 변경하려면 먼저 외래 키 제약 조건을 모두 제거해야합니다.

SET foreign_key_checks = 0 을 실행하지 않는 한 FOREIGN KEY 제약 조건에 의해 참조되는 테이블에 대해 DROP TABLE 을 발행 할 수 없습니다. 테이블을 삭제하면 테이블을 만드는 데 사용 된 문에 정의 된 제약도 모두 삭제됩니다.

삭제 된 테이블을 다시 만들 경우 해당 테이블에 그것을 참조하는 외래 키 제약 조건을 준수하는 정의가 존재해야합니다. 또한 컬럼의 올바른 이름과 형태 및 앞서 언급 한 참조되는 키에 대한 인덱스가 존재해야합니다. 이러한이 충족되지 않은 경우, MySQL은 오류 1005를 반환하고 오류 메시지에서 오류 150을 나타냅니다. 이것은 외부 키 제약이 올바르게 형성되지 않았 음을 나타냅니다. 마찬가지로, ALTER TABLE 오류 150에 실패했을 경우, 이것은 변경된 테이블에 대한 외부 키 정의가 잘못 형성되는 것을 나타냅니다.

InnoDB 테이블의 경우, SHOW ENGINE INNODB STATUS 의 출력을 체크하여 MySQL Server에서 최신 InnoDB 외부 키 오류에 대한 자세한 설명을 얻을 수 있습니다.