안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 6-3. 데이터 무결성 - 기본 키(Primary Key) 제약, UNIQUE 제약, 외래 키(Foreign Key) 제약을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 데이터 무결성 - 기본 키 제약, UNIQUE 제약, 외래 키(Foreign Key) 제약입니다. 

TL;DR

기본 키와 UNIQUE, 외래 키 제약은 데이터의 고유성을 보장하는 역할을 하며, 외래 키 제약은 다른 테이블의 데이터와 연결하여 데이터 무결성을 유지하는 중요한 역할을 합니다. 데이터 무결성 제약을 이해하면 테이블 간의 관계와 JOIN을 더 효과적으로 이해할 수 있습니다.

SQL 쿼리 JOIN을 설명하면서 외래 키에 대해서 잠시 소개해 드렸는데요, 이번 강좌에서 상세히 살펴봅니다.
제약에서 많은 분들이 어려워하는 부분이죠. 기본 키 제약, 외래 키 제약, 유니크 제약만 이해하시면, 테이블의 관계와 JOIN에 대해 더 잘 이해할 수 있습니다.

먼저 SQL Server 공식 용어정의 먼저 해 볼까요.

• 기본 키 제약 - PRIMARY KEY Constraint
• 외래 키 제약(또는 참조 키 제약) - FOREIGN KEY Constraint
• UNIQUE 제약 - UNIQUE Constraint

으로 명명하고 있습니다. 가끔 발음 그대로 프라이머리키, 포린키, 유니크 제약이라고 각각 부르는 분들도 있으니, 이런 경우를 미리 대비해 용어부터 정리합니다. 

그런데, 왜 나눠서 강좌를 진행하지 않고 
복잡하다는(?) 세 개의 제약을 묶어서 진행하나요?

기본 키 제약과 유니크 제약이 비슷하기 때문이고, 대부분 함께 사용되기 (기본 키 OR 유니크 제약 + 외래 키 제약) 때문에 같이 배우면 더 이해가 빠릅니다.

기본 키 제약과 UNIQUE 제약

기본 키 제약과 UNIQUE 제약을 설명드리면, 기본 키 제약과 유니크 제약 모두 개별 로우(Row, 레코드, 행)를 구별하는 구분자입니다. 즉, 하나의 테이블에서 로우의 고유성을 설정하는 제약입니다.

USE AdventureWorks;
GO

-- 만약 테이블이 존재하면 삭제
IF OBJECT_ID(N'dbo.orders', N'U') IS NOT NULL 
   DROP TABLE dbo.orders;
IF OBJECT_ID(N'dbo.products', N'U') IS NOT NULL 
   DROP TABLE dbo.products;  
IF OBJECT_ID(N'dbo.customers', N'U') IS NOT NULL 
   DROP TABLE dbo.customers;  
GO

-- customers 테이블 생성
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,  -- 기본 키 
  customer_name NVARCHAR(10) NOT NULL,
  email NVARCHAR(15)
    CONSTRAINT AK_email UNIQUE(email)  -- UNIQUE 제약
);
GO

-- customers 테이블에 데이터 삽입
INSERT INTO customers (customer_id, customer_name, email)
VALUES
  (1, N'김대우', N'kdw@example.com'),
  (2, N'손석구', N'ssk@example.com'),
  (3, N'박은빈', N'peb@example.com');
GO

INSERT INTO customers (customer_id, customer_name, email)
VALUES (1, N'김태리', N'ktr@example.com');  -- 기본 키에 중복 값 추가 에러

메시지 2627, 수준 14, 상태 1, 줄 26
Violation of PRIMARY KEY constraint 'PK__customer__CD65CB8552246D43'. Cannot insert duplicate key in object 'dbo.customers'. The duplicate key value is (1).
The statement has been terminated.

INSERT INTO customers (customer_id, customer_name, email)
VALUES (4, N'김우빈', N'kdw@example.com');  -- UNIQUE 제약 email에 중복 값 추가 에러

메시지 2627, 수준 14, 상태 1, 줄 33
Violation of UNIQUE KEY constraint 'AK_email'. Cannot insert duplicate key in object 'dbo.customers'. The duplicate key value is (kdw@example.com).
The statement has been terminated.

INSERT INTO customers (customer_id, customer_name, email)
VALUES (4, N'김우빈', NULL);  -- UNIQUE 제약 email에 NULL 값 추가 성공

기본 키 제약과 UNIQUE 제약은 둘 다 중복값을 허용하지 않는 제약입니다. 

