[Oracle SQL] 직접 만들어보며 배우는 SQL 기본키(Primary Key) VS 외래키(Foreign Key)

소개

6개월차 신입 데이터 분석가입니다. SQL에 대해 조금 더 깊게 이해하고자 공부를 시작합니다. 

 

www.yes24.com/Product/Goods/64391533

불친절한 SQL 프로그래밍

본 시리즈는 기본적으로 해당 도서를 참조하여 작성되었습니다.

아래와 관련된 키워드로 검색해보시면 환경세팅 관련 많은 도움을 받으실 수 있습니다.
- oracle sql developer scott hr
- oracle sql developer 설치
11g 설치는 https://data-make.tistory.com/2 해당 링크가 설명이 잘되어 있습니다.

환경은 Oracle SQL Developer에서 제공해주는 hr과 scott DB를 사용합니다. 해당 DB에서 기본적으로 제공해주는 테이블이 있고 제가 직접 테이블을 업로드하거나 생성할 수도 있습니다. 

 

gibles-deepmind.tistory.com/5

 

SQL 관련 포스팅 링크를 모아둔 아카이브 페이지가 있습니다. 

본 시리즈의 기본적인 문법은 Oracle SQL입니다. 현업에서 몇개월 간 종사해본 입장에서 DB별 SQL 문법 차이는 그렇게 크지 않습니다. 문법보다는 원리에 집중하시면 좋을 것 같습니다. 

글의 목표

1. 샘플 테이블 두개를 구성해봅니다.
2. 데이터의 구조적인 특징에 대해서 생각해봅니다.
3. 두 테이블을 통해 어떤 추론을 할 수 있는지 생각해봅니다.
4. 기본키와 외래키의 존재 이유에 대해 생각해 봅니다.

---

1. 샘플 테이블 구성

-- 날짜 표시 형식, 지정해준 포맷으로 날짜 데이터가 출력된다.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

/*user_info 테이블 생성*/
CREATE TABLE user_info(
    personID NUMBER NOT NULL,
    register_Date DATE,
    personName VARCHAR2(50),
    address VARCHAR2(50),
    age NUMBER,
    PRIMARY KEY(personID)
);

INSERT INTO user_info(personID, register_Date, personName, address, age)
VALUES(1, '2010-01-01', '오상식', '서울', NULL);
INSERT INTO user_info(personID, register_Date, personName, address, age)
VALUES(2, '2012-03-18', '천관웅', '강릉', 28);
INSERT INTO user_info(personID, register_Date, personName, address, age)
VALUES(3, '2013-07-15', '조동민', NULL, 29);
INSERT INTO user_info(personID, register_Date, personName, address, age)
VALUES(4, '2016-10-23', '김동식', '부산', 35);
INSERT INTO user_info(personID, register_Date, personName, address, age)
VALUES(5, '2019-06-13', NULL, '대전', 28);

어디선가 친숙한 이름이 보인다. 오상식?

http://program.tving.com/tvn/misaeng/13/Contents/Html?h_seq=2

미생 영업3팀의 등장인물들을 이용하여 간단한 유저 정보 테이블을 생성했다. 그런데, 여기 스파이가 한명 숨어있는데 그건 바로 나다. 참고로 2013-07-15는 나의 입대일이다. 군대에 있을때는 전역만 하면 모든게 해결될 줄 알았는데 먹고 살기는 참 쉽지 않다.

 

각설하고 데이터의 구조에 대해 살펴보자.

personID NUMBER NOT NULL
register_Date DATE
personName VARCHAR2(50)
address VARCHAR2(50)
age NUMBER
PRIMARY KEY(personID)

본 테이블은 크게 personID, register_Date, personName, address, age 이렇게 5개의 컬럼으로 구성되어 있다. 뒤따라 오는 NUMBER, DATE, VARCHAR2는 데이터 타입을 지정해주는 명령어이다. 우선은 크게 신경쓰지 말자. 

 

select * from user_info;

짠 위와 같이 고객 테이블 정보가 구성되었다. 다음 테이블로 넘어가보자.

