외래키(FK)에 부모에 없는 값이 들어갔어요☠️

나만의 기록을 위한 예제와 포스팅입니다!

결론

복합키를 참조하는 외래키에 NULL이 있다면 부모에 없는 값이 들어갈 수 있다.

환경

• DBMS version
  • PostgreSQL 13.7 on x86_64-pc-linux-gnu
  • Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)

참고 사항 : 아래 테스트는 PostgreSQL 기반으로 진행되었으며 다른 DBMS로는 MSSQL로 테스트를 진행하였습니다.

들어가기 전에

데이터를 확인하다 보니 기본키를 참조하는 외래키에 부모에 없는 값이 들어가 있다.
내 부족한 검색 능력으로는 관련된 글을 쉽게 찾을 수 없기 때문에 기록을 위해 적어본다.

상황

우선 기본키(PK)는 2개 이상의 컬럼을 갖는 복합키이며, 이를 참조하는 테이블에 부모에 없는 값이 들어갔다.
간단하게 말하자면 부모 테이블은 두 컬럼만 가지며 그 두 컬럼이 기본키로 되어 있고 (1, 'a') 라는 1개 로우만 가지고 있을 때, 자식의 외래키는 (null, '가나다') 이런 데이터가 있었다.

데이터를 본 순간 나는 아래와 같은 생각을 하게 됐다.

1. 왜 부모에 없는 값이 들어갔지?
2. 아니 애초에 참조된 부모 컬럼은 PK였고 PK는 NULL이 못 들어가잖아!?
3. DBMS에 이런 버그가 있다고? 에이 내 환경이 문제겠지..........
4. 뭐야 관련된 글도 별로 없어! 나 DBMS에 대한 버그 찾았다!!

[4]번은 조금 오버했지만, 저런 생각을 0.01초는 했다...

간단한 ERD와 상황을 재연해 보자!
아래는 학번과 강좌코드를 기본키로 갖는 수강 테이블과 이를 FK로 가지면서 nullable한 출석 테이블이 있다.
( 컬럼의 옵션이 nullable한 게 주요 옵션이며 포인트다.)

• 수강 테이블에 1개의 로우가 존재한다.
  
  
• 그렇다면 출석 테이블에 들어갈 수 있는 값들은?
  

부모에는 (1, A01) 값만 들어갔는데, 이를 참조하는 컬럼에서 null과 말도 안 되는
아래 참고 코드를 보면 알겠지만 외래키 제약조건을 걸었음에도 부모에 없는 값이 들어갔다(?)

부모를 참조하는 두 컬럼에 값이 존재하면서, 부모에 없는 값을 삽입할 때 FK 무결성 에러 발생(당연한 소리?)하는 경우를 제외하고는 한 컬럼에 NULL 있는 경우 FK 무결성 검증을 무시한다.

참고 쿼리

-- PostgreSQL 13.7
-- 수강 테이블
CREATE TABLE enrollment
(
  student_id Integer NOT NULL,
  class_code Character varying(5) NOT NULL
);
ALTER TABLE enrollment ADD CONSTRAINT pk_enrollment PRIMARY KEY (student_id,class_code);

-- 출석 테이블
CREATE TABLE attendance
(
  num Integer NOT NULL,
  student_id Integer,
  class_code Character varying(5),
  enter_time Timestamp(3) NOT NULL
);
ALTER TABLE attendance ADD CONSTRAINT pk_attendance PRIMARY KEY (num);

-- FK 생성
ALTER TABLE attendance ADD CONSTRAINT enrollment_atendance FOREIGN KEY (student_id, class_code) REFERENCES enrollment (student_id, class_code) ON DELETE CASCADE ON UPDATE NO ACTION;

-- INSERT DATA
INSERT INTO enrollment(student_id, class_code) VALUES (1, 'A01');
INSERT INTO attendance(num, student_id, class_code, enter_time) VALUES 
(1, 1, 'A01', '2024-02-15 13:00:00') 
, (2, 1, NULL, '2024-02-15 14:00:00') 
, (3, NULL, 'A01', '2024-02-15 16:00:00') 
, (4, NULL, NULL, '2024-02-15 17:00:00') 
, (5, NULL, '가나다', '2024-02-15 18:00:00') 
, (6, 999, null, '2024-02-15 19:00:00');

원인 및 해결

원인

우선 왜 이렇게 만들었는지에 대한 속 시원한 글은 찾지 못했다.
그래서 내 결론은 그냥 이렇게 만들었구나 싶다...

해결

PostgreSQL에 한해서는 FK를 만들 때 옵션을 줄 수 있다.

• MATCH SIMPLE (default)
• MATCH PARTIAL (아직 구현되지 않음)
• MATCH FULL

A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL constraints can be applied to the referencing column(s) to prevent these cases from arising.)
PostgreSQL FK 관련 도큐먼트

MATCH FULL 옵션을 사용하면

• 부모를 참조하는 두 컬럼에 값이 존재하면서 부모에 없는 값을 삽입할 때 FK 제약조건 위배 발생(당연한 소리)
• 컬럼 하나만 NULL인 경우 FK 제약조건 위배 발생
  • SQL Error [23503]: 오류: "attendance" 테이블에서 자료 추가, 갱신 작업이 "enrollment_atendance" 참조키(foreign key) 제약 조건을 위배했습니다
    Detail: MATCH FULL에 null 키 값과 nonnull 키 값을 함께 사용할 수 없습니다.

하지만 MSSQL에서는 관련 옵션이 없었다. 관련된 부분에 대한 도큐먼트는 아래와 같다.
null 옵션을 가진 컬럼에 대한 해결방법을 찾고 싶었지만 없었으며, MSSQL에서는 관련 컬럼을 NOT NULL로 지정해서 해결한다.

FOREIGN KEY 제약 조건의 열에 NULL 외의 다른 값을 입력한 경우에는 그 값이 참조되는 열에 있어야 합니다. 그렇지 않으면 외래 키 위반 오류 메시지가 반환됩니다. 복합 외래 키 제약 조건의 모든 값을 확인하려면 참여하는 모든 열에 NOT NULL을 지정합니다.
MSSQL FK 관련 도큐먼트

추가로

그렇다면 MATCH SIMPLE 옵션을 가진 FK에 CASCADE를 걸고 삭제를 하면 데이터는 어떻게 될까?
무언가 당연하겠지만 결과는, 모두 값이 있는 num 1번만 삭제되었다.

마무리

물론 해당 경우가 나오도록 설계하는 케이스는 많이 없을 거라 생각한다. 공부가 부족해 위와 같은 현상을 겪으면서 당황했으며 관련 글도 쉽게 찾을 수 없어 놀랐다.(찾으면서 이상한 댓글도 많이 봤다.. 그래서 DBMS의 오류(?) 이딴 생각을 하게 됐...)
또, MATCH PARTIAL 옵션에 대해서는 인덱스를 탐색하여 검증하는 방식이 두 번째 컬럼부터는 비효율적이어서 아직 구현되지 않은 걸까? 라는 짧은 생각을 해본다.

원인에 대해서는 관련 글이나 설명이 있다면 알려주세요.....

참고

PostgreSQL FK 관련 도큐먼트
MSSQL FK 도큐먼트