두 제약의 차이점은 NULL 가능 여부입니다. UNIQUE 제약은 NULL 값을 1개 삽입 가능하며 기본 키 제약은 경우 NULL값 사용이 불가합니다. 테이블에 NULL값 1개를 입력 가능 또는 불가 여부가 두 제약의 차이점입니다.

참고로, 기본 키 제약을 SSMS의 테이블 디자인에서 설정 가능합니다.

이미지 - SSMS 기본키 제약 설정

테이블 디자인에서 컬럼을 선택하고 기본 키 설정을 하면 됩니다. 

유니크 제약을 SSMS에서 생성하려면, 컬럼을 선택하고, “인덱스/키” 항목에서 추가합니다.

이미지 - SSMS UNIQUE 제약

항상 SQL 쿼리로 생성하고 설정하는 방법을 잘 기록해 두시기 바랍니다.

외래 키 제약

외래 키 제약은 참조하는 컬럼의 데이터가 반드시 참조받는 컬럼의 데이터로 존재해야만 하는 제약입니다. 실제로 업무에 많이 사용하는 제약이기도 하지요. 

지난 6-1. 데이터 무결성 - 데이터 무결성 이해 강좌에서 소개해 드렸던 내용 기억하시나요?

직급 테이블

사원 테이블

회사에 부장, 과장, 대리, 사원 직급이 있습니다. 그런데, 분명히 회사 사람인데 직급이 주임이라는 존재하지 않는 직급으로 되어 있다면 문제가 될 수 있습니다. 이럴 경우 잘못된 값인지 아닌지를 어떻게 판별이 가능할까요? 이것이 바로 참조 제약입니다.

 
2-9.  JOIN - 테이블 연결 강좌에서 소개해 드렸던 제품 - 주문 예제를 다시 활용하겠습니다.

USE AdventureWorks;
GO
-- 만약 테이블이 존재하면 삭제
IF OBJECT_ID(N'dbo.orders', N'U') IS NOT NULL  
   DROP TABLE dbo.orders;  
IF OBJECT_ID(N'dbo.products', N'U') IS NOT NULL  
   DROP TABLE dbo.products;  
GO

-- products 테이블 생성
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name NVARCHAR(10),
  price DECIMAL(10, 0)
);
GO

-- orders 테이블 생성
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  order_date DATE,
  total_amount DECIMAL(10, 0),
  FOREIGN KEY (product_id) REFERENCES products (product_id)  -- 외래 키 제약
);
GO

-- products 테이블에 데이터 삽입
INSERT INTO products (product_id, product_name, price)
VALUES
  (1, N'초코파이', 1200),
  (2, N'칸쵸', 800),
  (3, N'포테토칩', 1800),
  (4, N'꼬북칩', 1300);
GO

-- orders 테이블에 데이터 삽입
INSERT INTO orders (order_id, product_id, order_date, total_amount)
VALUES
  (1, 1, '2022-01-01', 100.50),
  (2, 3, '2022-02-05', 75.20),
  (3, 2, '2022-03-10', 150.80),
  (4, 2, '2022-04-15', 200.00);
GO

-- 샘플 데이터 조회
SELECT * FROM products;
SELECT * FROM orders;
GO

--JOIN 출력
SELECT * FROM products
    INNER JOIN orders ON products.product_id = orders.product_id;
GO

회사의 상품 테이블을 보면 “새우깡”이 없습니다. 만약, 존재하지 않는 제품을 고객이 주문한다면 어떻게 될까요? 어떻게 제품 테이블과 주문 테이블을 연결할 수 있을까요? 이럴 때 외래 키 제약이 위의 쿼리처럼 사용됩니다.

주문 테이블의 제품ID는 제품 테이블의 제품ID에 존재하는 값이어야 합니다. - REFERENCES 제품(제품ID) 

많은 분들이 참조하는 데이터(주문 테이블) 역시 1:1로 제품과 고유하게 매칭되어야 하는 것으로 오해하기도 합니다. 외래 키 제약은 참조받는 컬럼에 데이터가 있는 경우, 중복되어도 상관없습니다. 이럴 경우 “1대 다(One to Many)” 관계라고 표현합니다.

그렇다면? 왜 기본 키 제약과 함께 외래 키 제약을 설명하는 걸까요?

USE AdventureWorks;
GO
-- 만약 테이블이 존재하면 삭제
IF OBJECT_ID(N'dbo.orders', N'U') IS NOT NULL  
   DROP TABLE dbo.orders;  
IF OBJECT_ID(N'dbo.products', N'U') IS NOT NULL  
   DROP TABLE dbo.products;  
GO