/*purchase_order 테이블 생성*/
CREATE TABLE purchase_order(
    personID NUMBER NOT NULL,
    orderID NUMBER NOT NULL,  
    orderDate DATE,
    Reciever VARCHAR2(50),
    order_address VARCHAR2(50),
    product_name VARCHAR2(50),
    price NUMBER,
    quantity NUMBER,
    PRIMARY KEY(orderID),
    CONSTRAINT FK_personID FOREIGN KEY (PersonID) 
    REFERENCES user_info(personID)
    );
    
INSERT INTO purchase_order(personID, orderID, orderDate, Reciever, order_address, product_name, price, quantity)
VALUES(1, 100000001, '2020-10-05 10:30:22', '김부련', 'LA', '흑마늘즙', 100000, 1);
INSERT INTO purchase_order(personID, orderID, orderDate, Reciever, order_address, product_name, price, quantity)
VALUES(3, 300000001, '2017-08-03 17:21:35', '조동민', '분당구', '치닝디핑', 120000, 1);
INSERT INTO purchase_order(personID, orderID, orderDate, Reciever, order_address, product_name, price, quantity)
VALUES(3, 300000002, '2019-02-22 08:37:38', '조동민', '분당구', '마우스', 11000, 1);
INSERT INTO purchase_order(personID, orderID, orderDate, Reciever, order_address, product_name, price, quantity)
VALUES(5, 500000001, '2019-09-30 18:11:23', '장그래', '대전', '맥북', 2000000, 1);
INSERT INTO purchase_order(personID, orderID, orderDate, Reciever, order_address, product_name, price, quantity)
VALUES(5, 500000002, '2020-02-22 19:22:16', '장그래', '대전', '아이패드', 1000000, 1);

구매 정보 테이블이다.

 

본 테이블은 크게 personID, orderID, orderDate, Reciver, order_address, product_name, price, quantity 8개의 컬럼으로 구성되어 있다.

SELECT * FROM purchase_order;

짠 구매 정보 테이블까지 생성하였다. 이렇게 총 두 개의 샘플 테이블을 생성하였다.

---

2. 데이터의 구조적인 특징에 대해 생각해보기

각 컬럼들이 어떤 특징을 가지고 있는지 한번 살펴보자. Reciever(수취인), Order_Address(주소)등은 이름이 충분히 직관적인 컬럼이니 넘어가겠다.

PERSONID와 ORDERID를 한번 살펴보자.

자세히 살펴보니, 주문 번호는 맨 앞자리가 고객 번호로 시작하고 주문 횟수당 숫자가 하나씩 커지는 구조를 가지고 있다. 굳이, 여기서 이런 얘기를 꺼낸 이유는

모든 데이터에는 구조적인 특징이 있기 때문이다.
이유 없는 데이터는 없다. 이유 없는 데이터가 있다면 그 데이터를 제외하고 생각하는 것이 추론에 도움이 될 수 있다.

---

3. 두 테이블을 통해 정보 추론하기

select * from user_info;

SELECT * FROM purchase_order;

상단의 user_info 테이블에는 null 값으로 비어 있는 부분이 있다. 여기에는 다음의 가능성이 있을 수 있다.

가입 시 고객의 부담을 줄이기 위해 최소한의 정보만 기입하도록 한 경우일 수 있다. 여기에는 주민 번호가 빠져있지만, 이름과 주민 번호만 기입하고 나머지 정보는 선택 사항으로 받을 수도 있다.

위 내용을 어떻게 알 수 있을까?

/*user_info 테이블 생성*/
CREATE TABLE user_info(
    personID NUMBER NOT NULL,
    register_Date DATE,
    personName VARCHAR2(50),
    address VARCHAR2(50),
    age NUMBER,
    PRIMARY KEY(personID)
);

NOT NULL이 어디에 있는가? NOT NULL은 personID에만 있다. 즉, 나머지 값들은 굳이 적고싶지 않으면 비워도 되는 정보일 수 있다.

 

여기서 생각해야할 것은 빠져서 안되는 정보에는 반드시 NOT NULL을 붙여야 한다는 것이다.

 

user_info에서 조동민 고객의 주소가 빠져있는데 주문 테이블을 살펴보니 분당구로 두번 주문한 기록이 있다. 즉, 조동민 고객의 주소는 분당구라고 생각해볼 수 있다. 

 

