데이터베이스) DBMS 키 정리

회사 동기가 캠퍼스 리크루팅 때 모의 면접을 진행하면서 질문받은 내용을 정리해보겠습니다.

 

질문 내용: Candidate Key, Super Key, Primary Key에 대해 설명해보세요.

 

저는 질문 내용과 함께 키의 정의, 키의 필요성 그리고 보다 다양한 키에 대해서도 정리해보겠습니다.

각 키에 대한 예시는 키에 대한 개념을 설명한 뒤 예시 테이블을 통해 설명드리겠습니다.

 

Key

DBMS 키는 테이블 내 튜플(행)을 식별하기 위한 단일 속성 혹은 속성들의 집합을 말합니다.

 

 

Key가 필요한 이유

1. 앞서 언급했듯이 키는 테이블 내 튜플을 식별해주는 역할을 합니다.

• 실제 서비스를 개발할 때, 테이블 내 수많은 데이터가 있을 수 있고 심지어 동일한 데이터들이 여러 개 들어있을 수도 있습니다.
• 키는 테이블 내 수많은 데이터가 있더라도 찾고자 하는 튜플을 고유하게 식별하게 해 줄 수 있는 역할

2. 키는 테이블 간의 관계를 설정하고 테이블 간의 관계를 식별할 수 있습니다.

 

 

Key 종류

• Super Key (슈퍼키)
• Candidate Key (후보 키)
• Primary Key (기본키)
• Alternate Key (대체키)
• Foreign Key (외래 키)
• Compound Key (복합 키)
• Composite Key (복합 키)
• Surrogate Key (대리 키)
• Natural Key (자연 키)

 

Super Key (슈퍼 키)

1. 슈퍼 키는 테이블의 행을 식별하는 단일 또는 다중 키의 그룹입니다.

2. 슈퍼 키에는 고유 식별에 필요하지 않은 추가 속성이 있을 수 있습니다.

 

Candidate Key (후보 키)

1. 후보 키는 테이블에서 튜플을 고유하게 식별하는 속성 집합입니다.

2. 후보 키는 반복된 속성이 없는 Super Key입니다.

3. Primary Key는 후보 키에서 선택되어야 합니다.

4. 모든 테이블에는 최소한 하나의 후보 키가 있어야 합니다.

5. 테이블에는 여러 개의 후보 키가 있을 수 있지만 하나의 Primary Key만 있을 수 있습니다.

6. Candidate Key의 특징

• 고유한 값을 포함해야 합니다.
• 후보 키에 여러 개의 속성이 있을 수 있습니다.
• NULL 값을 포함하면 안 됩니다.
• 고유성을 보장하기 위해 최소한의 필드만 포함해야 합니다. (최소성 보장)
• 테이블의 각 튜플을 고유하게 식별해야 합니다.

 

Primary Key (기본키)

1. 기본키는 테이블의 모든 튜플을 고유하게 식별하는 테이블의 속성 혹은 속성 집합입니다.

2. 기본키는 중복될 수 없습니다. 즉, 기본키라면 테이블 내 동일한 값이 있을 수 없습니다.

3. 테이블에는 기본키가 단 하나 있을 수 있습니다.

4. 기본키 설정할 때 지켜야 하는 규칙

• 두 개의 행은 동일한 기본키를 가질 수 없습니다.
• 모든 행에 기본키를 부여해야 합니다.
• 기본키로 정의된 필드는 NULL 값일 수 없습니다.
• Foreign Key가 기본키를 참조할 경우 절대 수정하거나 업데이트할 수 없습니다.

 

Alternate Key (대체키)

1. 대체키도 Primary Key처럼 테이블의 모든 튜플을 고유하게 식별하는 테이블의 속성 혹은 속성 집합입니다.

2. 테이블에는 Primary Key 후보가 여럿 있을 수 있는데, 이러한 키들 중 Primary Key로 선정되지 않은 키들을 대체키라고 지칭합니다.

 

여기까지 내용을 그림으로 정리하자면 아래와 같습니다!

슈퍼키, 후보키, 대체키, 기본키를 표현하는 그림

 

Foreign Key (외래 키)

1. 외래 키는 두 테이블 사이에 관계를 만드는 key입니다.

2. 외래 키의 목적은 데이터 무결성을 유지하고 Entity의 서로 다른 두 인스턴스 간에 탐색을 허용하는 것입니다.

3. 다른 테이블의 기본 키를 참조할 때 두 테이블 간의 상호 참조 역할을 합니다.

4. 외래 키의 종류는 크게 세 가지입니다.

• 다른 릴레이션의 기본 키를 참조하는 외래 키
• 자체 릴레이션의 기본 키를 참조하는 외래 키
• 기본 키의 구성요소가 되는 외래 키

기본키 vs 외래키

기본키	외래키
테이블 내 튜플을 고유하게 식별	다른 테이블의 기본키가 되는 테이블의 속성
기본키는 절대 NULL 값을 허용하지 않음	외래키는 NULL 값일 수 있음
기본 키는 클러스터링된 인덱스로 DBMS 테이블의 데이터는 클러스터링된 인덱스의 순서로 물리적으로 구성된	외래 키는 클러스터링되거나 클러스터되지 않은 인덱스를 자동으로 만들 수 없다. 그러나 외래 키에 인덱스를 수동으로 만들 수 있음
테이블 내 기본 키가 단 하나	테이블 내 외래 키 여러개 허용

 

