SQL 기본 키에 대한 이해

기본 키(Primary Key)는 SQL 데이터베이스에서 중요한 개념입니다. 기본 키는 데이터베이스 테이블 내의 모든 행에게 고유한 아이디를 제공합니다. 만약 여러분이 SQL를 다루신다면, 기본 키가 무엇이며 기본 키에는 어떠한 값들이 저장되고 이들은 어떻게 생성할 수 있는지에 관해 아셔야 합니다. 그래서 이번 글에서는 기본 키에 관한 공부를 해보도록 하겠습니다.

 

테이블 내 행을 식별하는 법

SQL 데이터베이스는 데이터가 담긴 테이블을 가지고 있습니다. 그리고 그 테이블은 행으로 이루어져 있고요. 테이블 내의 각 행을 식별하기 위해서는 모든 행이 다른 값을 가지고 있는 열을 찾아야 합니다. 예를 들어, 만약 우리가 미국 시민에 관한 정보를 저장하는 테이블을 갖고 있습니다. 그렇다면, 모든 미국 시민을 식별하고자 social_security_number 열을 사용할 수 있을 것입니다. 다른 예시도 들어볼까요? 만약 특정 은행에서 발급된 통장의 정보를 저장하는 테이블을 갖고 있다고 가정해봅시다. 각 행마다 해당 은행에서 발급된 통장의 정보가 저장되어 있을 것이고, 그 많은 통장들을 식별하고자 account_number 열을 사용할 수 있을 것입니다. 하지만 지금 설명한 것처럼 모든 테이블이 이해하기 쉽게 그리고 명확한 기본 키를 갖추고 있지는 않습니다. 이에 관해서는 이번 글의 뒷부분에서 좀 더 자세히 말씀드리도록 하겠습니다.

 

기본 키란 무엇인가?

각 행마다 행을 식별할 수 있도록 전부 다른 값이 저장된 열을 찾으셨다면, 그 열을 가지고 기본 키를 만들 수 있습니다. 다시 한번 강조하자면, 기본 키는 테이블 내의 각 행을 고유하게 하는 열입니다. 고로, 기본 키는 모든 행이 고유한 값을 가지고 있는지 혹은 값이 비어있는 행이 있는지 확인할 수 있도록 합니다. 예를 들어, 여러분이 이미 어떤 열에 존재하는 값을 새로운 행을 만들어서 추가하고자 한다면, 이는 기본 키에 의해서 생성이 제한됩니다. 

또한, 기본 키는 NULL 값을 받아들이지 않습니다. 즉, 기본 키 열에는 NULL 값이 존재할 수 없습니다. 미국 시민의 정보를 저장하고 있는 테이블인 citizen 예시가 기억나십니까? 만약 social_security_number 열에 NULL 값이 포함된 행을 추가하고자 한다면, 이는 기본 키에 의해서 제지 당합니다. 다시 말해서, 기본 키는 기본 키가 되는 열의 행 값이 고유하고 비어있지 않도록 만들어 줍니다.

 

기본 키를 생성하는 법

아래와 같은 citizen 테이블을 만들고 싶습니다.

social_security_number	last_name	first_name	born_date
721071426	Doe	John	06-25-1912
211271298	Smith	Mary	01-23-1903

이제, 기본 키를 생성하는 SQL 구문에 관해 알아보도록 하겠습니다. 만약 citizen 테이블을 위한 기본 키를 정의하고 싶다면, 아래 쿼리문을 통해 테이블 및 기본 키를 생성할 수 있습니다.

CREATE TABLE citizen(
 social_security_number interger PRIMARY KEY,
 last_name varchar(40),
 first_name varchar(40),
 born_date date);

CREATE TABLE 구문 안에 PRIMARY KEY 절을 사용하여 어느 열이 기본 키인지 정의내려 주었습니다. 이렇게 기본 키를 정의한 후 social_security_number 열에 이미 존재하는 값을 새로운 행으로 추가하고자 한다면, 값을 추가하는 SQL 명령어인 INSERT는 작동하지 않을 것입니다. 그리고 아래와 같은 오류 메시지를 보여줄 것입니다.

INSERT INTO citizen VALUES (721071426, 'Kant' , 'Peter', '1920-09-22');

ERROR: duplicated key violation for primary key citizen_pkey 
DETAIL: Already existing key (social_security_number)=(721071426).

