[ PostgreSQL ] REFERENCES - 다른 테이블의 key를 사용하기

PostgreSQL에서 외래 키(Foreign Key) 사용법

외래 키(Foreign Key)는 한 테이블의 컬럼이 다른 테이블의 기본 키(Primary Key) 또는 유니크 키(Unique Key)를 참조하도록 설정하는 제약 조건입니다. 외래 키는 데이터베이스에서 참조 무결성(Referential Integrity)을 유지하는 데 중요한 역할을 합니다. 즉, 외래 키는 부모 테이블의 데이터와 자식 테이블의 데이터가 일관되도록 보장합니다.

외래 키의 특징

• 참조 무결성: 외래 키는 자식 테이블의 컬럼이 부모 테이블의 키를 정확히 참조하도록 보장합니다. 자식 테이블에 삽입되는 값은 부모 테이블에 존재하는 값이어야 합니다.
• 단방향 관계: 부모 테이블과 자식 테이블 간에는 단방향 관계가 성립합니다. 부모 테이블의 기본 키는 자식 테이블에서 참조될 수 있지만, 그 반대는 아닙니다.
• 삭제 및 업데이트 규칙: 외래 키 제약은 부모 테이블의 데이터가 삭제되거나 수정될 때 자식 테이블의 데이터를 어떻게 처리할지에 대한 규칙을 정의할 수 있습니다. 이 규칙은 ON DELETE와 ON UPDATE로 설정할 수 있습니다.

외래 키 규칙 (옵션)

1. ON DELETE CASCADE: 부모 테이블에서 데이터가 삭제되면 자식 테이블에서 참조하는 데이터도 자동으로 삭제됩니다.
2. ON DELETE SET NULL: 부모 테이블에서 데이터가 삭제되면 자식 테이블에서 해당 외래 키 값이 NULL로 설정됩니다.
3. ON DELETE RESTRICT: 부모 테이블에서 데이터가 삭제되면 자식 테이블에서 해당 데이터가 참조 중이라면 삭제할 수 없습니다.
4. ON DELETE NO ACTION: 기본값이며, 부모 데이터가 삭제될 때 아무런 액션도 취하지 않습니다.
5. ON DELETE SET DEFAULT: 부모 테이블에서 데이터가 삭제되면 DEFAULT롤 설정된 값으로 설정됩니다.

외래 키 사용법

1. 테이블 생성 시 외래 키 설정
   • 자식 테이블에 외래 키 제약을 추가하여 부모 테이블의 특정 컬럼을 참조하도록 합니다.
2. 기존 테이블에 외래 키 추가
   • ALTER TABLE을 사용하여 외래 키 제약을 나중에 추가할 수도 있습니다.

---

예제 1: 테이블 생성 시 외래 키 사용

-- 부모 테이블: customers
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

-- 자식 테이블: orders
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,  -- 외래 키로 참조할 컬럼
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

• customers 테이블은 고객 정보를 담고 있으며, customer_id는 기본 키입니다.
• orders 테이블은 고객의 주문 정보를 담고 있으며, customer_id는 customers 테이블의 customer_id를 참조하는 외래 키입니다.
• CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id) 부분에서 customer_id가 외래 키로 설정되었고, 이를 통해 orders 테이블의 각 주문은 반드시 존재하는 고객과 연결됩니다.

예제 2: 외래 키와 ON DELETE CASCADE 사용

부모 테이블에서 데이터가 삭제되면 자식 테이블의 관련 데이터도 자동으로 삭제되도록 설정할 수 있습니다.

-- 부모 테이블: departments
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

-- 자식 테이블: employees
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (department_id)
    ON DELETE CASCADE  -- 부모 테이블에서 부서 삭제 시, 해당 부서에 속한 직원도 삭제
);

INSERT INTO departments
(department_name)
VALUES ('management');

INSERT INTO emplyees
(employee_name, department_id)
VALUES ('John', 1);

DELETE FROM departments WHERE department_id=1;