-- products 테이블 생성
CREATE TABLE products (
  product_id INT, -- PRIMARY KEY  기본키 제약을 주석처리
  product_name NVARCHAR(10),
  price DECIMAL(10, 0)
);
GO

-- orders 테이블 생성
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  order_date DATE,
  total_amount DECIMAL(10, 0),
  FOREIGN KEY (product_id) REFERENCES products (product_id)  -- 외래 키 제약
);
GO

오류
메시지 1776, 수준 16, 상태 0, 줄 123
There are no primary or candidate keys in the referenced table 'products' that match the referencing column list in the foreign key 'FK__orders__product___70FDBF69'.
메시지 1750, 수준 16, 상태 1, 줄 123
Could not create constraint or index. See previous errors.

외래 키 제약 설정 불가 오류 발생 - 제품 테이블에 기본 키나 후보 키가 없음.

주문 테이블처럼 참조하는 값들은 중복되어도 상관없으나 제품 테이블 같은 참조받는 테이블 키에 값이 존재해야 하고, 고유해야 합니다.

만약, 제품ID가 고유하지 않다면? 주문을 설정할 때 값을 과연 참조하는 것일지 명확히 설정할 수 없으니 에러가 발생해야 정상입니다.

그렇다면 외래 키 제약을 SSMS에서 설정하는 방법을 살펴보겠습니다. 

이미지 - SSMS 외래키 설정

orders 주문 테이블에서 디자인을 수행하고, 외래 키를 설정할 컬럼 product_id를 선택합니다. “관계” 항목을 수행해 위와 같이 외래 키 설정 화면에서 외래 키를 생성하거나 수정할 수 있습니다. 또는 데이터베이스 다이어그램에서도 설정할 수 있습니다.

이미지 - SSMS 데이터베이스 다이어그램 외래 키 설정

이렇게 데이터베이스 다이어그램에서 외래 키를 설정할 orders와 products 테이블을 추가합니다. 이어서, 외래 키를 구성할 orders의 product_id를 잡고 products 테이블로 드래그하면 자동으로 외래 키 설정 화면이 뜨면서 설정됩니다. (마우스 드래그 방향이 참조하는 방향입니다.)

SSMS를 이용해 GUI로 외래 키 설정을 하실 일이 많지는 않겠지만(저도 강좌 만들면서 SSMS로는 처음 해봐요), 이런 방식도 있다는 것만 기억하셔도 됩니다. 

외래 키와 테이블 데이터 수정/삭제

외래 키를 처음 설정하고 가장 먼저 겪는 문제는, 테이블에서 데이터를 삭제하거나 수정하려고 할 때, 이런 오류를 만나게 됩니다.

삭제하려는 컬럼에 외래 키가 참조하고 있어 삭제할 수 없다는 오류가 발생합니다. UPDATE도 같은 오류가 발생합니다.

이런 오류가 발생하는 이유는 참조 무결성을 다시 생각해 보면 쉽습니다. 외래 키를 구성하면서 참조받는 제품 테이블의 컬럼에 값이 있어야 참조하는 주문 테이블에 INSERT가 가능했습니다. 제품 테이블의 참조받는 로우를 삭제하려면 먼저 주문 테이블이나 여러 테이블에서 참조하는 값을 삭제해야 참조받는 테이블의 로우를 삭제할 수 있습니다.

외래 키 때문에 삭제나 수정이 안된다고 외래 키를 사용하지 않을 경우, 데이터 무결성 문제가 발생해 더 큰 장애로 이어질 수 있습니다. 천천히 참조하는 테이블의 값을 수정/삭제하고 다음에 참조받는 테이블을 수정/삭제하세요.

테이블 간 외래 키 설정과 관계 구성을 보기 어렵다면, 아래 SQL 쿼리를 이용해 테이블 간 참조 관계 확인도 가능합니다.

USE AdventureWorks;
GO

-- 테이블의 외래 키와 참조하는 테이블 리스트
SELECT name as Foreign_Key
,schema_name(schema_id) as Schema_Name
,object_name(parent_object_id) as Table_Name
FROM sys.foreign_keys
WHERE Referenced_object_id = object_id('Production.Product','U'); -- 스키마이름, 테이블이름
GO

또한, 몇 번 소개해 드린 SSMS에서 “데이터베이스 다이어그램”을 생성하고 확인하면 손쉽게 테이블 간 관계를 확인할 수 있습니다. 

이미지 - 데이터베이스 다이어그램을 이용한 테이블 간 관계 확인

Cascade Referential Integrity(연계 참조 무결성 제약 조건)

연계 참조 무결성(Cascade Referential Integrity)은 일반적으로 CASCADE RI라고 부릅니다. 이런 상황을 가정해 보세요.

