69강 SQL 초석 다지기 : PostgreSQL Foreign Key constraint

728x90

ON DELETE RESTRICT DEFERRABLE, PostgreSQL 기초강의, PostgreSQL 기초강좌

69강 SQL 초석 다지기 : PostgreSQL Foreign Key constraint

SQL 초석 다지기 69강 시작합니다.

이번 시간에는

▶ PostgreSQL Foreign Key constraint

에 대해서 알아보는 시간입니다.

요약: 이번 강의에서는 PostgreSQL 외부 키와 외부 키 제약 조건을 사용하여 테이블에 외부 키를 추가하는 방법에 대해 배울 것입니다.

Introduction to PostgreSQL Foreign Key Constraint

외부 키는 다른 테이블의 기본 키를 참조하는 테이블의 열 또는 열 그룹입니다.

외부 키가 포함된 테이블을 참조 테이블 또는 하위 테이블이라고 합니다. 그리고 외부 키에 의해 참조되는 테이블을 참조되는 테이블 또는 상위 테이블이라고 합니다.

테이블은 다른 테이블과의 관계에 따라 여러 개의 외부 키를 가질 수 있습니다.

PostgreSQL은 외부 키 제약 조건을 사용하여 외부 키를 정의합니다. 외부 키 제약 조건은 하위 테이블과 상위 테이블 간의 데이터 참조 무결성을 유지하는 데 도움이 됩니다.

외부 키 제약 조건은 하위 테이블의 열 또는 열 그룹의 값이 상위 테이블의 열 또는 열 그룹의 값과 동일함을 나타냅니다.

PostgreSQL foreign key constraint syntax

다음은 외부 키 제약 조건 구문을 보여줍니다:

[CONSTRAINT fk_name]
   FOREIGN KEY(fk_columns)
   REFERENCES parent_table(parent_key_columns)
   [ON DELETE delete_action]
   [ON UPDATE update_action]

위 구문에서:

먼저 CONSTRAINT 키워드 뒤에 외부 키 제약 조건의 이름을 지정합니다. 제약 조건 절은 선택 사항입니다. 생략하면 PostgreSQL에서 자동 생성된 이름을 할당합니다.
둘째, FOREIGN KEY 키워드 뒤에 있는 괄호 안에 하나 이상의 외부 키 열을 지정합니다.
셋째, REFERENCES 절에서 외부 키 열이 참조하는 상위 테이블 및 상위 키 열을 지정합니다.
마지막으로 ON DELETE 및 ON UPDATE 절에서 삭제 및 업데이트 작업을 지정합니다.

삭제 및 업데이트 작업은 상위 테이블의 기본 키가 삭제 및 업데이트될 때의 동작을 결정합니다. 기본 키가 거의 업데이트되지 않기 때문에 ON UPDATE 작업은 실제로 자주 사용되지 않습니다. ON DELETE 작업에 집중하겠습니다.

PostgreSQL은 ON DELETE절의 옵션으로 다음 작업을 지원합니다:

SET NULL
SET DEFAULT
RESTRICT
NO ACTION
CASCADE

PostgreSQL foreign key constraint examples

다음 문장은 고객 및 연락처 테이블을 생성합니다:

(작업은 testdb에서 하시기 바랍니다.)

DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS contacts;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id)
  REFERENCES customers(customer_id)
);

이 예에서는 고객 테이블이 상위 테이블이고 연락처 테이블이 하위 테이블입니다.

각 고객은 0개 또는 여러 개의 연락처를 가지고 있으며 각 연락처는 0개 또는 한 개의 고객에 속합니다.

연락처 테이블의 customer_id 열은 고객 테이블에서 동일한 이름을 가진 기본 키 열을 참조하는 외부 키 열입니다.

연락처 테이블의 다음 외부 키 제약 조건 fk_customer는 customer_id를 외부 키로 정의하였습니다.

CONSTRAINT fk_customer
FOREIGN KEY(customer_id)
REFERENCES customers(customer_id)

외부 키 제약 조건에는 ON DELETE 및 ON UPDATE 작업이 없으므로 기본적으로 NO Action으로 설정됩니다.

이제 실습을 해보겠습니다.

NO ACTION

다음은 고객 및 연락처 테이블에 데이터를 삽입합니다:

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');

INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev');

이 상태에서 고객 테이블의 customer_id 1을 삭제해 봅시다.

DELETE FROM customers
WHERE customer_id = 1;

ON DELETE NO ACTION으로 인해 고객 ID 1의 참조 행이 연락처 테이블에 여전히 존재하기 때문에 PostgreSQL에서 제약 조건 위반이 발생합니다:

RESTRICT 작업은 NO 작업과 유사합니다.

RESTRICT는 INITIALLY DEFERRED(초기 지연) 또는 INITIALLY IMMEDIATE(초기 즉시) 모드에서 DEFERRABLE(지연가능)로 외래 키 제약 조건을 정의할 때만 발생합니다. 이에 대해서는 뒤에서 자세히 설명하겠습니다.

SET NULL

SET NULL은 상위 테이블의 참조 행이 삭제될 때 자식 테이블의 참조 행에 있는 외부 키 열에 NULL을 자동으로 설정합니다.

실습을 위해서 샘플 테이블을 삭제하고 ON DELETE 절에서 SET NULL 옵션을 사용하는 외부 키로 다시 만들고 데이터를 삽입합니다.

DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id)
  REFERENCES customers(customer_id)
  ON DELETE SET NULL
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');

INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev');

SET NULL의 작동 방식을 확인하기 위해서 고객 테이블에서 ID가 1인 고객을 삭제합니다:

