trigger를 이용해 sqlite에서 foreign key를 사용하자!

원문 : http://nineye.com/blog/archives/444?replytocom=2006

---

sqlite에서 특정 column의 검색 속도를 향상 시키기 위해 foreign key를 사용하려고 했다.

이를 위해, sqlite 공식 홈페이지에서 foreign key에 관한 정보를 얻으려고 뒤져보다가… 컥.. 아래와 같은 내용을 발견했다.

 

FOREIGN KEY constraints

FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers.

 

즉, query문에서 foreign key에 대해 오류없이 파싱은 하지만 실제 foreign key의 기능은 지원해 주지 않는다는… 차라리 오류 메세지라도 발생시켜 줄 것이지… 아마 이 글을 읽지 않은 사람은 왜 foreign key가 제대로 동작하지 않는지 자기 소스를 열심히 쳐다보면서 삽질하겠지… ㅡㅡ;;;

어쨌든 trigger를 통해서 foreign key와 같은 동작을 할 수 있다고 편리하게 안내해 주네…ㅋ

sqlite의 foreign key와 관련해서 검색을 해보다가 이에 대한 이슈 중, 가장 과거의 글은 2003년에 작성된 글이었다. 5년이 지났는데도 왜 아직까지 foreign key를 지원해 주지 않는 건지 모르겠다. 물론 foreign key를 사용하면 전체적인 table관리의 성능에 악효과를 주긴 하지만 그건 trigger를 이용해도 마찬가지다…

어쨌든 이제부터 trigger를 이용해서 foreign key를 대체하는 방법에 대해서 설명한다.

일반적인 primary key를 가지는 column이 있는 table을 생성한다.

 

?

1	create table testtbl1 (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR(10), std_num INTEGER)

 

trigger에 사용 할 constraint를 가진 table을 생성한다.

 

?

1 2 3

create table testtbl2 ( id INTEGER PRIMARY KEY NOT NULL, tbl1_id INTEGER NOT NULL CONSTRAINT fk_tbl1_id REFERENCES testtbl1(id) ON DELETE CASCADE)

 

testtbl2에 insert 시, 수행해야 하는 trigger를 생성한다. 의미는, testtbl2 테이블에 레코드를 삽입하기 전에, 삽입할 레코드의 tbl1_id 필드의 값이 비어 있지 않은 지 체크하고, 또한 testtbl1테이블의 id필드의 값을 검색해서, tbl1_id 필드의 값과 같은 값이 있는 지 체크한다. 이 두가지 조건을 만족하지 않는다면 raise함수 내의 오류 메세지와 함께 rollback이 수행된다. 참고로 rollback이 수행되면 동일 transaction내의 명령들이 모두 함께 취소된다. 오류가 발생한 명령만 취소하려면 abort를 사용하자.

 

?

1 2 3 4 5 6

create trigger fk_tbl1_tbl2_i before insert on testtbl2 for each row begin select raise(rollback, 'insert on table "testtbl2" violates foreign key constraint "fk_tbl1_id"') where NEW.tbl1_id not null and (select id from testtbl1 where id = NEW.tbl1_id) is null; end;

 

testtbl2에 update 시, 수행해야 하는 trigger를 생성한다. 의미는, testtbl2 테이블의 특정 레코드를 update할 때, 새로 업데이트 되는 레코드의 tbl1_id 필드의 값과 동일한 id가 있는 지 testtbl1의 id를 검색해 보고, 없으면 raise함수 내의 오류 메세지와 함께 rollback이 수행된다.

 

?

1 2 3 4 5

create trigger fk_tbl1_tbl2_u before update on testtbl2 for each row begin select raise(rollback, 'update on table "testtbl2" violates foreign key constraint "fk_tbl1_id"') where (select id from testtbl1 where id = NEW.tbl1_id) is null; end;

 

testtbl2에 delete 시, 수행해야 하는 trigger를 생성한다. 의미는, testtbl1 테이블의 레코드를 삭제할 때, testtbl1 테이블의 id필드와 동일한 값을 가지는 testtbl2 테이블의 tbl1_id 필드가 있다면, 그 레코드도 함께 삭제한다.

 

?

1 2 3 4 5

create trigger fk_tbl1_tbl2_d before delete on testtbl1 for each row begin delete on testtbl2 where tbl1_id = OLD.id; end;

 

자, 이제 다 됐다. 이제 testtbl2에 insert, update 명령들을 실행 시, 항상 testtbl1을 체크하고, testtbl1에 delete 명령을 실행 시, 항상 testtbl2를 체크한다.

그리고 foreign key를 이용한 성능에 관련해서 한마디 하자면, 원래 처음 생각은 primary key의 레코드에 foreign key의 레코드들이 연결되어서 primary key의 레코드에 어떤 연산이 일어나면 foreign key의 레코드들에 영향을 준다고 생각했다. 따라서 primary key를 검색에 이용하면 손쉽게 primary key에 해당하는 foreign key의 레코드들을 찾아낼 수 있다고 생각했다. 즉, primary key의 검색은 빠르니, primary key column과 foreign key column을 join시키면 primary key가 아닌 foreign key의 레코드들도 빠르게 검색할 수 있다고 생각했다.

하지만 내 생각은 완전히 잘못된 것이었다. 대부분의 DBMS가 foreign key를 이용해서 select문의 검색 성능을 높이지는 않았다. 단, oracle은 foreign key를 이용하여 검색을 빠른 속도로 할 수 있다는 글은 있는데 그 글도 믿을 수 있는 것인지 잘 모르겠다.

왜 그럴까? 계속 고민해 봐도 도저히 이해할 수 없는 부분이다. 비록 foreign key의 주된 기능이 관계형 DB에서 table끼리의 연결된 데이터 유효성을 위한 제약이란 건 이해를 하지만, 이미 연결 시켜 놓은거 빠른 검색에 이용하게 해주면 얼마나 좋을까… 아마 수많은 연산을 지원해 주기 위해서 최적화 시켜야 하기 때문에 지원해 주지 못하니까 그럴것이다… 라고 추측은 하지만 어쨌든 맘에 안든다…ㅋ

이전 글의 sqlite테스트에서 보면 알겠지만 특이하게 중복 값을 허용하는 column에 대해서는 index를 생성하여도 검색 속도에 그리 크게 영향을 주지 않았다. index를 위해 거의 40%의 DB용량이 늘어났는데도… 원래 foreign key에 대해서 생각한 것도 index를 부여해도 속도가 빠르지 않아서 foreign key를 이용해볼까.. 했던 것이다.

이제 어떻게 해야 검색 속도를 증가시킬 수 있을까.. 중복 값을 허용하니 이넘을 primary key로 만들수도 없는 노릇이다… ㅡㅡ;;;

누구 좋은 방법 아는 사람 없소???