또한, user_info에서 5번 고객의 이름이 NULL로 되어 있는데 purchase_order 테이블에서 5번 고객의 주문 기록에 수취인이 장그래로 되어 있는 것을 확인할 수 있다. 즉, 5번 고객의 본명은 장그래라고 추론해볼 수 있을 것이다.

 

(실무에서 무턱대고 이렇게 하면 안된다. 이것은 단지 예시일 뿐)

---

4. 외래키와 기본키의 존재 이유

1~3번 과정을 거친 이유는 외래키와 기본키의 존재 이유에 대해 생각해보기 위함이다. 외래키든 기본키든 열쇠인건 매한가지이다. 우선, 열쇠라는 것에 대해서 생각해보자.

 

https://commons.wikimedia.org/wiki/File:Standard-lock-key.jpg

 

요즘이야 대부분 집들이 비밀번호 도어락을 사용하지만, 주택에 사셨던 분들은 이런 열쇠를 하나씩 들고 다니셨을 것다. 열쇠의 가장 큰 특징은 대응이다. 대응이라는 말을 너무 어렵게 생각하지말고 예시를 하나 생각해보자.

 

예를 들어, 우리집 열쇠가 남의 집 대문도 열 수 있다면? 반대로 남의 집 열쇠가 우리집 대문도 열 수 있다면? 정말 끔찍할 것이다. 반대로, 원룸 같은 경우 집주인이 모든 원룸을 열 수 있는 마스터키를 들고 있는 경우가 있다. 남용이 되어 문제가 되는 경우도 있지만 어쨋든

키는 일반적으로 어떤 속성과 속성을 대응시키는 역할을 한다.

결론적으로 외래키와 기본키는 어떤 속성과 속성을 대응시키기 위해 존재한다. 조금 더 유식한 말로는 "식별"이라는 단어를 쓸 수 있다. 영어로는 Identify!

4-1. 기본키(Primary Key)

기본키는 우리집 열쇠라고 생각하면 된다. 이게 무슨 말이냐면, 우리집 문은 우리집 열쇠만으로 열 수 있어야 한다는 뜻이다. 간단한 예시를 생각해보자.

원룸 호수	키 이름
101	A
201	B
301	C
401	D

이렇게 각 호수별로 각각 다른 키를 사용해야 한다.

즉, 테이블에서 기본키의 역할은 각 행을 고유하게 해주는 것이다.

select * from user_info;

user_info 테이블을 다시 살펴보자. 2번 고객과 5번 고객의 나이는 동일하다. 하지만, 2번 고객과 5번 고객은 personID로 구분 된다. 즉, personID는 user_info 테이블의 고유키이다.

/*user_info 테이블 생성*/
CREATE TABLE user_info(
    personID NUMBER NOT NULL,
    register_Date DATE,
    personName VARCHAR2(50),
    address VARCHAR2(50),
    age NUMBER,
    PRIMARY KEY(personID)
);

 

기본키는 보통 TABLE의 마지막에 PRIMARY KEY(열 이름)을 추가하여 지정한다. 한 가지 잊지 말아야 할 것은 기본키는 반드시 NOT NULL이어야 한다. 

4-2. 외래키(Foreign Key)

Foreign은 "외국"이라는 뜻이다. 즉, 외래키는 다른 테이블의 키를 의미한다. 쉽게 말해 남의집 열쇠이다. 

/*purchase_order 테이블 생성*/
CREATE TABLE purchase_order(
    personID NUMBER NOT NULL,
    orderID NUMBER NOT NULL,  
    orderDate DATE,
    Reciever VARCHAR2(50),
    order_address VARCHAR2(50),
    product_name VARCHAR2(50),
    price NUMBER,
    quantity NUMBER,
    PRIMARY KEY(orderID),
    CONSTRAINT FK_personID FOREIGN KEY (PersonID) 
    REFERENCES user_info(personID)
    );

해당 테이블에서 고유키는 주문 아이디이다. -> PRIMARY KEY(orderID)

 

    CONSTRAINT FK_personID FOREIGN KEY (PersonID) 
    REFERENCES user_info(personID)

 

