SQL 기본키, 외래키

기본 키(Primary Key) 는 SQL 데이터베이스에서
중요한 개념입니다.

 

기본 키는 데이터베이스 테이블 내의 모든 행에게 고유한 아이디를 제공해 줍니다.

기본 키

기본키란?

기본 키는 테이블 내의 각 행을 고유하게 하는 열입니다.

만약 여러분이 이미 어떤 열에 존재하는 값을 새로운 행을 만들어서 추가하고자 한다면, 이는 기본 키에 의해서 생성이 제한됩니다. 또한, 기본 키는 NULL 값을 받아들이지 않습니다.

 

기본 키를 생성하는 방법

citizen 테이블을 위한 기본 키를 정의하고 싶다면, 아래 쿼리문을 통해 테이블 및 기본 키를 생성할 수 있습니다.

CREATE TABLE citizen(
 social_security_number NUMBER PRIMARY KEY,
 last_name VARCHAR2(40),
 first_name VARCHAR2(40),
 born_date DATE
);

 

이렇게 social_security_number 을 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).

 

모든 테이블마다 기본 키가 존재해야 하지는 않습니다.
하지만, 데이터베이스의 제어 및 관리, 최적화를 위해서는 기본 키를 설정하는 걸 추천합니다.

 

이러한 방법 말고도 CONSTRAINT 를 사용하여 기본키에 이름을 지정할 수 있습니다.

CREATE TABLE country (
    country_id NUMBER,
    name VARCHAR2(100) NOT NULL,
    CONSTRAINT pk_country PRIMARY KEY (country_id) -- 기본키 이름 지정
);

 

이러한 기본키에 이름을 지정하는 방식은 테이블 설계 변경이나 제약조건 관리가 용이해 집니다.

예를 들어 아래와 같이 제약조건 이름을 부여하면, 필요할 때 해당 제약조건을 쉽게 삭제하거나 수정할 수 있습니다.

-- 제약조건 삭제
ALTER TABLE students DROP CONSTRAINT pk_students;

기본키가 숫자가 아닌 경우

이러한 테이블이 있을 때 airport_code 열을 기본 키로 정의한 airport 테이블을 생성하고 싶다면 아래와 같이 코드를 짜주시면 됩니다.

CREATE TABLE airport (
 airport_code CHAR(3) PRIMARY KEY,
 airport_name VARCHAR2(40),
 airport_city VARCHAR2(40)
);

 

이렇게 숫자가 아닌 데이터를 기본 키로 정의하는 것은 가능한 일입니다. 하지만 숫자로만 구성된 데이터를 기본 키로 설정하는 것이 SQL 의 실행 속도가 훨씬 좋습니다. 왜냐하면 데이터베이스는 문자보다는 숫자에 더 빨리 반응하기 때문입니다.

그래서 정말로 분석의 실행 속도가 중요한 업무라면 가상의 숫자 열을 생성하여 기본키로 설정하는 것을 추천합니다.

 

기본 키 여러개 설정하기

경우에 따라 1개 이상의 기본 키를 정의해야 할 수도 있습니다.

이런 경우를 다수 열 기본 키, 복합 기본 키 라고도 합니다. 이러한 경우는 현업에서 꽤나 많이 사용됩니다.

 

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

 

해당 문제를 해결하기 위해서는, reservation_id 를 추가 기본 키로 정의하면 해결할 수 있습니다.

하지만 똑같은 손님이 다른 시간에 예약을 하는 경우도 염두해 두고 해당 문제까지 생각해 기본 키를 정의해야 합니다.

CREATE TABLE reservation (
 customer_name VARCHAR2(40),
 reservation_day DATE,
 reservation_time TIME,
 number_of_people NUMBER,
 CONSTRAINT TEST_PK(기본키이름) PRIMARY KEY (customer_name, reservation_day, reservation_time)
);

 

이렇게 여러 개의 기본 키를 정의하는 방법은 1개의 기본 키를 정의하는 구문과는 살짝 다릅니다.

다수의 기본 키를 정의할 때는 기존에 열 이름 옆에 PRIMARY KEY 라고 적어준 것과는 달리, 따로 CONSTRAINT 로 기본키 이름을 설정해 준 다음 PRIMARY KEY 열을 만들어야 합니다. PRIMARY KEY 명령문을 적은 후 괄호 안에 기본 키가 될 열의 이름들을 나열해 주면 됩니다.

여러 열을 조합하여 PK 를 생성할 때는 반드시 CONSTRAINT 를 사용하셔야 합니다.

실사용 예제

CREATE TABLE enrollment (
    student_id NUMBER,
    course_id NUMBER,
    enrollment_date DATE,
    CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id)
);

 

INSERT INTO enrollment (student_id, course_id, enrollment_date) VALUES (1, 101, SYSDATE);
INSERT INTO enrollment (student_id, course_id, enrollment_date) VALUES (1, 102, SYSDATE); -- 허용
INSERT INTO enrollment (student_id, course_id, enrollment_date) VALUES (1, 101, SYSDATE); -- 오류 발생

 