* 외래 키에 NULL 값이 허용되는 이유에 대해서는 아래 링크를 참고해주세요!

https://stackoverflow.com/questions/7573590/can-a-foreign-key-be-null-and-or-duplicate

Can a foreign key be NULL and/or duplicate?

 

Compound Key, Composite Key (복합 키)

1. 복합 키에는 특정 레코드를 고유하게 인식할 수 있는 속성이 두 개 이상 있을 수 있습니다.

2. 각 속성이 데이터베이스 내에서 단독으로 고유하지 않을 수 있습니다.

3. 그러나 다른 속성 혹은 속성들과 결합하면 복합 키의 조합이 고유해집니다.

4. 복합 키의 목적은 테이블의 각 튜플을 고유하게 식별하는 것입니다.

5. Compound Key와 Composite Key의 차이는 Compound Key의 어떤 속성이든 외래 키가 될 수 있지만 Composite Key의 경우 Key의 일부 속성이 외래 키일 수도 있고 아닐 수도 있습니다.

 

Surrogate Key (대리 키)

1. 각각의 튜플을 고유하게 식별하는 것을 목적으로 하는 인공 키를 대리 키라고 합니다.

2

. 이러한 종류의 키는 자연적인 기본 키 즉 Natural Key가 없을 때 생성됩니다.

3. 대리 키는 보통 정수이고 어떤 의미도 갖지 않는다. (예를 들자면 sequence)

4. 대리 키 생성 조건

• Primary Key로 설정할 속성이 없을 경우
• Primary Key가 너무 크거나 복잡한 경우

 

Natural Key (자연 키)

1. Surrogate Key와 달리 테이블을 이루는 속성들 가운데 의미를 담고 있는 Candidate Key를 자연 키라고 합니다.

 

자연 키 vs 대리 키

1. 자연 키는 그 자체가 의미를 갖는 검색이 될 수 있지만, 대리키는 레코드의 기본키를 이용한 수정, 삭제, 외래키 참조에 효율적입니다.

2. 자연키는 자신이 정보를 가지고 있으므로, 테이블에서 자연 키로 검색하는 경우가 대부분이고 수정, 삭제, 외래 키 참조가 없다면 자연 키를 인덱스로 사용하는 것이 낫습니다.

3. 일반적으로 대체키의 도입을 고민하는 경우는, 자연 키가 숫자로 이루어지지 않는 경우입니다. (만약 자연키가 숫자로 있다면 당연히 대체키를 도입할 필요가 없습니다.)
4. 만약 자연키가 문자열이거나 둘 이상의 속성 집합으로 이루어진 복합키로 만들어야 한다면 대체키를 고려해봐야 합니다.
5. integer index와 string index의 성능을 비교했을 때 당연히 integer index가 빠릅니다. 따라서 일반적인 경우 대리 키를 사용하는 것이 낫습니다. (검색의 성능도 빠르고 크기도 작기 때문에 Block IO도 적게 발생할 가능성이 높습니다.)

 

각각의 키들에 대한 예시

 

 

각 키들에 대한 예시는 위 테이블을 참고해주세요!

예시를 쉽게 이해하기 위해 테이블 사진에 마우스를 올린 뒤 Highlight 버튼을 활성화해주세요.

 

기본키 예시

1. users 테이블의 id

2. products 테이블의 id

3. merchants 테이블의 id

4. countries 테이블의 code

5. orders 테이블의 id

6. merchant_periods 테이블의 id

 

대체키 예시

1. countries 테이블의 name

 

후보 키 예시

앞선 기본키와 대체키 예시로 든 속성들

 

슈퍼 키 예시

1. 후보 키 예시들

2. users 테이블의 {id, full_name}

3. countries 테이블의 {code, name}

...

 

* 2번처럼 고유 속성이 아닌 full_name과 고유 속성인 id를 묶어도 되고, 3번처럼 고유 속성인 code와 name으로 묶어도 됩니다. 즉, 최소성을 보장하지 않아도 됩니다.

 

외래 키 예시

1. merchants 테이블의 country_code (countries 테이블의 code 참조)

2. order_items 테이블의 order_id (orders 테이블의 id 참조), product_id (products 테이블의 id 참조)

...

 

복합 키 예시

Compound Key: order_items 테이블의 {order_id, product_id}

Composite Key: order_items 테이블의 {order_id, quantity}

 

* Compound Key의 어떤 속성이든 foreign key, Composite Key 같은 경우 foreign key일 수도 아닐 수도

 

대리 키 예시

1. users, products, merchants, orders, merchant_periods 테이블의 id

 

* 의미가 딱히 없는 sequence 같은 키

 

자연 키 예시

1. countries 테이블의 code

 

* 나라 코드 같은 경우 각각 고유의 의미를 가지고 있음

 

[출처]

https://www.guru99.com/dbms-keys.html

https://bunhere.tistory.com/45

http://blog.naver.com/PostView.nhn?blogId=qbxlvnf11&logNo=221198309383&parentCategoryNo=&categoryNo=34&viewDate=&isShowPopularPosts=false&from=postView

https://jhnyang.tistory.com/71?category=817647

https://dbdiagram.io/d/5ee02f0a9ea313663b3a4da5