외래키는 위와 같이 지정했는데 문법은 너무 신경쓰지 말고 외래키가 할 수 있는 역할을 생각해보자. 

SELECT * FROM purchase_order;

select * from user_info;

목차 3에서 우리가 null값에 대해서 추론을 할 수 있었던 이유는 두 테이블이 PERSONID 값으로 연결되어 있었기 때문이다. 

간단하게 생각하자. 외래키는 서로 다른 두 테이블을 연결하는 역할을 한다.

4-3. 참조(REFERENCES)

외래키를 상속한다는 표현을 많이 사용한다. 명확한 의미는 아니라고 생각해서 다른 테이블을 참조했다고 하겠다. 그리고, 부모 테이블 / 자식테이블 이라는 용어를 많이 사용한다. 

부모 테이블 : 참조된 테이블, REFERENCES, USER_INFO
자식 테이블 : 부모 테이블을 참조한 테이블, PURCHASE_ORDER

문법은 다음과 같다.

 

    CONSTRAINT FK_personID FOREIGN KEY (PersonID)  
    REFERENCES user_info(personID)

 

쉽게 말해 FOREIGN KEY를 PersonID로 사용할 것인데 그 속성을 user_info 테이블에서 참조하겠다는 뜻이다. 여기서 두 가지 재밌는 현상이 발생한다.

1. 부모 테이블의 기본키(PK)에 없는 값을 자식 테이블에 추가하면 에러가 발생 한다.

-- 에러 발생
INSERT INTO purchase_order(personID, orderID, orderDate, Reciever, order_address, product_name, price, quantity)
VALUES(6, 600000001, '2020-02-23 17:42:16', '안영이', '서울', '데스크탑', 3000000, 1);

무결성 제약조건이라는 단어는 너무 신경쓰지 말자. 무결성은 좀 더 공부해서 다음에 포스팅 하겠다.

 

왜 이런 에러가 발생했을까?에 대해서 생각해보자. 왜냐하면, personID 6번 고객은 user_info 테이블 즉, 참조 테이블에 존재하지 않기 때문이다.

select * from user_info;

2. 자식 테이블이 있는 한 부모 테이블을 삭제할 수 없다.

DROP TABLE user_info;

정확히 말하면 부모테이블의 기본키를 참조한 자식테이블이 있는 한 이다.

 

4-4. 왜 기본키(PK)를 설정해야 하는가?

1. 데이터의 중복을 방지한다.

이 점은 기본키 항목에서 충분히 설명했으니 넘어가겠다.

2. 기본키는 -> 인덱스가 되고 -> 인덱스를 사용하면 속도가 빨라진다.

Oracle enforces a UNIQUE key or PRIMARY KEY integrity constraint by creating a unique index on the unique key or primary key. This index is automatically created by Oracle when the constraint is enabled

- https://docs.oracle.com/cd/A64702_01/doc/server.805/a58397/ch14.htm

오라클 공식문서에 따르면 단일키 / 기본키는 자동적으로 인덱스가 된다. 

 

Index가 되면 Full Table 스캔을 할 필요가 없어진다. 조금 어려운 말인데 다음과 같이 생각할 수 있다.

 

예를 들어서 내가 찾고자 하는 값이 포함된 컬럼이 인덱스 컬럼으로 지정되어 있다고 가정하자. 인덱스로 지정이 되었다는 의미는 내가 그 컬럼에서 특정 정보를 찾기 위해 컬럼에 포함되어 있는 모든 값을 찾을 필요가 없다는 의미이다. 나는 그 값이 어느 인덱스에 매핑이 되어있는지 알고 있기 때문에 원하는 값만 조건으로 입력해주면 그 값을 빠르게 찾을 수 있다.

 

무슨 말인지 모르겠으면 빠르다! 이정도만 알고 넘어가자. 인덱스 부분은 나중에 좀 더 자세히 다루겠다.

 

REFERENCE

docs.oracle.com/cd/A64702_01/doc/server.805/a58397/ch14.htm

Managing Indexes

www.youtube.com/watch?v=p3yJZH8_bsc&ab_channel=Studytonight