• departments 테이블에서 department_id를 기본 키로 설정하고, employees 테이블에서 이 컬럼을 참조하는 외래 키를 추가했습니다.
• ON DELETE CASCADE 옵션을 사용하여, 만약 departments 테이블에서 부서가 삭제되면, 해당 부서에 속한 직원(employees 테이블의 행)도 자동으로 삭제되도록 설정합니다.

예제 3: 외래 키와 ON DELETE SET NULL 사용

부모 테이블의 데이터가 삭제되면, 자식 테이블에서 해당 외래 키 값이 NULL로 설정되도록 할 수 있습니다.

-- 부모 테이블: products
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL
);

-- 자식 테이블: order_items
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (product_id)
    ON DELETE SET NULL  -- 부모 테이블에서 제품 삭제 시, 해당 제품을 참조하는 값은 NULL로 설정
);

INSERT INTO products
(product_name)
VALUES ('bicycle'); 

INSERT INTO order_items
(order_id, product_id, quantity)
VALUES (1, 1, 3000);

DELETE FROM products WHERE product_id=1;

• products 테이블에서 product_id는 기본 키이며, order_items 테이블에서 이를 외래 키로 참조합니다.
• ON DELETE SET NULL을 사용하면, 부모 테이블에서 제품이 삭제될 때, order_items 테이블에서 해당 제품을 참조하는 product_id 값이 NULL로 설정됩니다.

예제 4: 외래 키와 ON DELETE RESTRICT 사용

부모 테이블의 데이터가 자식 테이블에서 참조 중일 때 삭제를 제한할 수 있습니다.

-- 부모 테이블: categories
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

-- 자식 테이블: products
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category_id INT,
    CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories (category_id)
    ON DELETE RESTRICT  -- 부모 테이블에서 카테고리 삭제 시, 해당 카테고리를 참조하는 제품이 있으면 삭제 불가
);

INSERT INTO categories
(category_name)
VALUES ('bicycle'); 

INSERT INTO products
(product_name, category_id)
VALUES ('Racing', 1);

DELETE FROM categories WHERE category_id=1;

• categories 테이블의 category_id를 참조하는 products 테이블에서 외래 키가 설정되었습니다.
• ON DELETE RESTRICT 옵션을 사용하면, categories 테이블에서 카테고리가 삭제될 때, 해당 카테고리를 참조하는 제품이 존재하면 삭제가 제한됩니다.

---

예제 5: 기존 테이블에 외래 키 추가

-- 기존 테이블에 외래 키 추가
ALTER TABLE orders
ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

• ALTER TABLE을 사용하여 orders 테이블에 customer_id 외래 키 제약을 추가하는 예제입니다. 이 방식은 이미 생성된 테이블에 외래 키 제약을 추가할 때 사용됩니다.

---

외래 키의 역할

1. 데이터 무결성 보장: 외래 키는 부모 테이블의 데이터가 변경될 때 자식 테이블이 올바르게 반영되도록 보장하여 데이터 무결성을 유지합니다.
2. 연관된 데이터 추적: 외래 키를 통해 여러 테이블 간의 관계를 명확히 정의하고, 관련된 데이터를 추적할 수 있습니다.
3. 데이터 삭제/수정의 일관성: 외래 키 제약은 데이터 삭제 및 수정 시 일관성을 유지하는데 도움을 줍니다. ON DELETE CASCADE나 ON DELETE SET NULL 등의 옵션을 사용하여 부모 테이블의 삭제가 자식 테이블에 미치는 영향을 정의할 수 있습니다.

---

요약

• 외래 키(Foreign Key)는 한 테이블의 컬럼이 다른 테이블의 기본 키 또는 유니크 키를 참조하도록 설정하여, 데이터베이스의 참조 무결성을 보장합니다.
• 외래 키는 ON DELETE, ON UPDATE와 같은 옵션을 사용하여 참조된 데이터가 삭제되거나 업데이트될 때 자식 테이블에서 어떻게 처리할지 정의할 수 있습니다.
• PostgreSQL에서는 테이블 생성 시 또는 나중에 ALTER TABLE을 사용하여 외래 키를 설정할 수 있습니다.

이렇게 외래 키는 관계형 데이터베이스에서 데이터의 일관성, 무결성 및 구조적인 관계를 유지하는 중요한 역할을 합니다.