이쯤 돼서 제가 매번 받는 질문을 공유하고자 합니다. 모든 테이블마다 기본 키가 존재해야 할까요? 아뇨, 꼭 그렇지는 않습니다. 기본 키를 정의하지 않은 채 SQL 테이블을 생성할 수 있습니다. 하지만, 데이터베이스의 제어 및 관리, 최적화를 위해서는 기본 키를 생성하는 것을 추천드립니다.

 

기본 키가 숫자가 아닐 경우

지금까지 살펴본 예시는 기본 키가 숫자 데이터인 경우뿐이었습니다. 기본 키는 숫자 데이터 이외에도 다른 데이터 종류들을 허용합니다. 

예를 들어, 전 세계에 있는 공항에 관한 정보를 저장하는 테이블을 생성하고 싶습니다. 해당 테이블에 생성할 수 있는 열의 이름은 airport_name, airport_city, aiport_country, airport_code 정도 될 것 같습니다. 이중 airport_code 는 알파벳 3개로 구성돼 있는 각 나라마다 고유하게 부여받는 코드입니다. 따라서 이걸 해당 테이블의 기본 키로 정의하고자 합니다.

airport_code	airport_name	airport_city	airport_country
JFK	John Kennedy	New York	USA
LGA	La Guardia	New York	USA
CDG	Charles de Gaulle	Paris	France
BCN	El Prat	Barcelona	Spain

아래 쿼리문은 airport_code 열을 기본 키로 정의한 airport 테이블을 생성해 줍니다.

CREATE TABLE airport (
 airport_code char(3) PRIMARY KEY,
 airport_name varchar(40),
 airport_city varchar(40));

airport_code 가 총 3개의 문자로 구성되어 있기 때문에 데이터 종류를 char(3) 라고 적어주었습니다.

또 다른 예시도 살펴보도록 하겠습니다. 특정 주에 등록된 부동산 매물에 관한 테이블을 만들고 싶습니다. 각 매물은 2개의 알바펫과 8개의 숫자로 구성된 코드를 부여받고, 이는 각 매물마다 고유합니다. 따라서 property_id 를 기본 키로 정의할 수 있습니다. 

CREATE TABLE property(
 property_id char(11) PRIMARY KEY,
 property_district varchar(40),
 property_owner varchar(40),
 property_value numeric(10,2));

property_id 가 문자와 숫자의 조합이기 때문에 데이터 종류를 char(11) 라고 적어주었습니다. 

숫자 데이터가 아닌 기본 키의 또다른 예시를 보여드리고자 비행기 이야기를 다시 꺼내보도록 하겠습니다. 보통, 비행기는 문자와 숫자의 조합인 고유 코드를 부여받습니다. 그래서 우리 예시에서는 비행기마다 숫자와 문자의 조합으로 이루어진 총 10 글자의 코드를 부여받았다고 가정하겠습니다.

CREATE TABLE flight(
 flight_number char(10) PRIMARY KEY,
 airport_code_origin char(3),
 airport_code_destination char(3),
 flight_duration interval);

물론 숫자가 아닌 데이터를 기본 키로 정의하는 것은 가능한 일입니다. 하지만 숫자로만 구성된 데이터를 기본 키로 설정하는 것이 SQL의 실행 속도가 훨씬 좋습니다. 왜냐하면 데이터베이스는 문자보다는 숫자에 더 빨리 반응하기 때문입니다. 고로, 정말로 분석의 실행 속도가 정말로 중요한 업무라면 가상의 숫자 열을 생성하여 표면사의 기본 키로 설정하는 것을 추천드립니다.

방금 제시한 상황을 실제 쿼리문을 통해 살펴보도록 하겠습니다. flight 테이블에 숫자 열을 추가하여 그것을 기본 키로 사용하고자 합니다. 아래와 같이 쿼리문을 작성할 수 있습니다.

CREATE TABLE flight(
 flight_id interger PRIMARY KEY,
 flight_number char(10),
 airport_code_origin char(3),
 airport_code_destination char(3),
 flight_duration interval);

 

기본 키가 여러 개의 열로 존재할 수 있을까?

경우에 따라 1개 이상의 기본 키를 정의해야 할 수도 있습니다. 이런 경우를 다수 열 기본 키(multi-column primary keys) 혹은 복합 기본 키(composite primary keys)라고 부릅니다. 그리고 생각보다 이런 경우는 꽤나 흔하답니다.