DELETE FROM customers
WHERE customer_id = 1;

ON DELETE SET NULL 작업으로 인해 연락처 테이블의 참조 행이 NULL로 설정됩니다. 확인해 보겠습니다.

SELECT * FROM contacts;

위에서 분명히 알 수 있듯이, customer_id가 1인 행은 이제 customer_id가 NULL로 설정됩니다

CASCADE

ON DELETE CASCADE는 상위 테이블의 참조 행이 삭제될 때 하위 테이블의 참조 행을 모두 자동으로 삭제합니다. 실제로는 ON DELETE CASCADE가 가장 일반적으로 사용되는 옵션입니다.

실습을 위해 샘플 테이블을 다시 작성합니다. fk_customer의 삭제 작업은 CASCADE로 변경합니다.

DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id)
  REFERENCES customers(customer_id)
  ON DELETE CASCADE
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');

INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','john.doe@bluebird.dev'),
      (1,'Jane Doe','(408)-111-1235','jane.doe@bluebird.dev'),
      (2,'David Wright','(408)-222-1234','david.wright@dolphin.dev');

다음 문장은 고객 ID 1을 삭제합니다:

DELETE FROM customers
WHERE customer_id = 1;

ON DELETE CASCADE 작업으로 인해 연락처 테이블의 모든 참조 행이 자동으로 삭제됩니다:

SELECT * FROM contacts;

SET DEFAULT

ON DELETE SET DEFAULT는 상위 테이블에서 참조되는 행이 삭제될 때 하위 테이블에서 참조하는 행의 외부 키 열에 기본값을 설정합니다.

이 옵션은 FOREIGN KEY에 DEFAULT 값을 주면, 참조 무결성을 깨기 때문에 의미가 없습니다.

DEFAULT 값을 주지 않으면, SET NULL과 같습니다.

실무에서 사용할 일은 없습니다.

RESTRICT

RESTRICT에 대한 설명은 나중에 자세히 한다고 하고는 내용이 없습니다.

그래서 여기저기 찾아 보았습니다.

RESTRICT와 NO ACTION의 근본적인 차이는

NO ACTION은 DELETE가 물리적으로 먼저 일어나다가 제약조건에 걸리는 것이고
RESTRICT는 DELETE전에 제약조건을 확인해서 물리적인 DELETE 행위자체가 일어나지 않습니다.

그러면 제약조건을 지연하는 DEFERRABLE 옵션을 보면,

DEFERRABLE : 지연가능
NOT DEFERRABLE : 지연불가능 (기본값)

생략되면 '지연불가능'이 DEFAULT입니다.

DEFERRABLE(지연가능)으로 지정되었을 때

또 다른 옵션

INITIALLY IMMEDIATE : 초기에 즉시 (기본값) - 지연은 가능한데 바로 제약조건을 수행하라
INITIALLY DEFERRED : 초기에 연기 - 지연가능하니 나중에 제약조건을 수행하라

중 하나를 선택하여야 합니다.

그러면, 2개의 차이만 알면 됩니다.

ON DELETE RESTRICT DEFERRABLE INITIALLY IMMEDIATE

이 옵션은 ON DELETE RESTRICT 제한을 즉시 수행하라

즉, DELETE전에 제약조건을 확인해서 물리적인 DELETE 행위자체가 일어나지 않는 것.

ON DELETE RESTRICT와 같다

ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRE

이 옵션은 ON DELETE RESTRICT를 연기했다가 나중에 수행하라

NO ACTION가 같습니다.

DELETE를 물리적으로 먼저 일어나고, 그 후에 제약조건에 걸리는 것.

ON DELETE 또는 ON DELETE NO ACTION과 같다.

그러니 복잡한 옵션을 사용할 일은 없다,

Add a foreign key constraint to an existing table

기존 테이블에 외부 키 제약 조건을 추가하려면 다음 형식의 ALTER TABLE 문을 사용합니다:

ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (fk_columns)
REFERENCES parent_table (parent_key_columns);

ON DELETE CASCADE 옵션을 사용하여 외부 키 제약 조건을 기존 테이블에 추가하는 경우 다음 단계를 수행해야 합니다:

먼저 기존 외부 키 제약 조건을 삭제합니다:

ALTER TABLE child_table
DROP CONSTRAINT constraint_fkey;

다음 ON DELETE CASCADE 작업을 사용하여 새 외부 키 제약 조건을 추가합니다:

ALTER TABLE child_table
ADD CONSTRAINT constraint_fk
FOREIGN KEY (fk_columns)
REFERENCES parent_table(parent_key_columns)
ON DELETE CASCADE;

이번 시간에는 PostgreSQL 외부 키와 외부 키 제약 조건을 사용하여 테이블에 대한 외부 키를 만드는 방법에 대해 배웠습니다.

내용은 많지 않은데 글로 적다 보니 분량이 많아졌네요.

수고하셨습니다.

본 강의는 PostgreSQL Tutorial을 참조합니다. https://www.postgresqltutorial.com/

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.

728x90

저작자표시 비영리 변경금지

'IT > SQL 기초강좌 (PostgreSQL)' 카테고리의 다른 글

71강 SQL 초석 다지기 : PostgreSQL UNIQUE constraint (125)	2023.01.14
70강 SQL 초석 다지기 : PostgreSQL CHECK constraint (126)	2023.01.13
68강 SQL 초석 다지기 : PostgreSQL Primary Key constraint (96)	2023.01.11
67강 SQL 초석 다지기 : PostgreSQL TRUNCATE TABLE (67)	2023.01.10
66강 SQL 초석 다지기 : PostgreSQL TEMPORARY TABLE (117)	2023.01.09