http://www.mysqlkorea.co.kr
한글매뉴얼 5.0 , 한글매뉴얼 5.1 , MySQL 5.1 HA , 사용자매뉴얼
Advanced Knowle...  
엔지니어 노트  
블로그존  
글로벌 MySQL  
MySQL 5.5 GA  
MySQL 5.6 Developer  
최신글
mysql 단편화에 …
mssql -> mysql이…
sql 쿼리문 질문…
mysql db 백업과 …
delete from~ 시 …
 
한글매뉴얼 5.0 > 매뉴얼존 > 한글매뉴얼 5.0
 

14.2.6.4. FOREIGN 제한

 

InnoDBFOREIGN 키제한 (constraint)도 지원한다. InnoDB에서의 foreign 키 제한 정의문 신텍스는 아래와 같은 형태가 된다:

 

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

    REFERENCES tbl_name (index_col_name, ...)

    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] 

 

Foreign 키 정의문은 아래의 조건에 달려 있다:

  • 양쪽의 테이블은 모두 InnoDB 테이블이어야 하고 TEMPORARY 테이블이 아니어야 한다.
  • 레퍼런스 테이블에서는, foreign 키 컬럼이 동일한 순서로 처음 컬럼에 리스트되어 있는 곳에 인덱스가 존재해야 한다. 그와 같은 인덱스가 존재하지 않으면, 레퍼런싱 테이블에 자동으로 생성이 된다.
  • 레퍼런스 테이블에서는, 레퍼런스 컬럼이 동일한 순서로 첫 번째 컬럼에 리스트되어 있는 곳에 인덱스가 존재해야 한다.
  • foreign 키 컬럼상의 인덱스 프리픽스는 지원되지 않는다. 이것의 한 가지 시퀀스는 BLOB TEXT 컬럼이 foreign 키에는 포함될 수 없다는 것이며, 그 이유는 이러한 컬럼에 있는 인덱스는 항상 프리픽스 길이를 포함해야 하기 때문이다.
  • 만일 CONSTRAINT symbol 구문이 주어진다면, symbol 값은 데이터 베이스안에서 유니크 해야 한다. 만일 그 구문이 주어지지 않는다면, InnoDB는 그 이름을 자동으로 생성한다.

InnoDB는 부모 테이블 (parent table) 안에 매칭되는 후보 키 값이 존재하지 않을 경우에는 차일드 테이블에서 foreign 키 값을 생성하고자 시도하는 모든 INSERT 또는 UPDATE 연산을 거부한다. 차일드 테이블에 매칭되는 열을 가지고 있는 부보 테이블에서 후보 키 값을 업데이트 또는 삭제하고자 시도하는 UPDATE 또는 DELETE 연산을 실행하는InnoDB의 동작은 FOREIGN 구문의 ON UPDATE ON DELETE 서브 구문을 사용해서 지정되는 referential action에 달려 있게 된다. 사용자가 부모 테이블에서 열을 업데이트 또는 삭제하고자 시도하고, 차일드 테이블에 매칭되는 열이 하나 또는 그 이상이 있는 경우, InnoDB는 실행되는 동작에 관련하여 5가지의 옵션을 지원한다:

  • CASCADE: 부모 테이블에서 열을 삭제 또는 업데이트하고 차일드 테이블에서 매칭 열을 자동으로 삭제 또는 업데이트 한다. ON DELETE CASCADE ON UPDATE CASCADE는 모두 지원된다. 두 테이블간에는, 부모 테이블 또는 차일드 테이블에 있는 동일한 컬럼에서 작용하는 ON UPDATE CASCADE 구문을 정의하지 말아야 한다.
  • SET NULL: 부모 테이블에서 열을 삭제 또는 업데이트하고 foreign 키 컬럼 또는 차일드 테이블에 있는 컬럼을 NULL로 설정한다. 이것은 foreign 키 컬럼이 NOT NULL 수식어를 가지고 있는 않을 경우에만 유효하다. ON DELETE SET NULL ON UPDATE SET NULL 구문은 모두 지원된다.
  • NO ACTION: 표준 SQL에서는, NO ACTION 은 말 그대로 프라이머리 키 값을 삭제 또는 업데이트하려는 시도가 레퍼런스 테이블에 관련된 foreign 키 값이 존재할 경우에는 허용되지 않는다는 것을 의미한다. InnoDB는 부모 테이블에 대한 삭제 또는 업데이트 연산을 거부한다.
  • RESTRICT: 부모 테이블에 대한 업데이트 또는 삭제 연산을 거부한다 (Reject). NO ACTION RESTRICTON DELETE 또는 ON UPDATE 구문을 생략한 것과 같은 것이다. (어떤 데이터베이스 시스템은 검사 연기 (deferred check)를 가지고 있는데, NO ACTION이 검사 연기이다. MySQL의 경우, FOREIGN 키 제한은 즉시 검사를 진행하기 때문에, NO ACTION RESTRICT는 동일한 것이 된다.)
  • SET DEFAULT: 이 동작은 파서에 의해 인식되지만, InnoDBON DELETE SET DEFAULT 또는 ON UPDATE SET DEFAULT 구문을 가지고 있는 테이블 정의문을 거부한다.

