Effective-SQL Better-Way-6 : 참조 무결성을 보호하려면 외래키를 정의하자

좋은 데이터베이스 스키마를 설계하려면 연관된 테이블의 기본키 값을 가지고 있도록 테이블의 외래키를 정의하는 것이 좋습니다. 예를 들어 Orders 테이블은 Customers 테이블의 기본키를 가리키는 customer_id나 customer_sequence 컬럼을 정의해 주문 고객 정보를 식별할 수 있게 해야 합니다.

다음은 카페와 메뉴간의 관계도를 다이어그램으로 간단하게 보여주는 그림입니다.

• Menus - Cafes 관계 다이어그램

위 그림에서 노란색 키는 기본키, 파란색 키는 외래키를 나타내며 화살표 방향을 통해 부자 관계를 알 수 있습니다. ( 다이어그램 사진의 일부분이라 테이블이 몇 개 없습니다. 참고해주세요!)

관계를 설명하면 다음과 같습니다.

1. 카페 : 카테고리 -> 1 : 다
2. 카테고리 : 메뉴 -> 1 : 다
3. 카페 : 카페 파일 url 저장소 -> 1 : 다
4. 메뉴 : 메뉴 파일 url 저장소 -> 1 : 다

왜 관계를 정의 하는 걸까?

선언적 참조 무결성을 정의했기 때문에 데이터베이스 시스템은 테이블 간의 관계를 알고 있습니다. 근데 왜 이런 정의를 해야 할까요? 이렇게 관계를 정의하는 이유는 두 가지의 목적이 있습니다.

1. 데이터베이스에서 새로운 뷰나 저장 프로시저를 생성할 때 쿼리 디자이너가 JOIN 절을 올바르게 만들 수 있도록 도와준다.
2. 일대다 관계에서 테이블의 데이터를 입력하거나 변경, 삭제할 때 데이터베이스 시스템이 데이터 무결성을 강화하는 데 도움 준다.

이 중 두 번째 목적이 더 중요합니다. 예를 들어 메뉴 테이블에 빠지거나 엉뚱한 메뉴를 입력하지 않도록 해야 하기 때문입니다. 만약 cafe_id 값을 변경한다면 변경된 값을 메뉴 테이블 관련된 로우에도 모두 전파되어야 합니다. 삭제도 물론 마찬가지입니다.

따라서 데이터베이스 시스템에서 이런 주요 기능을 사용하려면 다 관계에 있는 테이블을 생성할 때 CREATE TABLE 구문이나 ALTER TABLE 구문으로 FOREIGN KEY 제약 조건을 추가해야 합니다. 다음 쿼리 문을 보겠습니다.

• Cafe 테이블

CREATE TABLE Cafe {
    cafe_sequence int NOT NULL PRIMARY KEY,
    create_at date NOT NULL,
    ...
};

• Menu 테이블

CREATE TABLE Menu {
    menu_sequence int NOT NULL PRIMARY KEY,
    ...
    cafe_sequence int NOT NULL DEFAULT 0,
    ...
};

ALTER TABLE Menu
    ADD CONSTRAINT Menu_FK99
        FOREIGN KEY (cafe_sequence)
            REFERENCE Cafe (cafe_id);

이렇게 선언하는 순간 Menu 테이블에 입력된 데이터가 참조 무결성을 위반할 때, Menu 테이블의 ALTER TABLE 구문은 실패할 것입니다. 물론 일부 데이터베이스 시스템에서는 성공 할 수 있지만, 데이터베이스 옵티마이저가 이 제약 조건을 신뢰할 수 없다고 판단해 더는 사용하지 않을 것입니다.

하지만 단순히 제약조건을 정의한다고 해서 이전에 입력된 데이터에 대한 참조 무결성이 꼭 보장된다고 장담할 수 없습니다.

이와 같은 문제가 있어 정답은 아니지만, 저 같은 경우 이미그레이션 작업을 통해 해결합니다. 일부 데이터베이스 시스템에서는 참조 무결성 제약 조건을 정의하면 자동으로 외래키 컬럼에 인덱스를 만드므로, 조인을 수행할 때 성능 향상 효과를 볼 수 있습니다.

정리하자면...

결론적으로 명시적으로 외래키를 만들면 부모 테이블에 없는 로우를 가리키는 자식 테이블 로우가 없음을 보장할 수 있음으로 관련된 테이블 간에 데이터 무결성을 확인하는 데 좋습니다. 또한 이미 데이터가 있는 테이블에 외래키 제약 조건을 추가할 때 이 제약 조건을 위반하는 데이터가 있다면 제약 조건은 실패할 것입니다. 즉 참조 무결성을 보호하려면 외래키를 써야 합니다.

다음에는 테이블 간 관계를 명확히 하자에 관해 공부해 보겠습니다.