[database] Relational Database(Primary key와 Foreign key)

본 게시글은

서울대학교 데이터사이언스대학원 이상원 교수님의

데이터사이언스 응용을 위한 빅데이터 및 지식관리시스템 수업을

학습을 목적으로 재구성하였습니다

---

저번 시간 수업에 이어서

DDL과 DML을 잠깐 살펴본 뒤

Primary key와 Foreign key에 대해서 자세하게 살펴본다

 

 

 

DDL은 table을 만드는 등의

역할을 하는 언어다

 

CREATE TABLE, CREATE VIEW, CREATE INDEX와 같은 것들이

DDL에 속한다

 

 

 

DML은 table 내의 데이터들을 조작하는 언어이다

Insert, Delete, Update와 같은 것들이 있다

 

 

특정 조건을 filter 걸어 데이터들을 조회하는

select문도 대표적인 DML이다

 

 

 

Integrity Constraint, 줄여서 IC에 대해서 알아보자

 

한국어로는 무결성 제약조건이라고 하는데

IC는 어떤 조건을 만족시켜야 한다는 constraint이다

 

IC는 데이터베이스에서 모든 Instance가 준수해야하는 조건들인데

예를 들면 integer값만 들어와야하는 column에는 integer만 넣어야한다는 조건도

domain constraint로 IC에 해당한다

 

이것 이외에도 우리가 오늘 집중적으로 배울 내용인

primary key, foreign key가 있다

 

앞에서도 말했지만 database는 반드시

IC를 만족해야한다

 

 

이러한 IC는 누가 지켜야할까?

 

사용자가 지켜야할까, DB를 사용하는 프로그램이 지켜야할까

아니면 DBMS가 지켜야할까?

 

결론은 DBMS가 IC를 지키는게 가장 효율적이다

 

사용자는 실수할 확률이 높으며

어플리케이션이 관리를 하면 프로그램의 복잡도가 올라가지만

DBMS는 데이터베이스 자체에서 IC를 관리하도록

설정할 수 있기 때문이다

 

DBMS가 IC를 체크하고 관리하게 되면

데이터는 더욱 현실세계의 의미를 정확하게

반영할 확률이 높게 되고

사용자나 개발자의 부담이 줄어들게 된다

 

 

 

table을 만들 때 필요한 IC는 명세할 수 있다

 

그리고 필요하다면 IC는 enable할수도 disable 할 수도

change 할 수도 있다

그럼 DBMS는 어떤 IC가 있는지 저장하고 관리하게 된다

 

따라서 table에 어떤 tuple이 update가되거나

insert가 될 때

해당 tuple이 IC를 만족하는지 확인하고

만족하지 않는다면 에러를 띄우게 된다

 

 

 

IC의 대표 주자 중 하나인

Primary Key에 대해서 알아보자

 

위 ppt의 예제를 보면 5개의 attribute가 있다

여기서 key의 역할을 할 수 있는건

학번의 역할을 하는 sid이다

 

key는 각 tuple을 distinct하게 구별할 수 있게 하는

두 개 이상의 field인데,

위 예제에서는 sid 혹은 sid + name과 같은 것들이

key가 될 수 있다

 

이러한 key가 1개 이상이면 이런 key들이

primary key들의 후보가 될 수 있다고 해서

candidate key라고 부른다

 

그 candidate key 중에서 한 개를 선택해서

key로 사용하면 그 친구가 primary key(PK)가 되는 것이다

 

위 예시에서는 sid를 pk로 사용한다고 한다

그럼 table을 만들 때 sid가 pk라고

사용자가 직접 명세를 한다

 

이렇게 pk를 설정하는 이유는

동일한 tuple이 있으면 안된다는

entity integrity를 보장하기 위해서이다

 

 

Primary Key와 Candidate Key를 설정하는

SQL문을 한 번 살펴보자

 

어떤 학생이 어떤 강의를 수강했는지 저장하는

Enrolled 라는 table을 생성한다고 한다

 

table을 create할 때 위와 같이 해주는데

여기서는 pk를 sid와 cid의 조합으로 해주기로 했다

그럴 때는 PRIMARY KEY(sid, cid)와 같이

작성해주면 된다

 

그 다음 그 밑의 DDL을 또 살펴보자

이번에는 sid로만 pk를 설정해줬다

그런데 밑에 UNIQUE라고 새로운 부분이 있는데

이는 pk는 sid지만

cid와 grade의 조합에 관해서도

모든 tuple들은 달라야한다고 표기하는 것이다

 

 

oracle에서는 user_constraints라는 table이 자체적으로 있는데

이는 해당 table에 어떤 constraint가 있는지

확인할 수 있는 table이다

 

oracle에서는 pk에 대해서 index table을 자동으로 생성한다

그래서 select로 pk에 대해서 검색할 때

index table을 이용해서 검색한다

 

pk는 중복이 되면 안되기 때문에 index table을 생성하는 것인데

pk가 있는 table에 데이터가 update 혹은 insert 될 때마다

DBMS는 기존의 row들을 돌면서