InnoDB는 테이블 안에 있는 foreign 키 레퍼런스를 지원한다는 것을 알아두자. 이와 같은 경우, “차일드 테이블 레코드 (child table records)”는 동일한 테이블에 있는 종속 레코드를 실제로 참조하게 된다.

 

InnoDB는 키 검사를 테이블 스캔 없이 빨리 진행할 수 있도록 하기 위해 키와 레퍼런스 키 상에 인덱스를 요구한다. foreign 키에 있는 인덱스는 자동으로 생성된다. 이것은 이전 버전의 것들과 대조를 이루는 것인데, 이전 버전에서는 인덱스라 명확하게 생성되어야 하거나 FOREIGN 키 제한 생성이 실패했었다.

 

foreign 키에 있는 상응 컬럼과 레퍼런스 키는 반드시 InnoDB 내부에 있는 데이터 타입과 유사한 타입을 가짐으로써 타입 변환 없이 비교를 할 수 있어야 한다. 정수 타입의 크기와 부호는 반드시 동일해야 한다. 스트링 타입의 길이는 같을 필요가 없다. 만일 여러분이 SET NULL 을 지정한다면, 차일드 테이블에서 컬럼을 NOT NULL 로 선언하지 않았다는 것을 확인하도록 한다.

 

만일 MySQLCREATE TABLE 명령문에서 에러 번호 1005를 생성하고, 그 에러 메시지가 에러 번호 150을 참조한다면, foreign 키 제한이 올바르게 형성되지 않아서 테이블 생성에 실패한 것이다. 비슷하게, 만일 ALTER TABLE이 실패하고 이것이 에러 번호 150을 참조한다면, 이것은 변경된 테이블 (altered table)에 대한 키 정의문이 올바르게 형성되지 않았음을 의미한다. 여러분은 서버에서 발생한 가장 최근의 InnoDB foreign 키 에러에 대한 자세한 설명을 SHOW ENGINE INNODB STATUS을 통해서 볼 수가 있을 것이다.

 

Note: InnoDBforeign 키 또는 NULL 컬럼을 가지고 있는 레퍼런스 키 값에서 FOREIGN 키 제한을 검사하지 않는다.

 

Note: 현재까지의 버전으로는, 트리거는 캐스캐이드 (cascaded) foreign 키 동작으로 활성화시킬 수가 없다.

 

Deviation from SQL standards: 만일 동일한 레퍼런스 키 값을 가지는 부모 테이블에 몇 개의 열이 존재한다면, InnoDB는 마치 동일한 키 값을 가지고 있는 부모 열이 존재하지 않는 것처럼 foreign 키 검사를 실행한다. 예를 들면, 만일 여러분이 RESTRICT 타입 제한을 정의하였고, 몇몇 부모 열을 가지는 차일드가 존재한다면, InnoDB는 이러한 부모 열 중의 어떠한 것도 삭제하지 못하도록 한다.

 

