즐겁게 배우는 SQL 8. 데이터 정의

jiffydev·2021년 7월 10일

sql

즐겁게 배우는 SQL

목록 보기

8/13

본 포스트는 박재호님의 유튜브 강의(링크)를 보고 일부 발췌하여 정리한 내용입니다.
사용된 자료, 샘플 데이터 등은 모두 SQLite Tutorial에서 확인할 수 있습니다.

1. 데이터 타입

이제 테이블을 직접 생성해야 하는데, 그 전에 테이블에 들어가는 데이터에 대해 정의를 해야 한다.
데이터를 정의하려면 우선 데이터의 타입을 알아야 하기 때문에 이에 대해 우선 소개하고자 한다.

sqlite에서 제공하는 데이터 타입은 비록 여타 오픈소스 DB에 비해 그 종류가 많지는 않지만(5가지), 기본적인 타입은 다 가지고 있으므로 기초를 알기에는 문제가 없을 것이다.

sqlite의 데이터 타입은 다음과 같다.

정수, 실수는 무한하지 않다는 점에 유의하자.

그리고 우리가 입력한 데이터의 타입을 typeof()함수를 통해 확인할 수도 있다.

SELECT
	typeof(100),
	typeof(10.0),
	typeof('100'),
	typeof(x'1000'),
	typeof(NULL);

2. 테이블 생성

이제 본격적으로 테이블을 생성해 보자.
이전 예제들에서 이미 테이블 생성 구문이 있기는 했으나, 좀 더 자세히 알아보도록 하자.

테이블 생성 구문은 다음과 같은 형태를 띈다.

CREATE TABLE [IF NOT EXISTS] [schema_name].table_name (
	column_1 data_type PRIMARY KEY,
   	column_2 data_type NOT NULL,
	column_3 data_type DEFAULT 0,
	table_constraints
) [WITHOUT ROWID];

테이블 이름을 넣는다. sqlite의 경우 테이블명은 sqlite으로 시작해서는 안된다는 규칙이 있다.
IF NOT EXISTS는 같은 테이블이 이미 존재할 경우 생성하지 않도록 해 주는데, 자주 사용하지는 않고 배치작업으로 테이블을 생성해야 할 때 멱등성(ideompotency)를 유지하기 위해 사용하는 정도이다.
테이블명 앞에 schema_name이 있는 것을 볼 수 있는데, 데이터베이스의 종류가 여러 개가 될 수 있기 때문에 이를 구별하기 위해 사용한다. 스키마는 메인 데이터베이스가 될 수도, temp 데이터베이스나 기타 데이터베이스가 될 수도 있다.
컬럼을 생성할 때는 이름, 데이터 타입을 명시하고 컬럼의 제약사항(PRIMARY KEY, UNIQUE, NOT NULL 등)을 나타낼 수 있다.
테이블 전체의 제약사항을 걸 수 있는데 PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK 등이 있다.
여기서 왜 PRIMARY KEY는 컬럼의 제약사항에도 있고 테이블의 제약사항에도 있는지 의문이 들 수 있다.
답은 PRIMARY KEY가 특정 컬럼과 컬럼을 합쳐야 되는 경우가 존재하기 때문에 테이블의 제약사항에도 존재할 수 있는 것이다.

이제 실제 테이블을 생성해보자. 생성하게 될 테이블들은 아래와 같은 관계를 가진다.

groups와 contacts는 N:M 관계로, 하나의 그룹(모임 등)은 여러 사람의 연락처를 가질 수 있고, 하나의 연락처도 여러 그룹을 가질 수 있다.

CREATE TABLE contacts (
	contact_id INTEGER PRIMARY KEY,
	first_name TEXT NOT NULL,
	last_name TEXT NOT NULL,
	email TEXT NOT NULL UNIQUE,
	phone TEXT NOT NULL UNIQUE
);

CREATE TABLE groups (
   group_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL
);

여기까지는 sql문을 보면 금방 어떤 내용의 테이블인지 이해가 될 것이다.
다만 다음의 중간 테이블을 생성할 때 머리가 아파올텐데, 어떻게 생성하는지 우선 확인해 보자.

CREATE TABLE contact_groups(
   contact_id INTEGER,
   group_id INTEGER,
   PRIMARY KEY (contact_id, group_id),
   FOREIGN KEY (contact_id) 
      REFERENCES contacts (contact_id) 
         ON DELETE CASCADE 
         ON UPDATE NO ACTION,
   FOREIGN KEY (group_id) 
      REFERENCES groups (group_id) 
         ON DELETE CASCADE 
         ON UPDATE NO ACTION
);

contact_id, group_id 필드를 만들고, 테이블의 제약사항을 보면
PRIMARY KEY가 위에서 설명한 것처럼 두 컬럼을 결합해 만든 것을 알 수 있다.

그리고 각 id를 FOREIGN KEY로 선언하는데, id는 원래 테이블을 참조하여, 원래 테이블에서 id가 삭제되면 중간 테이블에서도 삭제하고, 데이터가 갱신될 경우에는 아무 것도 하지 않도록 설정하였다.

3. 테이블 변경

테이블 변경이라고 해도 할 수 있는 것들은 매우 많다.
테이블/컬럼 이름을 바꿀 수도 있고, 컬럼을 추가하거나 삭제할 수도 있다.

