[PostgreSQL - DBA] FK Option

 

지금 프로젝트는 테스트 단계에 중도 투입된 프로젝트인데.. ERD가 없다..

그래서 DA#을 이용해서 리버스 모델링을 하려는데..  모든 테이블에 대해 FK가 하나도 없다..

흠.. 지금 같은 팀의 DA 사수님도 중도 투입이시라 왜 FK를 빼고 작업을 한 것인지.. 의도한 것인지.. 그걸 알 수가 없다..

물론 증권이나 은행권에 가끔 성능 관련 이슈로 FK를 제거하고 프로젝트를 하는 경우도 있다고 하는데.. RDB에서 R을 빼는게 맞는 건가 싶다..

사실 이러한 이슈로 혹시 추후에 FK를 추가할 경우가 생길 거 같은데.. 그때 쓸 만한 옵션이나 고려할 사항들을 정리해 보려 한다.

 

1. 뒤늦은 FK 생성이니(이미 데이터가 있음) 혹시 모를 무결성 오류를 무시하기 위해 NO VALID 옵션으로 FK 생성하기

ALTER TABLE child4 ADD CONSTRAINT fk_child4_01 FOREIGN KEY ("p_id") REFERENCES parent4("p_id") NOT VALID;

 

-- 참조 테이블 생성
CREATE TABLE parent4 (
  p_id varchar(255) NOT NULL,
  password varchar(255),
  CONSTRAINT "pk_parent4" PRIMARY KEY ("p_id")
);

-- 기본 테이블 생성
CREATE TABLE child4 (
  p_id varchar(255) NOT NULL,
  c_id varchar(255) NOT NULL,
  c_text varchar(255) NULL,
  CONSTRAINT "pk_child4" PRIMARY KEY ("p_id", "c_id")
);

ALTER TABLE child4 ADD CONSTRAINT fk_child4_01 FOREIGN KEY ("p_id") REFERENCES parent4("p_id") NOT VALID;

 

NOT VALID 옵션으로 FK를 생성하면, 기존의 데이터에 대해서는 FK 정합성 체크를 하지 않는다.

즉, 테스트 단계에서의 데이터를 가져갈 수 있다.

 

2. 테이블 간의 FK VALIDATION 검사를 트랜잭션 단위로 변경하기

이것도 다~~ 이미 FK 없이 개발 및 테스트 진행중이니 분명 선후행 관계를 신경쓰지 않고 DELETE 로직을 작성했을 가능성이 아주 크다.(부모 테이블을 먼저 삭제하는 등..)

이럴때를 위해 FK에 대한 VALIDATION  검사를 "트랜잭션" 단위로 묶을 수 있다.

ALTER TABLE child4 ADD CONSTRAINT fk_child4_01 FOREIGN KEY ("p_id") REFERENCES parent4("p_id") INITIALLY DEFERRED NOT VALID;

위 전체 SQL에서 해당 옵션은 아래와 같다.

INITIALLY DEFERRED

DEFERRED :
SET CONSTRAINTS는 현재 트랜잭션 내에서 제약 조건 검사 동작을 설정합니다. IMMEDIATE 제약 조건은 각 문의 끝에서 확인됩니다. DEFERRED 제약 조건은 트랜잭션이 커밋될 때까지 확인되지 않습니다. 각 제약조건에는 자체 IMMEDIATE 또는 DEFERRED 모드가 있습니다.
 
생성 시 제약 조건에는 DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE 또는 NOT DEFERRABLE의 세 가지 특성 중 하나가 제공됩니다. 세 번째 클래스는 항상 IMMEDIATE이며 SET CONSTRAINTS 명령의 영향을 받지 않습니다. 처음 두 클래스는 표시된 모드에서 모든 트랜잭션을 시작하지만 해당 동작은 SET CONSTRAINTS를 통해 트랜잭션 내에서 변경될 수 있습니다.

뭐.. 위 내용은 한번 읽어 보면 될 듯 하다.( SET CONSTRAINTS 같은 트랜잭션 범위 내에서 순간 적으로 조정 가능한 범위도 있나 보다.)

 

그러면 일단 commit 전까지는 잘~ insert, delete 뭐 잘 안쓰겠지만 update도 잘 되다가, commit 시에 참조키 제약 조건 오류가 발생한다.

다만....... 위 외의 경우는 해결할 방법이 없다.. 아니 그걸 해결 할거면 FK를 안넣는게 맞지..

예를들어..
1. 부모 테이블 먼저 delete 하고 다른 트랜잭션에서 자식 테이블을 삭제하려는 로직이 있는 application의 경우 오류가 나겠지..

2. 부모 테이블의 데이터가 삭제 되어도 자식 테이블의 데이터는 유지하려는 식의 로직이 있는(이럴리가 없을리 없다하기에는.. 모르는거다.. 여기 상황 보면..) 경우에는.. 답이없다. 해줄 방법이~..