예를 들어, reservation 이란 테이블이 있다고 가정해보겠습니다. 해당 테이블은 customer_name, reservation_day, reservation_time, number_of_people 열로 구성되어 있습니다. 손님의 이름을 기본 키로 삼고 싶지만, 오직 손님의 이름만 가지고는 기본 키로 삼을 수 없습니다. 그 이유는 같은 이름의 다른 손님이 존재할 수도 있고, 같은 손님이 여러 번 예약을 할 수 있으니까요. 기본 키가 되는 열은 행마다 중복되지 않는 값을 가져야 한다는 점, 기억하시나요? 그렇기 때문에 손님 이름만 가지고는 기본 키로 사용할 수 없습니다.

해당 문제를 해결하기 위해, 우리는 reservation_id 를 추가 기본 키로 정의할 것입니다. 손님 이름과 예약 날짜를 같이 보면 각 행마다 고유한 값이 저장될 수 있을 것 같습니다. 하지만, 만약 고객이 하루에 예약을 여러 번 했다면 어떻게 될까요? 점심에도 예약을 하고, 저녁에도 예약을 한 것입니다. 고객 이름도 같고 예약 날짜도 동일해서 우리가 기본 키로 설정한 2개 열의 값이 중복인 상황입니다. 이렇게 되면 데이터베이스는 저녁 예약에 관한 정보를 저장할 수 없게 됩니다. 이런 불상사를 막기 위해, 우리는 또 다른 열을 기본 키로 추가 정의해주어야 합니다. 예를 들면, 예약 시간(reservation_time)과 같은 값을 말이죠.

아래 결과는 우리가 customer_name 과 reservation_day 만 기본 키로 정의할 경우 테이블에 저장될 수 없는 데이터들입니다. 고객의 이름과 예약 날짜가 동일하므로 기본 키들이 고유한 값으로 구성되지 않았기 때문입니다. 

customer_name	reservation_day	reservation_time	number_of_people
John Doe	2020-09-20	11:30 AM	2
John Doe	2020-09-20	8:00 PM	2

여러 개의 기본 키를 정의하는 방법은 1개의 기본 키를 정의하는 구문과는 살짝 다릅니다. 다수의 기본 키를 정의할 때는 기존에 열 이름 옆에 PRIMARY KEY라고 적어준 것과는 달리, 따로 PRIMARY KEY 열을 만들어줍니다. PRIMARY KEY 명령문을 적은 후 괄호 안에 기본 키가 될 열의 이름들을 나열해 줍니다. 아래 쿼리문을 보시면 이해가 금방 되실 겁니다.

CREATE TABLE reservation (
 customer_name varchar(40),
 reservation_day date,
 reservation_time time,
 number_of_people integer,
 PRIMARY KEY (customer_name, reservation_day, reservation_time));

보통은 2개 정도의 타 테이블을 연결해야 하는 상황이라면, 특정 테이블에 다수의 기본 키를 정의해주면 좋습니다. 예를 들자면, 대학 데이터베이스가 있습니다. 해당 데이터베이스에는 course 와 student 테이블이 존재합니다. 각 course 마다의 학생 등록 수를 조사하기 enrollment 라는 테이블을 생성하고자 합니다. 해당 테이블은 student_id 열 (student 테이블에서 가져옴), course_id 열(course 테이블에서 가져옴), start_date 열(course 테이블에서 가져옴)로 구성해 줄 것입니다.

enrollment 테이블을 만들기 위해서는 여러 개의 기본 키가 필요합니다. 아마 course_id 와 student_id 를 동시에 기본 키로 정의해줘야 할 것입니다. 왜냐고요? 우선, course_id 만으로는 기본 키가 될 수 없습니다. 한 수업에 여러 명의 학생들이 등록할 것입니다. 그렇게 되면 동일한 course id 아래 studnet_id 만 다른 행이 여러 개 생성되는데, 전에도 말했듯이 기본 키는 각 행의 값이 고유해야 하니까요. '동일한 course_id' 가 생성될 상황부터가 잘못된 것이죠. 이와 비슷하게, student_id 만으로도 기본 키가 될 수 없습니다. 똑같은 학생이 여러 개의 course 에 등록을 할 것이기 때문입니다. 

아래 쿼리문은 지금까지 말한 enrollment 테이블을 생성하는 쿼리문입니다.

CREATE TABLE enrollment(
 course_id interger,
 student_id integer,
 start_date date,
 PRIMARY KEY (course_id, student_id));

 

글을 마무리하며

이번 글에서는 기본 키에 관해 공부해 보았습니다. 기본 키 말고도 다른 키가 있는데요. 바로 외래 키입니다. 다음 시간에는 외래 키에 관해 알아보도록 하겠습니다.

 

원문 : What is a Primary Key in SQL?