[postgres] 외래키 제약 조건에 대한 정리

postgres 데이터베이스를 활용해서 테이블을 구성하고, 회원탈퇴 기능을 구현하려고 하는 와중에 삭제 요청을 하니 외래키 참조가 지정된 상위 테이블이 존재하여 삭제가 불가능하다는 에러 문구가 떴었다. 이러한 삭제가 불가능한 이유에 대해서 알아보니 관계형 데이터베이스에서 참조 무결성을 유지하여 상위 테이블과 하위 테이블 간의 관계가 유효한지 확인하는 것이 외래키 제약 조건이 존재하는 이유라는 것을 확인할 수 있었다.
 
따라서, 만일 회원탈퇴 처리를 할 때 해당 유저가 작성한 글이나 북마크 목록 등의 개인 정보를 연쇄적으로 삭제하기 위해서는 기본적으로 설정되어 있는 제약 조건을 수정할 필요성을 알게 되었고, 이 포스트는 해당 기능 구현을 위해 찾아본 내용을 간략하게 정리하여 향후 참고하기 위한 목적으로 작성되었다.
 

CASCADE | 참조중인 상위 테이블의 행을 연쇄적으로 삭제

부모 테이블의 행이 삭제되면 이와 연관된 자식 테이블의 모든 행도 자동으로 삭제처리하고 싶을 때 CASCADE 를 사용한다.  이 때 postgres 에서는 ON DELETE 을 같이 붙여서, ON DLETE CASCADE 라고 입력해야 한다. 이렇게 되면 향후 Departments 테이블의 행을 삭제하면, 해당 행의 기본키를 외래키로 지정한 Employees 테이블의 행도 같이 삭제된다.
 

CREATE TABLE Departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id) ON DELETE CASCADE
);

 

SET NULL | 외래키 열(컬럼)을 NULL 로 지정한다.

부모 테이블의 행이 삭제되면 자식 테이블의 외래키 열이 NULL로 지정된다. 사용 방법은 CASCADE 와 동일하며 ON DELETE 뒤에 SET NULL 을 입력한다.
 
예시에 오타가 있는데 향후 수정할 것이다

CREATE TABLE Departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id) ON DELETE CASCADE
);

 

RESTRICT | 참조 중인 테이블이 존재하면 삭제 거부

부모 테이블의 행이 삭제되려고 할 때 자식 테이블에 연결된 행이 있으면 삭제가 거부된다.
 

CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id) ON DELETE RESTRICT
);

 

NO ACTION | (기본값) 참조 중인 테이블이 존재하면 삭제 거부

이 옵션은 기본적으로 외래키를 지정하면 적용된다고 나와 있다. 하지만 RESTRICT와 동일한 동작을 수행한다. 부모 테이블의 행이 삭제되려고 할 때 자식 테이블에 연결된 행이 있으면 삭제가 거부된다.
 

CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id) ON DELETE NO ACTION
);

[참고]무결성 제약조건에 대한 정리

개체 무결성(Entity Integrity)

개체 무결성은 기본 키(primary key)가 각 행(row)을 고유하게 식별하고 NULL 값을 가질 수 없도록 하는 규칙. 즉, 기본 키는 데이터베이스 내의 각 행을 유일하게 식별해야 하며, NULL 값을 가질 수 없어야 한다.

 

참조 무결성(Referential Integrity)

참조 무결성은 외래 키(foreign key) 관계에서 관리된다. 이는 외래 키가 참조하는 테이블의 기본 키 값과 일치하거나 NULL이어야 한다. 또한, 부모 테이블에서 업데이트나 삭제가 발생할 때 이와 관련된 자식 테이블의 데이터가 일관성 있게 유지되어야 한다. 즉, 어느 행은 삭제 되었더니 null 이 되고, 어느 행은 연쇄적으로 삭제 되는 등의 처리가 아니라 일관성 있게 관리되어야 한다.
 

도메인 무결성(Domain Integrity)

도메인 무결성은 각 열(column)이 허용되는 값의 범위를 제한하는 규칙. 즉, 각 열은 정의된 데이터 형식과 크기를 준수해야 하며, NULL 값이나 잘못된 데이터 유형이 들어가지 않도록 해야 한다.

 

사용자 정의 무결성(User-defined Integrity)

사용자 정의 무결성은 데이터베이스 관리자나 응용 프로그래머가 정의한 비즈니스 규칙을 나타 냄. 이는 데이터베이스에 특정 비즈니스 논리가 준수되도록 보장하는 데 사용한다..

 

무결성 제약 조건(Integrity Constraints)

무결성 제약 조건은 데이터베이스 내의 무결성을 유지하기 위해 정의되는 규칙. 이러한 제약 조건은 기본 키, 외래 키, 고유 제약 조건, NOT NULL 제약 조건 등을 포함한다.
 

참고하면 좋은 문헌

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-foreign-key/

PostgreSQL Foreign Key