사실 테이블을 변경하는 것은, 데이터가 많아질수록 성능 문제나 기타 여러 문제가 발생할 소지가 있기 때문에 되도록 하지 않는 것이 좋지만 살다 보면 할 수밖에 없을 때도 있을 것이다.
따라서 상용 데이터에서 테이블을 변경할 때는 최대한 심사숙고해서 변경하는 것이 좋다.

우선 간단하게 이름을 변경하는 sql문은 다음과 같다.

ALTER TABLE existing_table
RENAME TO new_table;

이름을 변경할 때 알아두어야 할 점이 몇 가지가 있는데,

ALTER TABLE은 데이터베이스 안에 있는 테이블의 이름만 바꾸는 것이지 다른 데이터베이스 안에 있는 테이블은 변경되지 않는다.
테이블과 연결된 데이터베이스의 인덱스나 트리거는 변경된 테이블을 따라간다. 따라서 기존 테이블을 그대로 놔둔다면 인덱스/트리거를 다시 설정해야 한다.
테이블을 view나 트리거가 참조하는 경우, view, 트리거의 정의도 변경해 주어야 한다.

새로운 컬럼을 추가하는 기본적인 형태는 아래와 같다.

ALTER TABLE table_name
ADD COLUMN column_definition;

여기서도 몇 가지 주의사항이 있는데,

당연하겠지만 새로운 컬럼은 UNIQUE나 PRIMARY KEY제약을 가질 수 없다.
이것도 당연하지만 NOT NULL제약을 걸었다면 NULL값이 있으면 안된다.
새로운 컬럼은 CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME을 기본값으로 가질 수 없다.
새로운 컬럼이 foreign key이고 foreign key 제약조건 체크가 활성화 되어 있다면, 새 컬럼은 기본값으로 NULL을 설정할 수 있어야 한다.

컬럼을 추가했다면 컬럼도 삭제할 수 있다.
그런데 컬럼을 추가하는 것이 별로 어렵지 않았던 반면, 삭제하는 것은 고려해야 할 점이 많다.

위의 모든 스텝을 밟아야 올바르게 컬럼을 삭제할 수 있다는 것이다. 갑자기 난이도가 올라가도 너무 올라간 것 같다.

요약하자면 제약조건 해제, 트랜잭션 시작, 인덱스 등 저장, 새 테이블 만들어서 기존 테이블 데이터 복사하고 기존 테이블은 drop, 인덱스/제약조건들 다시 설정하는 것이 방법이다.

이는 sqlite의 특성상 컬럼을 그냥 지우게 되면 데이터 자체의 형상이 망가져서 처리하기가 까다롭기 때문이다.

위 절차를 sql문 형태로 나타내면 다음과 같다.

-- disable foreign key constraint check
PRAGMA foreign_keys=off;

-- start a transaction
BEGIN TRANSACTION;

-- Here you can drop column
CREATE TABLE IF NOT EXISTS new_table( 
   column_definition,
   ...
);
-- copy data from the table to the new_table
INSERT INTO new_table(column_list)
SELECT column_list
FROM table;

-- drop the table
DROP TABLE table;

-- rename the new_table to the table
ALTER TABLE new_table RENAME TO table; 

-- commit the transaction
COMMIT;

-- enable foreign key constraint check
PRAGMA foreign_keys=on;

테이블을 변경한다는 것이 생각보다 쉽지 않은 일임을 알 수 있을 것이다.

4. 테이블 제거

테이블을 생성, 변경했다면 제거해야 할 일도 분명 발생한다.
제거하지 않고 그대로 남겨둘 수도 있지만 유지보수상 짐이 될 가능성이 높은데 굳이 남길 필요는 없을 것이다.

다만 한 번 drop한 테이블은 복구할 수 없고, 백업에서 다시 가져와야 하기 때문에 명령을 실행하기 전에 항상 두번 세번 생각해야 한다.

테이블을 제거하는 책임은 무겁지만 실행하는 명령은 가볍다.

DROP TABLE [IF EXISTS] [schema_name.]table_name;

5. 청소

데이터베이스를 청소한다는 것은 무엇이고 왜 필요할까?
데이터베이스에서 drop, delete로 데이터를 삭제해도 데이터베이스 파일의 크기는 변하지 않는다. 왜냐하면 향후 사용을 위해 실제로 지우는 것이 아니라 지웠다고 표시만 해 놓은 상태이기 때문이다.
그리고 insert, delete 등을 테이블에서 수행하면 설정해 놓은 인덱스가 파편화된다.
이러한 결과들이 성능에도 영향을 미치기 때문에, vacuum을 사용해 데이터베이스를 최적화하는 것이 중요하다.

다만 vacuum은 임시 데이터베이스 파일을 만들어 조각모음을 수행하고, 다시 원본 데이터베이스에 이를 반영하는 방식을 취하므로, 실시간성이 떨어진다.
따라서 너무 자주 해도 좋지 않고, 그렇다고 지나치게 미뤄도 좋지 않으니 그 사이의 절충점을 찾아야 한다.

보통 vacuum이 필요한 시점은 큰 테이블이나 인덱스를 삭제하고 난 후이니, 그 때 수동으로 vacuum을 실행하는 것이 바람직할 것이다.

jiffydev

잘 & 열심히 살고싶은 개발자

이전 포스트

즐겁게 배우는 SQL 7. 트랜잭션

다음 포스트

즐겁게 배우는 SQL 8. 데이터 정의