외래 키

기본키와 함께 등장하는 개념으로 , 외래 키는 보통은 두개의 데이터베이스 테이블을 잇고자 기본 키와 함께 사용합니다.

아래의 그림과 같이 서로 연결시켜 주는 것이라고 생각하시면 됩니다.

외래 키란?

데이터베이스를 설계할 때, 하나의 테이블 만으로는 해당 테이블이 대표하는 데이터를 하나로 정리하기 힘들 수 있습니다.

이런 경우에는 테이블을 더 생성한 후, 외래 키를 사용하여 데이터베이스 내의 두 테이블을 이어주면 됩니다.

 

예를 들어, 나라 정보가 쌓이는 country 테이블이 있다고 가정해 봅시다. 여기에 하나의 나라에 여러개의 대도시에 관한 정보를 연결하고 사용하고 싶습니다. 그러면 country 테이블 내에서 각 나라마다 부여된 고유 아이디를 city 테이블에도 고대로 가져와 사용해야 합니다.

 

1. country 테이블에 기본 키 생성하기
DROP TABLE country;
CREATE TABLE country(
 country_id NUMBER PRIMARY KEY,
 name VARCHAR2(50),
 population NUMBER,
);

2. 기본 키와 외래 키를 정의한 새로운 테이블 생성
CREATE TABLE city (
 city_id NUMBER,
 name VARCHAR2(50),
 city_country_id NUMBER,
 CONSTRAINT fk_country FOREIGN KEY (city_country_id) REFERENCES country(country_id)
);

 

위에 코드처럼 외래 키를 연결하여 데이터베이스에게 두 테이블 간의 관계를 알려주고, 부모 테이블에 존재하지 않는 값을 자식 테이블에서 만들려고 하는 불상사를 막을 수 있습니다.

외래 키 제한 표현의 가장 첫 번째 역할은 두 테이블 같의 관계를 정의하는 일입니다.

 

외래 키 제한 표현 이해하기

두 테이블 사이에 외래 키가 존재하게 되면, 더 넓은 정보를 담고 있는 테이블은 부모 테이블이라고 보고 그보다 좁은 정보를 담고 있는 테이블을 자식 테이블이라고 봅니다. 이러한 자식 테이블 안의 값이 부모 테이블 안에 언제나 존재해야 합니다. 그렇지 않으면 당연히 오류가 발생합니다.

 

앞서 배웠던 것들은 부모 테이블과 자식 테이블에 새로운 값을 넣는 상황만 다루어 보았습니다.

하지만 역으로 삭제하는 상황이 생긴다면 어떻게 해야 할까요?? 어떤 값을 부모 테이블에서 지웠는데, 자식 테이블에선 그 값이 지워지지 않는다면 해당 데이터의 일관성은 유지되지 못할 것입니다.

이러한 문제 때문에 외래 키를 정의할 때 ON DELETE CASCADE 절을 추가로 적어줍니다.

 

외래 키를 정의할 때 ON DELETE CASCADE 절을 추가로 적어준다면 이런 일이 발생하는 것을 막을 수 있습니다.

CREATE TABLE city (
 city_id NUMBER,
 name VARCHAR2(50),
 city_country_id NUMBER,
 CONSTRAINT fk_city FOREIGN KEY (city_country_id)
   REFERENCES country(country_id) ON DELETE CASCADE
);

 

간단한 코드를 추가로 적어줌으로써 부모 테이블에서 특정 값이 삭제되면, 자동적으로 자식 테이블에서도 그 값이 지워지도록 설정할 수 있습니다. 이를 통해 데이터베이스 내의 데이터의 퀄리티를 유지할 수 있습니다.

 

실사용 예제

국가 테이블 생성

CREATE TABLE country (
  country_id NUMBER PRIMARY KEY,
  name VARCHAR2(100) NOT NULL
);

 

도시 테이블 생성

CREATE TABLE city (
  city_id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  city_country_id NUMBER,
  CONSTRAINT fk_city FOREIGN KEY (city_country_id) 
    REFERENCES country(country_id) ON DELETE CASCADE
);

 

데이터 삽입

INSERT INTO country (country_id, name) VALUES (1, 'USA');
INSERT INTO country (country_id, name) VALUES (2, 'Korea');

INSERT INTO city (city_id, name, city_country_id) VALUES (1, 'New York', 1);
INSERT INTO city (city_id, name, city_country_id) VALUES (2, 'Seoul', 2);

 

삭제

DELETE FROM country WHERE country_id = 1; -- "New York" 데이터도 같이 삭제됨.

 

만약 외래키를 삭제하고 싶다면 아래처럼 해주시면 됩니다.

ALTER TABLE city DROP CONSTRAINT fk_city;

 

만약 실수로 외래키를 테이블을 만든 뒤 추가하고 싶다면 어떻게 해야 할까요? 아래처럼 하시면 됩니다.

ALTER TABLE city ADD CONSTRAINT fk_city FOREIGN KEY(city_country_id) REFERENCES city (city_id);