동일한 pk가 있는지 검사해야하는데

index table이 없으면 전체를 탐색하며 체크해야한다

 

그 효율성을 높이기 위해서 pk에 대해서

index table을 따로 생성하는 것이다

 

 

이제 Foreign Key에 대해서 알아보자

Primary Key는 Entity Integrity를 지키기 위해서 였다면

Foreign Key는 Referential Integrity를 지키기 위해서다

 

foreign key는 참조할 때 쓰는 key이며

닽일이 아닌 복수의 key도 가능하다

 

foreign key는 logical pointer의 역할을 하는데

참조 Key이기 때문에

값을 logical하게 가리키는 것을 말한다

 

 

 

위는 Foreign Key의 SQL 예시이다

 

Enrolled Table을 만들 때

table의 sid는 Students의 sid를 참조한다

 

여기서 Enrolled table에 새로운 tuple이 insert된다고 가정하자

sid와 cid의 조합이 pk이기 때문에

두 attribute의 조합이 distinct 하다면 pk는 만족시키지만

만약 sid가 Students table에 없는 sid라면

foreign key를 만족시키지 못한다

 

따라서 pk인 sid, cid는 null 값일 수 없고

foreign key도 역시 null 값이 들어오면 안된다

 

pk에 대해서는 자동으로 index table을 만드는데

foreign key에 대해서는 index table을 만들지는 않는다고한다

 

 

foreign key는 같은 table의

내부에 있는 것을 참조할 수도 있다

이런걸 self-referencing이라고 한다

 

위 ppt에서는 MGR는 EMPNO인 EMP를 참조하고

EMPNO는 이 table의 pk이다

 

 

 

FK가 insert, delete 되면 어떻게 될까?

Referential Integrity를 지키기 위해서

DBMS는 어떤 정책을 가지고 있을까?

 

DBMS가 parent table의 pk

즉, foreign key와 관련된 tuple을

delete 할 때

취할 수 있는 action은 총 4가지가 있다

 

1. 해당 key를 갖고있는 row를 모두

cascade 하게 삭제하기

2. parent table의 pk를 갖고있는 tuple을 삭제하지 못하게 하기

(NO ACTION임)

3.  그냥 참조하고 있는 부분을 null로 만들기

4. 삭제될 때 새로 설정할 default value 설정하기

 

 

위와 같은 4가지 정책들 중에서

우리가 어떤 정책을 선택할지

SQL에 정의해줄수있다

 

ON DELETE CASECADE를 하는 경우

parent table의 deletion에 따라서

foreign key가 설정되어있는 table도 삭제된다

 

아무것도 설정해주지 않는다면

삭제가 안되게 하는 NO ACTION이

default로 설정되어있다

 

ON UPDATE도 parent table의 pk가

update 될 때의 정책을 정의해주는 것이다

 

oracle에서는 delete는 cascade가 되지만

update는 cascade가 안된다고 한다

 

DB마다 이런 규칙들이 조금씩 다르다

 

 

약간 깊은 내용인데 관심이 있으면

읽어보라고 하셨다

 

결론은 fk에서는 index table을 자동으로 생성하지 않는다고 한다

 

 

이번에는 매 transaction마다 IC를

어떻게 체크하는지에 대해서 알아보자

 

대부분의 경우에 default로 IC는

transaction이 발생할 때마다

즉시(IMMEDIATE)하게 체크한다

 

하지만 이것을 사용자가 임의로

delay 시킬 수 있다

이런걸 deferred mode라고 하는데

ppt에 나와있는 예시의

SET CONSTRAINT Foo DEFERRED는

지금부터 Foo table에 대해서는 IC check를

delay 시켜달라는 뜻이다

 

그래서 오른 쪽의 transaction이 commit이 될 때 까지

IC check가 delay된다

 

 

이제 SQL에 대한 구체적인 내용으로 넘어간다

 

SQL은 뭐하는 언어냐?

Structured Query Language의 약자인 SQL은

데이터를 조작하는 언어다

 

 

SQL은 언제 처음 만들어졌을까?

IBM의 SystemR이라는 prototype을 1970년대에 만들면서

사용자가 terminal로 입력해서 데이터를 조작할 수 있도록 만들었는데

이것이 지금 SQL의 시초가 되었다

 

이것이 SQL의 standard가 되었고

지금 현재까지 발전하게 되었다

 

 

SQL의 예시이다

이미 많이들 알고 있겠지만

위와같이 SELECT문을 써서

필요한 데이터만 조회한다

 

*을 쓰면 모든 field가 다 출력되고

아니면 필요한 field만 선택할 수도 있다

 

 

구체적인 DBMS의 Query 수행에 대한 내용이다

복수의 WHERE 절을 사용했을 때

DBMS 내부에서는 어떤 순서로 해당 조건들을 처리할까?

 

이런 것들이 구체적인 SQL에 대한 내용인데

여기서부터는 다음시간에 배운다고 한다 ^^,,,

 

 

 

여기서 이번주 DB수업 내용 정리도 마무리-!