InnoDB는 깊이-우선 알고리즘 (depth-first algorithm)을 통해서 캐스캐이딩 연산을 실행하는데, 이 알고리즘은 FOREIGN 키 제한에 상응하는 인덱스에 있는 레코드를 기반으로 한다.

 

Deviation from SQL standards: -UNIQUE 키를 참조하는 FOREIGN 제한은 표준 SQL이 아니다. 이것은 표준 SQL에 대한 InnoDB의 확장 형태이다.

 

Deviation from SQL standards: 만일 ON UPDATE CASCADE 또는 ON UPDATE SET NULL가 캐스캐이드 동안 이미 업데이트된 동일 테이블에 다시 한번 업데이트를 한다면, 이것은 RESTRICT과 비슷한 동작을 하게 된다. 이것은 여러분이 셀프-레퍼렌셜 (self-referential) ON UPDATE CASCADE 또는 ON UPDATE SET NULL 연산을 사용할 수 없다는 것을 의미한다. 이것은 무한 루프 (infinite loop)가 캐스캐이딩 업데이트를 하지 못하도록 한다는 것을 의미한다. 반면에, 셀프-레퍼렌셜 ON DELETE SET NULL은 가능한데, 이것은 마치 셀프-레퍼렌셜 (self-referential) ON DELETE CASCADE와 같게 된다. 캐스캐이딩 연산은 15 레벨 깊이 이상은 네스티드 (nested) 되지 않는다.

 

Deviation from SQL standards: MySQL의 일반적 경우처럼, 많은 열을 삽입, 삭제, 업데이트 하는 표준 SQL 명령문에서는, InnoDBUNIQUE FOREIGN 키 제한을 열 별로 검사한다. 표준 SQL에 따르면, 디폴트 동작은 검사를 연기하는 것이다 (deferred checking). , 제한은 전체 SQL 명령문이 진행된 이후에만 검사가 이루어진다. InnoDB 구현이 제한 검사를 연기하지 않는 한, foreign 키를 통해서 자신을 참조하는 레코드를 삭제하는 것과 같은 일은 발생하지 않게 된다.

 

아래의 문장은 싱글-컬럼 foreign 키를 통해서 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; 

 

아래의 예문은 두 개의 다른 테이블에 대해서 foreign 키를 가지고 있는 product_order 에 대한 보다 복잡한 형태이다. 하나의 foreign 키는 product 테이블에 있는 이중-컬럼 인덱스 (two-column index)를 참조한다. 다른 것은 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),

                            FOREIGN KEY (product_category, product_id)

                              REFERENCES product(category, id)

                              ON UPDATE CASCADE ON DELETE RESTRICT,

                            INDEX (customer_id),

                            FOREIGN KEY (customer_id)

                              REFERENCES customer(id)) ENGINE=INNODB;

 

 

InnoDBALTER TABLE를 사용해서 새로운 foreign 키 제한을 테이블에 추가하는 것을 허용한다.

 

ALTER TABLE tbl_name

    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

    REFERENCES tbl_name (index_col_name, ...)

    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] 

 

Remember to create the required indexes first. 또한, ALTER TABLE를 사용하면 셀프-레퍼렌셜 foreign 키 제한 역시 테이블에 추가할 수가 있다.

 

InnoDB는 키를 드롭 (drop)시키기 위한 ALTER TABLE 사용 역시 지원한다:

 

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

 

