Slow walking man

앞선 포스트에서 노원구 주민 테이블을 가지고 이야기 했으니, 편의상 요번에도 주민 테이블을 가지고 예를 들어 보겠다..

상황은 이렇다.. 어느날 노원구에서 살고 있던 수지가 동작구로 이사를 간다고 했다. 이것을 테이블 관점에서 본다면, 노원구 주민 테이블에서 수지를 빼고, 동작구 주민 테이블에 수지를 넣는 작업이 진행되어야 한다.

1 DELETE FROM 노원구 주민 테이블 WHERE 주민테이블.주민이름='수지';

2 INSERT INTO 동작구 주민 테이블 VALUES ('수지');

그런데 이런 상황이 발생해 버리면 어떨까?

1번 상황을 진행하는 동안 알수 없는 에러로 1번줄을 수행하지 못하고 2번과정으로 넘어가 동작구 주민 테이블에 수지가 추가되었다. 그러면 이제 가상의 수지 한명이 동작구에 살게 된다. 그러나 이 동작구 수지의 실체는 없다.

뭐 이런건 단순한 실수로 넘어가 줄 수 있겠지만, 은행이라면 어떨까? 잘못된 오류의 금액이 몇억원이라면?

이런 문제점을 해결하기 위해 TRANSACTION은 복수의 명령어로 이뤄진 쿼리를 모두 실행하고 오류가 없을 때 비로소 실제 DB에 적용한다. 실행 도중에 하나의 오류라도 발견되면, 전체 과정중 일부가 성공했다 하더라도 실제 DB에는 반영하지 않는다.

따라서 동시에 진행되는 TRANSACTION작업은 서로에 의한 처리 결과를 처리가 완료되기 이전에 볼 수 없다. 일련의 모든 작업이 성공으로 끝나고 COMMIT이 이뤄 질 때 비로소 처리 결과가 DB에 반영되어 다른 작업들도 이 반영된 결과를 볼 수 있다.

Postgresql에서는 "BEGIN"과 "COMMIT"이라는 단어로 query를 감싸므로써 이런 TRANSACTION 기능을 사용 할 수 있다.

PRIMARY KEY

먼저 주 키 (PRIMARY KEY) 는 테이블에서

1. 레코드들이 가지고 있는 값들 중에 각 레코드들 별로 유니크하게 가지고 있는 항목

2. 중복이 불가능하고, NULL을 입력하는 것도 불가능하다.

예를 들어 국민의 개인정보를 관리하는 테이블에서는 주민등록번호가 이런 PRIMARY KEY역할을 한다고 볼 수 있겠다. 등록번호와 이름만 관리하는 임의의 주민 TABLE을 만들어보자면 아래와 같다. (주민등록번호를 PRIMARY키로 설정하겠다)

memID를 PRIMARY KEY로 설정하여 테이블을 생성했다. pgAdmin III에서 해당 테이블을 열어 보면 memID옆에 [PK]라고 PRIMARY KEY임을 표시하고 있다.

예를 들어서 위와같이 memid=1 인 아이유가 이미 테이블에 들어 있는데 같은 id로 수지를 입력하려고 하면 아래 output과 같이 PRIMARY KEY라서 중복된 키 값을 입력할 수 없다는 출력이 뜨면에 에러가 발생한다.

또한 memID를 NULL로 설정해놓고 입력을 할려고 해도, NULL이 허용되지 않는  PRIMARY KEY의 조건때문에 에러가 발생하게 된다.

FOREIGN KEY

FOREIGN KEY는 다음과 같은 경우라고 생각하면 된다.

일단 앞서 만든 주민 테이블이 노원구 주민 테이블이라고 생각해보자. 노원 도서관을 활용하는 사람들의 이용 실적 테이블을 만드는데 사실 노원 도서관은 노원 구민뿐 아니라 다른 구 시민도 사용한다. 따라서 노원구 사람들의 노원도서관 이용실적을 만들기 위해서는 도서관을 이용하는 사람들의 명부를 작성하되 그 사람이 노원구민일 때만 명부를 작성하면 된다.

이런 방법을 위해서는 명부를 작성할 때 그 사람의 주민 번호가 주민 테이블에 있는 번호인지 확인해야 한다. 이런 경우 주민 테이블의 memID를 FOREIGN KEY라고 한다.

그럼 실제로 사용실적을 작성할 테이블 usageTable을 만들어보자.

앞서 아이유, 수지, 박보영이 살고 있는 구의 주민 테이블을 다음과 같이 작성하였다.

그리고 아래 쿼리 문으로 usageTable을 만들었다. usageTable의 memID는 membershipTable의 memID 열에 REFERENCES를 두었다.

자.. 이제 테스트를 한번 해보자..

다른 구에 살고 있는 id 4번의 미란다 커가 노원구 도서관에 12월 5일에 왔다.

그래서 이용 실적에 넣어 보려고 하다보니까, 노원구 주민 테이블에 미란다 커의 주민등록 번호 4번이 없기 때문에 아래와 같이 에러가 발생하게 되었다.

그럼 다음 테스트를 보자..

다른 구에 살고 있는 테일러 스위프트가 노원도서관에 왔다. 그런데 테일러 스위프트가 외국인이다 보니 아직 주민등록 번호가 없단다. 그래서 다음과 같이 memID에 NULL을 입력했다.

결과는...? 

읭?

등록이 되었다.

사실 이건 의도한 바가 아니긴 한데, 이런 경우는 usageTable의 memid를 NULL값이 오지 못하도록 따로 설정을 해주어야 한다. 이것은 pgAdmin III에서 쉽게 할 수 있는데, usageTable의 column으로 가서 해당 memid의 NULLable항목을 체크해제 해주면 여기에 NULL을 넣은 레코드가 생성되지 못하게 막을 수 있다.

이런 개념을 maintainning data integrity라고 한단다. 형식상 잘못된 데이터를 허용하지 않는다는 개념이다.