SQL Server에서 삭제 시 NULL로 설정되는 외래 키 만들기 (ON DELETE SET NULL)

이번 튜토리얼에서는 SQL Server에서 삭제 시 NULL 설정(Set Null on Delete) 제약 조건이 있는 외래 키(Foreign Key)를 만드는 방법을 단계별로 설명합니다.

SQL Server에서 ON DELETE SET NULL 외래 키란?

외래 키에 ON DELETE SET NULL 옵션이 적용되면, 부모 테이블(parent table)의 레코드가 삭제될 때 자식 테이블(child table)에서 해당 레코드를 참조하던 외래 키 컬럼의 값이 자동으로 NULL로 변경됩니다. 이때 자식 테이블의 데이터는 삭제되지 않고 그대로 유지됩니다.

이러한 외래 키는 CREATE TABLE 문 또는 ALTER TABLE 문을 사용하여 생성할 수 있습니다.

CREATE TABLE 문으로 ON DELETE SET NULL 외래 키 만들기

문법

CREATE TABLE child_table
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_name
  FOREIGN KEY (child_col1, child_col2, ... child_col_n)
  REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
  ON DELETE SET NULL
  [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
);

매개변수 설명

child_table: 생성하려는 자식 테이블의 이름입니다.
column1, column2: 테이블에 생성할 컬럼입니다. 각 컬럼은 하나의 데이터 타입을 가져야 하며, NULL 또는 NOT NULL 여부를 지정해야 합니다. 지정하지 않으면 기본값은 NULL입니다.
fk_name: 생성할 외래 키 제약 조건(constraint)의 이름입니다.
child_col1 ~ child_col_n: 부모 테이블의 기본 키를 참조할 자식 테이블의 컬럼입니다.
parent_table: 자식 테이블에서 사용할 기본 키를 포함하고 있는 부모 테이블의 이름입니다.
parent_col1 ~ parent_col_n: 부모 테이블의 기본 키를 구성하는 컬럼입니다. 외래 키는 이 컬럼들과 자식 테이블의 컬럼들 사이에 제약 관계를 형성합니다.
ON DELETE SET NULL: 부모 테이블의 데이터가 삭제되면 자식 테이블의 해당 데이터가 NULL로 설정됩니다. 자식 데이터는 삭제되지 않습니다.
ON UPDATE: 선택 사항으로, 부모 데이터가 수정될 때 자식 데이터를 어떻게 처리할지 지정합니다.

참조 동작(Referential Action) 옵션

NO ACTION: 부모 데이터가 삭제 또는 수정되어도 자식 데이터에는 아무 작업도 수행하지 않습니다.
CASCADE: 부모 데이터가 삭제 또는 수정되면 자식 데이터도 함께 삭제 또는 수정됩니다.
SET NULL: 부모 데이터가 삭제 또는 수정되면 자식 데이터가 NULL로 설정됩니다.
SET DEFAULT: 부모 데이터가 삭제 또는 수정되면 자식 데이터가 기본값으로 설정됩니다.

예제

CREATE TABLE products
(
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
(
  inventory_id INT PRIMARY KEY,
  product_id INT,
  quantity INT,
  min_stock INT,
  max_stock INT,
  CONSTRAINT fk_inv_product_id
  FOREIGN KEY (product_id)
  REFERENCES products (product_id)
  ON DELETE SET NULL
);

이 예제에서는 먼저 기본 키 product_id를 포함하는 부모 테이블 products(상품)를 생성했습니다. 그다음 외래 키와 삭제 제약 조건을 갖는 자식 테이블 inventory(재고)를 생성했으며, CREATE TABLE 문은 inventory 테이블에 fk_inv_product_id라는 이름의 외래 키를 만듭니다. 이 외래 키는 inventory 테이블의 product_id 컬럼과 products 테이블의 product_id 컬럼 사이의 관계를 형성합니다.

이 외래 키는 ON DELETE SET NULL로 지정되어 있으므로, SQL Server는 부모 테이블의 데이터가 삭제될 때 자식 테이블의 해당 레코드 값을 NULL로 설정합니다. 즉, product_id가 products 테이블에서 삭제되면, 같은 product_id를 사용하던 inventory 테이블의 레코드는 NULL로 변경됩니다.

주의: NOT NULL 컬럼과의 충돌

중요한 점은 inventory 테이블의 product_id 컬럼이 NULL로 설정될 수 있어야 하므로, 이 컬럼이 반드시 NULL 값을 허용하도록 정의해야 한다는 것입니다. 만약 컬럼을 NOT NULL로 선언하면 아래와 같은 오류가 발생합니다.

CREATE TABLE inventory
(
  inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  ...
);

Msg 1761, Level 16, State 0, Line 1
Cannot create the foreign key 'fk_inv_product_id' with the SET NULL referential action, because one or more referencing columns are not nullable.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

따라서 다음과 같이 inventory 테이블의 product_id 컬럼이 NULL 값을 받을 수 있도록 정의해야 합니다.

CREATE TABLE inventory
(
  inventory_id INT PRIMARY KEY,
  product_id INT,
  quantity INT,
  min_stock INT,
  max_stock INT,
  CONSTRAINT fk_inv_product_id
  FOREIGN KEY (product_id)
  REFERENCES products (product_id)
  ON DELETE SET NULL
);
-- Command(s) completed successfully.

ALTER TABLE 문으로 ON DELETE SET NULL 외래 키 만들기

이미 존재하는 테이블에 외래 키를 추가하려면 ALTER TABLE 문을 사용합니다.

문법

ALTER TABLE child_table
ADD CONSTRAINT fk_name
  FOREIGN KEY (child_col1, child_col2, ... child_col_n)
  REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
  ON DELETE SET NULL;

매개변수 설명

child_table: 외래 키를 추가할 자식 테이블의 이름입니다.
fk_name: 생성할 외래 키 제약 조건의 이름입니다.
child_col1 ~ child_col_n: 부모 테이블의 기본 키를 참조할 자식 테이블의 컬럼입니다.
parent_table: 기본 키를 포함하고 있는 부모 테이블의 이름입니다.
parent_col1 ~ parent_col_n: 부모 테이블의 기본 키를 구성하는 컬럼입니다. 외래 키는 이 컬럼들과 자식 테이블의 컬럼들 사이에 제약 관계를 형성합니다.
ON DELETE SET NULL: 부모 테이블의 데이터가 삭제될 때 자식 테이블의 해당 데이터가 NULL로 설정되도록 지정합니다. 자식 테이블의 데이터는 삭제되지 않습니다.

예제

ALTER TABLE inventory
ADD CONSTRAINT fk_inv_product_id
  FOREIGN KEY (product_id)
  REFERENCES products (product_id)
  ON DELETE SET NULL;

이 예제에서는 inventory 자식 테이블에 fk_inv_product_id라는 이름의 외래 키를 추가하여, product_id를 기준으로 부모 테이블 products를 참조하도록 합니다.

ON DELETE SET NULL을 지정하면 SQL Server는 부모 테이블의 product_id 데이터가 삭제될 때, 자식 테이블 inventory의 해당 레코드가 NULL 값으로 설정된다는 것을 인식합니다.