만약 제품테이블 제품ID 같은 참조받는 값이 변경되거나 삭제가 된다면?

주문 테이블처럼 참조하는 로우 값들은 어떻게 될까요? 
네 맞습니다. 만약 제품 테이블처럼 참조받는 값이 함부로 변경이 된다면, 참조하는 값은 공중에 붕 떠버리는 사태가 일어날 수 있겠죠? 그래서 SQL Server는 참조하는 값이 참조할 값으로 자동 수정/삭제될 수 있도록 설정하는 기능을 제공합니다. 이 기능을 연계 참조 무결성이라고 부릅니다. 

구문 정보

CASCADE 할 건지 안 할 건지만 지정하면 됩니다. 당연히 참조값이 수정/삭제될 경우에만 있겠지요.

다시 위의 제품 / 주문 테이블을 활용해 보겠습니다.

USE AdventureWorks;
GO
-- 만약 테이블이 존재하면 삭제
IF OBJECT_ID(N'dbo.orders', N'U') IS NOT NULL  
   DROP TABLE dbo.orders;  
IF OBJECT_ID(N'dbo.products', N'U') IS NOT NULL  
   DROP TABLE dbo.products;  
GO

-- products 테이블 생성
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name NVARCHAR(10),
  price DECIMAL(10, 0)
);
GO

-- orders 테이블 생성
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  order_date DATE,
  total_amount DECIMAL(10, 0),
  FOREIGN KEY (product_id) REFERENCES products (product_id)  -- 외래 키 제약
    ON UPDATE CASCADE  -- CASCADE RI 설정 UPDATE시 자동 연계
    ON DELETE NO ACTION  -- CASCADE RI 설정 DELETE시 동작 안 함
);
GO

-- products 테이블에 데이터 삽입
INSERT INTO products (product_id, product_name, price)
VALUES
  (1, N'초코파이', 1200),
  (2, N'칸쵸', 800),
  (3, N'포테토칩', 1800),
  (4, N'꼬북칩', 1300);
GO

-- orders 테이블에 데이터 삽입
INSERT INTO orders (order_id, product_id, order_date, total_amount)
VALUES
  (1, 1, '2022-01-01', 100.50),
  (2, 3, '2022-02-05', 75.20),
  (3, 2, '2022-03-10', 150.80),
  (4, 2, '2022-04-15', 200.00);
GO

-- 샘플 데이터 조회
SELECT * FROM products;
SELECT * FROM orders;
GO

-- 참조받는 products 테이블의 product_id에 UPDATE 수행
UPDATE products SET product_id = 99
WHERE product_id = 3;
GO

-- 샘플 데이터 조회
SELECT * FROM products;
SELECT * FROM orders;  -- 참조하는 orders 테이블의 product_id도 CASCADE로 자동 변경됨.
GO

-- 참조받는 products 테이블에 DELETE 수행
DELETE FROM products WHERE product_id = 2;  -- 에러. DELETE는 NO ACTION으로 지정했기 때문. 수동으로 참조하는 orders 테이블을 수정한 다음 삭제 해야 함.
GO

이렇게 CASCADE RI를 구성하면 참조하는 값도 자동으로 변경/삭제될 수 있도록 동작을 설정할 수 있습니다. 

CASCADE RI는 훌륭한 기능이지만, 생각보다 사용할 일이 많지 않습니다. 한번 등록된 제품 테이블과 같은 참조받는 값은, 제품이름이나 정보는 종종 바뀌더라도 INSERT시 부여받는 “고윳값”은 내부적으로만 사용되어 참조받는 고윳값이 실제로 수정/삭제될 일은 거의 없기 때문입니다. 삭제도 실제 삭제가 아니라, 특정 컬럼의 Flag를 변경해(예를 들어, is_deleted 컬럼을 생성하고 컬럼 값을 이용) 애플리케이션에서 삭제 상태로 보이도록 처리하기 때문입니다. 경우에 따라 CASCADE RI를 사용하시길 바라며, 가급적 테이블을 디자인할 때 참조받는 값(보통 기본 키)은 수정/삭제되지 않도록 디자인하시면 좋습니다.

실무에서 거의 모든 테이블에 적용되는 핵심 기능

참조 무결성의 핵심인 기본 키와 외래 키 조합은 실무에서 거의 모든 테이블에 적용되어 사용될 정도로 중요한 데이터의 무결성을 보장하는 데이터베이스의 핵심 기능입니다. 처음이라 어려울 수도 있지만, 차근차근 살펴보시고 궁금한 사항은 Q&A 게시판에 문의하세요.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022