데이터 무결성 체크

오늘은 foreign key가 어떻게 데이터 무결성을 지키고, 불일치하거나 잘못된 데이터와 관련된 흔한 문제를 막아주는지 알아볼 거야.

먼저, 우리가 "데이터 무결성"이라고 할 때 뭘 의미하는지부터 짚고 가자. 예를 들어, 주문(orders) 테이블이랑 고객(customers) 테이블이 있다고 해보자. 만약 주문에 있는 고객이 실제 고객 테이블에 없다면, 이건 무결성 위반이야. 연관된 테이블의 데이터가 논리적으로 항상 맞아떨어져야 해.

데이터 무결성은 이런 걸 의미해:

• 아무 "빈" 참조도 없음: 다른 테이블의 뭔가를 참조한다면, 그 "뭔가"는 항상 존재해야 해.
• 수정 에러에 강함: 만약 다른 레코드가 참조하는 값을 테이블에서 지우려고 하면, DB가 우리한테 경고를 주거나 상황을 제대로 처리해줘야 해.

이런 걸 위해 PostgreSQL에서는 foreign key를 쓰는 거지.

foreign key가 데이터 무결성을 어떻게 보장할까?

테이블에 foreign key를 만들면, PostgreSQL이 자동으로 이런 걸 체크해:

1. 부모 테이블에 데이터가 있는지 확인. 레코드를 insert하거나 update하기 전에, PostgreSQL은 foreign key가 참조하는 값이 연결된 테이블에 실제로 있는지 확인해.
2. 데이터 삭제나 변경. 부모 테이블에서 레코드를 삭제하거나 바꾸기 전에, 그 레코드를 자식 테이블에서 참조하고 있는지 체크해.

foreign key는 일종의 "가드" 같은 거야. 잘못된 데이터가 들어오는 걸 막아주고, 테이블들이 정해진 규칙 안에서만 상호작용하게 해줘.

예시: 학생과 강의 테이블에서의 데이터 무결성

예를 들어, students랑 courses라는 두 테이블이 있다고 해보자. 각 학생은 여러 강의에 등록할 수 있어. 이 관계를 표현하려면 enrollments 테이블을 써. 누군가 존재하지 않는 강의에 학생을 등록시키려고 할 때를 생각해보자.

1단계. 세 개의 연관된 테이블을 만들어보자:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id)
);

여기서:

• enrollments 테이블에서 student_id와 course_id foreign key를 명시적으로 지정해서, 각각 students와 courses의 primary key를 참조하게 했어.

흔한 데이터 무결성 체크

1. 데이터 insert할 때 체크

enrollments 테이블에 없는 student_id나 course_id로 insert하려고 하면 에러가 나.

예시:

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- 에러! ID가 999인 학생은 없어.

에러 메시지:

ERROR:  insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"
DETAIL:  Key (student_id)=(999) is not present in table "students".

2. 데이터 삭제할 때 체크

부모 테이블에서 참조되는 레코드를 삭제해보자.

예시:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO courses (title) VALUES ('Mathematics');

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1); -- 성공적으로 insert

DELETE FROM students WHERE student_id = 1; -- 에러! 학생이 아직 강의에 등록되어 있어!

에러 메시지:

ERROR:  update or delete on table "students" violates foreign key constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

이런 경우에는 CASCADE, SET NULL, RESTRICT 같은 전략을 써서 제대로 삭제할 수 있어. 이건 전에 얘기했었지.

foreign key로 무결성 체크하는 예시들

예시 1: 잘못된 데이터 자동 차단

foreign key 덕분에 PostgreSQL이 "없는" 데이터를 insert하는 걸 자동으로 막아줘:

-- 없는 학생을 강의에 등록해보자:
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- 에러! ID가 42인 학생은 없어.

이렇게 하면 students 테이블에 없는 학생은 강의에 등록할 수 없어.

예시 2: ON DELETE CASCADE로 데이터 삭제

foreign key에 ON DELETE CASCADE 옵션을 주면, 부모 테이블에서 레코드를 삭제할 때 자식 테이블의 관련 데이터도 같이 삭제돼.

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- 기존 foreign key 삭제

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON DELETE CASCADE;

DELETE FROM students WHERE student_id = 1; -- 이제 enrollments 테이블의 레코드도 같이 삭제돼

예시 3: ON UPDATE로 변경 처리

foreign key에 ON UPDATE CASCADE를 주면, 부모 테이블의 값이 바뀔 때 자식 테이블의 값도 자동으로 바뀌어.

-- 부모 키가 바뀌면 자식 테이블에도 자동 반영되게 foreign key 설정:
ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey;

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON UPDATE CASCADE;

-- 학생 ID를 바꿔보자:
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- 이제 enrollments 테이블의 student_id도 10으로 바뀜.

데이터 무결성 테스트하기

foreign key 설정이 실제로 어떻게 동작하는지 여러 상황에서 꼭 테스트해보는 게 좋아:

1. 잘못된 student_id나 course_id로 insert 시도해보기.
2. students에서 데이터 삭제하고 enrollments 테이블이 어떻게 되는지 확인해보기.
3. students 테이블의 데이터를 바꿔보고, 연관된 레코드가 제대로 업데이트됐는지 확인해보기.

foreign key 쓸 때 주의할 점

가끔 헷갈릴 수 있는 상황들이 있어:

• 인덱스 없음. 부모 테이블(students 같은)에 참조되는 컬럼에 인덱스가 없으면, PostgreSQL이 느려질 수 있어. 그래서 부모 테이블의 primary key는 항상 인덱스여야 해.
• 순환 참조. 두 테이블이 서로를 참조하면, 데이터 insert할 때 문제가 생길 수 있어. 이런 경우에는 설계를 더 신중하게 해야 해.
• 전체 레코드 삭제. cascade delete로 모든 레코드를 지울 때, 자식 테이블의 데이터 특성을 잘 고려해야 예기치 않은 상황을 막을 수 있어.

이런 문제를 피하려면, 테이블 설계를 꼼꼼히 하고, 실제 DB에 적용하기 전에 관계 규칙을 꼭 테스트해보는 게 중요해.