mysql innodb

 Mysql에서 innodb 는 상용DB와 비슷한 기능들을 지원한다.
물론 아직도 지원하지 않는 기능이 많지만...  그럭저럭 쓸만하다.
다음은 그중 외래키 지원에 관한것이다.

외래키란 A, B 라는 테이블이 있을경우
A 테이블 Key 값을 참조하여 B 테이블에서 사용할때 B테이블의 Key를 외래키라고 한다.
참고로 A 테이블의 참조하는 키를 참조키라고 한다.
그러므로 A 테이블의 참조키와 B 테이블의 외래키값은 동일한 값이 들어간다.
그외 자세한 내용은 인터넷에 많으니 찾아보시고... ^^

그러나 Web 쪽에서 Mysql 이 많이 사용되다 보니 거의 외래키를 사용할일이 없고..
웹호스팅사에서도 거의 InnoDB는 잘 지원하지 않다 보니 자주 사용되지는 않는다.
Mysql 서버를 독자적으로 쓰거나.. 또는 업무용 프로그램의 DB로 사용한다면
외래키 및 트랜젝션 처리를 사용하게 될것이다.

그럼 왜 외래키를 이용할까?
그건 데이터의 정확성 때문이라고 할수 있다.
왜 정확해 지냐라고 묻는다면 아래의 예제를 설명하고 나서 이야기 하겠다.

우선 다음과 같은 테이블을 만들자.
member_info : 회원 테이블
member_login : 회원이 로그인하면 로그인시간을 기록하는 테이블

CREATE TABLE `member_info` (
    `id` VARCHAR(20) NOT NULL DEFAULT '',
    `name` VARCHAR(16) NOT NULL DEFAULT '',
    PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `member_login` (
    `id` varchar(20) NOT NULL,
    `logintime` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (`id`),
    CONSTRAINT `fk_id`
        FOREIGN KEY (`id`) REFERENCES `member_info` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

member_info 테이블은 기본적인 테이블 만드는 방법이다.
DDL(Create, Drop, Alter)문에 대한 설명은 여기서 하지 않겠다.

member_login 테이블이 실제 외래키를 담고 있는 테이블이다.
CONSTRAINT `fk_id` <== 제약조건을 건다는 말이며 그 이름을 fk_id 라고 지정했다
FOREIGN KEY (`id`) <== 현재테이블의 필드명이 id 인것이 외래키라는것을 의미한다.
REFERENCES `member_info` (`id`) <== member_info 테이블의 id 필드를 참조한다는 것을 의미한다.
ON DELETE CASCADE <== 참조키가 삭제되면 외래키도 삭제한다
ON UPDATE CASCADE <== 참조키가 업데이트 되면 외래키도 업데이트를 한다.

현재 테스트 버젼 5.0.37에서는 지원하는 옵션은 4가지가 있다.
CASCADE
  참조키와 동일하게 맞춘다. 즉 참조키값이 삭제되면 해당 테이블의 동일한 레코드도 삭제되며
  참조키값이 업데이트(예 aa => bb 로 바뀐다면)되면 외래키값도 업데이트(aa => bb 로 바뀐다)가 된다.
RESTRICT : 참조키가 삭제되거나 업데이트 되는걸 막는다.
SET NULL : 참조키가 삭제되거나 업데이트 되면 외래키값을 null 로만든다.
NO ACTION : 참조키가 삭제되거나 업데이트 되어도 아무런 동작을 하지 않는다. 이 경우는 외래키의 의미가 없다고 볼수 있다.

그럼 insert 문을 실제 데이터를 입력해 보자.

insert into member_info values('test1','테스터1');
insert into member_login values('test1',now());

위의 sql문을 순서를 바꾸어서 실행하면 에러가 날것이다.
회원정보 테이블(member_info)이 중심테이블이라고 한다면... member_login 테이블은
회원정보 테이블(member_info)에 종속적이라고 할수 있다.
즉 member_info 테이블의 id 값에 있는것이 member_login의 id값이 들어갈수 있는것이다.
그러니.. insert 문 작성시 member_info 부터 입력해야 한다.

현재 테이블 설정이 CASCADE 이니 조금전에 입력한 member_info의 id 값 'test1' 을 update 해보자.

update member_info set id='test2' where id='test1';

이제 member_login 의 데이터를 select 해서보면

select * from member_login 을 해보면..

member_login 을 업데이트를 하지 않았음에도 외래키값이 update 되어있는걸 확인할수 있을것이다.
그럼 삭제는 어떨까?
member_info 의 id='test2' 값을 다시 삭제하면 member_login의 row 데이터도 같이 삭제된것을
볼수 있다.

그럼 왜 외래키가 사용되는지 조금은 감이 오리라 생각된다.
참고로 member_login 테이블의 데이터가 삭제될때는 상관이 없다.
다만 member_login 의 데이터는 insert 또는 update 될때 member_info 의 id 필드를 참조하기때문에
영향을 받는다..

이와같은 방식으로
member_info 의 ID 값이 삭제나 업데이트 될때 제한을 둔다던지(restrict)
member_info 의 ID 값이 삭제나 업데이트 될때 member_info의 ID 값을 Null 을 만들던지(set null) 한다..
다만 no action 은 말 그대로 아무런 동작을 하지 않는것이기에 표면상의 외래키일 뿐이다.

참고로 Alter 문은 아래와 같이..
ALTER TABLE member_login Drop FOREIGN KEY fk_id;
ALTER TABLE member_login ADD CONSTRAINT `fk_id` FOREIGN KEY (`id`) REFERENCES `member_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

이 글은 스프링노트에서 작성되었습니다.

.