만일 여러분이 foreign 키를 생성할 때 FOREIGN 구문에 CONSTRAINT 이름을 포함하게 되면, foreign 키를 드롭 시키기 위해 그 이름을 참조할 수 있게 된다. 그렇지 않으면, fk_symbol 값은 foreign 키가 생성될 때 InnoDB에 의해 내부적으로 생성된다. foreign 키를 드롭하고자 할 때 심볼 값을 알아보기 위해서는, 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`; 

 

여러분은 단일 ALTER TABLE 명령문의 서로 분리되어 있는 구문 안에 foreign 키를 추가하거나 드롭 시킬 수가 없다. 명령문을 서로 분리하는 것이 요구된다.

 

InnoDB 파서는 FOREIGN ... REFERENCES ... 구문에 있는 테이블 및 컬럼 구분자가 백틱 (backticks)안에 들어 가는 것을 허용한다. (다른 방법으로는, ANSI_QUOTES SQL 모드가 활성화 되어 있는 경우에는, 이중 인용 부호를 사용할 수 있다.) InnoDB 파서는 lower_case_table_names 시스템 변수 설정도 함께 고려한다.

 

InnoDBSHOW CREATE TABLE 명령문의 결과 중의 일부로 테이블의 foreign 키 정의문을 리턴한다:

 

SHOW CREATE TABLE tbl_name; 

 

Mysqldump는 덤프 파일에 대한 테이블의 올바른 정의문도 만들기도 하는데, 이것은 foreign 키에 대한 고려를 잊지 않는다.

 

여러분은 아래와 같이 테이블에 대한 FOREIGN 키 제한을 화면에 보일 수도 있다:

 

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

 

FOREIGN 키 제한은 결과 값의 Comment 컬럼에 리스트 된다.

 

foreign 키 검사를 실행할 때, InnoDB는 차일드 또는 검사를 하는 부모 레코드에 공유되는 하위-레벨 락을 설정한다. InnoDBFOREIGN 키 제한을 즉시 검사한다; 이 검사는 트랜젝션 실행을 기다리지 않는다.

 

foreign 키와 관련을 갖고 있는 테이블에 대해서 덤프 파일을 손쉽게 리로드하기 위해서는, mysqldumpFOREIGN_KEY_CHECKS 0으로 설정하기 위한 명령문을 덤프 결과에 자동으로 포함시킨다. 이렇게 하면 덤프가 리로드될 때 특정 순서로 리로드해야 하는 테이블에 대한 문제를 피할 수가 있다. 이 변수는 수동으로 설정할 수도 있다:

 

mysql> SET FOREIGN_KEY_CHECKS = 0;

mysql> SOURCE dump_file_name;

mysql> SET FOREIGN_KEY_CHECKS = 1;

 

이렇게 하면 덤프 파일이 foreign 키에 대해서 올바른 순서로 되어 있는 않는 테이블을 가지고 있을 경우에 어떤 순서로도 테이블을 임포트 (import) 시킬 수가 있게 된다. 또한, 임포트 연산의 속도를 증가 시킬 수도 있다. FOREIGN_KEY_CHECKS 0으로 설정하면 FOREIGN 키 제한을 무시하는 데에도 유용하게 된다. LOAD DATA  ALTER TABLE 연산을 하는 동안. 하지만, 비록 FOREIGN_KEY_CHECKS=0가 된다고 하더라도, InnoDB는 컬럼이 비-매칭 컬럼 타입을 참조하는 곳에서는 foreign 키 제한을 생성하는 것을 허용하지 않게 된다.

 

InnoDBSET FOREIGN_KEY_CHECKS=0로 설정되어 있지 않는 한, FOREIGN 제한에 의해 참조가 되는 테이블을 드롭하는 것을 허용하지 않는다. 여러분이 테이블을 드롭하는 경우, 자신의 생성 명령문에서 정의된 제한 값 역시 드롭된다.

 

만일 여러분이 드롭된 테이블을 재-생성한다면, 이것을 참조하는 FOREIGN 키 제한에 맞는 정의문을 가지고 있어야 한다. 이것은 올바른 컬럼 이름과 타입을 가지고 있어야 하며, 레퍼런스 키 상에서 인덱스를 가지고 있어야 한다. 만일 이러한 조건에 맞지 않는다면, MySQL은 에러 번호 1005를 리턴하고 에러 메시지 150을 참조하게 된다.

 

상위
14.2.6. InnoDB 테이블 생성 …
14.2.6.1. 서로 다른 API를 가…
14.2.6.2. MyISAM 테이블을 Inn…
14.2.6.3. AUTO_INCREMENT 컬럼…
14.2.6.4. FOREIGN 키 제한
14.2.6.5. InnoDB 와 MySQL 리…
MySQL Korea 사이트의 컨텐츠 소유권은 (주)상상이비즈에 있으므로 무단전재를 금합니다.
ⓒ 2010-2011 ssebiz All Rights Reserved.