[PostgreSQL실용]_8_테이블디자인 (네이밍, 제약조건CONTRAINT추가와 제거, 자동증가키 IDENTITY와 SERIAL, CREATE INDEX 로 실행시간단축)

Hyejin Beck·2024년 1월 16일

데이터베이스(SQL)

목록 보기

18/40

SQL 데이터베이스를 구성하고 속도를 빠르게 만들기 위해 필요합니다.

이름 지정 규칙 (네이밍 컨벤션) 과 코딩 컨벤션
데이터 무결성 유지
쿼리 속도를 높이기 위한 테이블에 인덱스 추가
등이 있습니다.

네이밍 컨벤션

PostgreSQL은 대소문자에 관계없이 테이블명을 소문자로 처리합니다.

CREATE TABLE customers ()
CREATE TABLE Customers ()
두개 모두 동일한 것으로 취급합니다.

"큰따옴표"

"큰따옴표"를 이용해서 테이블이름을 지정할 수는 있습니다.
CREATE TABLE Customers ()
CREATE TABLE "Customers" ()
CREATE TABLE "cus tomer s" ()
세 개 다 각각 다른 테이블로 저장됩니다.

하지만 이후 모든 열 참조에 "큰따옴표"를 붙혀 써야 합니다.

SELECT FROM Customers
SELECT FROM "Customers"
SELECT * FROM "cus tomer s"

"큰따옴표" 안엔 공백을 넣어줄 수도 있습니다.
하지만 그렇게 되면 띄어쓰기로 인해 번거롭거나 혼란이 많을테죠....

_를 사용합니다.

us_counties_pop_est_2019

약어를 사용합니다.

arrival_time보단 art_tm

복수형을 사용합니다.

students , teachers, books

더 짧게 사용합니다.

표준 SQL은 128자이지만, PostgreSQL은 63자, Oracle은 최대 30자

테이블 복사하기 쉽게 사용합니다.

art_schools_2024_mai 보다는

art_schools_2024_05 가 복붙 하기 좋습니다.

테이블생성시,제약조건

CREATE TABLE 테이블을 만들때 제약조건을 걸어줘야, 데이터 품질을 유지하고 테이블 간 관계의 무결성을 보장하는데 도움이 됩니다.
(조건을 걸어줘야 부정확한 정보를 필터링 해줄수 있을테니깐요)

PRIMARY KEY : 고유의 행
- 컬럼명 타입 PRIMARY KEY
- 컬럼명 타입 ,
  CONSTRAINT 조건명지정 PRIMARY KEY (컬럼명)
  여기서 CONSTRAINT 와 조건명지정은 생략할수 있습니다.
FOREIGN KEY , REFERENCES : 참조 행
- 컬럼명 타입 REFERENCES 스키마명.테이블명(컬럼명)
CHECK : 특정 컬럼의 입력 가능한 값의 범위를 지정
- 컬럼명 타입 CHECK (조건절)
UNIQUE : 각 행에서 고유함 (서로 중복X)
- 컬럼명 타입 UNIQUE
- 컬럼명 타입 ,
  CONSTRAINT 조건명지정 UNIQUE (컬럼명)
NOT NULL : 결측치가 없음

students 정보가 담긴 테이블을 만들어보겠습니다.

CREATE TABLE classes (
	id integer,                   
	name text, 
	day text, 
	teachers_name text, 
	CONSTRAINT class_id_key PRIMARY KEY (id),  			   -- 위의 id를 PK로 조건 + 조건명 지정해야 
	CONSTRAINT class_name_key UNIQUE (name, teachers_name) -- 위의 컬럼들을 UNIQUE로 조건 + 조건명 지정 
); 

CREATE TABLE students (
	id SERIAL PRIMARY KEY ,             		-- 학생 한명당 하나씩 자동 부여될 고유의 id 
	first_name VARCHAR(50) NOT NULL,    		-- 이름은 문자열로 결측치가 없어야함 
	age INT CHECK (age >= 5),           		-- 학생들 나이는 5세이상이여야
	class_id INT REFERENCES public.classes(id), -- public스키마에 있는 classes테이블의 id를 외래키로 (FOREIGN KEY)
	email VARCHAR(100) UNIQUE           		-- 학생들의 이메일주소는 서로 중복되지 않아야
);

PK와 FK(REFERENCES 포함), UNIQUE 지정 등에 대해선 여러 방법들이 있는것 같습니다.

자동증가 키

IDENTITY

ANSI SQL 표준

CREATE TABLE 테이블명 (
	컬럼명1 smallint GENERATED ALWAYS AS IDENTITY, 
	컬럼명2 integer GENERATED ALWAYS AS IDENTITY,
	컬럼명3 bigint GENERATED ALWAYS AS IDENTITY,
	컬럼명4 ... 
); 

INSERT INTO 테이블명 (컬럼명 4,,) 
VALUES ('값, 값, 값,,,'), 
		('값, 값, 값,,,'), 
		('값, 값, 값,,,'), 
		... 
);

여기서 컬럼명1~3은 자동으로 증가하는 키이기 떄문에
INSERT INTO 값을 넣을때 포함하지 않습니다. (억지로 부여해줘도 되긴 합니다)

SERIAL

CREATE TABLE 테이블명 (
	컬럼명1 SMALLSERIAL ,
	컬럼명2 SERIAL, 
	컬럼명3 BIGSERIAL ,
	컬럼명4 ... 
); 

INSERT INTO 테이블명 (컬럼명 4,,) 
VALUES ('값, 값, 값,,,'), 
		('값, 값, 값,,,'), 
		('값, 값, 값,,,'), 
		... 
);

차이점

일단 둘 다 하나의 열을 제거후 새 값을 추가하면, 제거된 열 번호와는 상관없이 기존대로 다음 번호를 부여해줍니다.
1 -> 2 -> 3 -> 4 -> 5 -> 6

IDENTITY는 사용자가 억지로 값을 삽입하지 않도록 방지해줍니다.

FK 원본 삭제행 따라서 삭제되기

원본 테이블의 행이 삭제될때마다
FK로 가져온 열또한 자동으로 제거될 수 있도록 할 경우
테이블 생성시 ON DELETE CASCADE 를 붙혀줍니다.

CREATE TABLE 테이블명1 (
컬럼명1 타입,
컬럼명2 타입,
컬럼명3 타입 REFERENCES 테이블명2 (컬럼명) ON DELETE CASCADE
)
이렇게 하면 테이블2의 컬럼이 삭제될 때마다, 테이블1의 컬럼3도 반영됩니다.

제약조건 제거, 추가

ALTER TABLE 으로 테이블 조건을 변경할 수 있습니다.

조건 제거

ALTER TABLE 테이블명 DROP CONSTRAINT 조건명

조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 조건명 PRIMARY KEY (컬럼명)

NOT NULL 제거

ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 DROP NOT NULL

NOT NULL 추가

ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 SET NOT NULL

인덱스로 쿼리속도 향상

인덱스 : 데이터 베이스가 관리하는 별도의 데이터 구조

인덱스를 추가하여 쿼리 속도를 높일 수 있습니다.

데이터베이스는 데이터를 찾기위해 각 행을 스캔하는 대신, 인덱스를 바로 가기로 사용합니다.

B-TREE 기본인덱스

PostgreSQL 에서 기본 인덱스 유형은 B-TREE 인덱스 입니다.

기본 키 또는 UNIQUE 제약 조건에 지정된 열에 자동으로 생성
CREATE INDEX 문 실행시 기본 생성되는 유형
균형잡힌 트리라는 뜻 Balaced TREE 의 약자
값 검색시 원하는 인덱스를 찾을 때까지 분기를 통해 맨 위에서부터 아래로 내려다보는 방식으로 데이터 구성
범위 연산자 (< , <=. > , BETWEEN 등) 사용하여 정렬하고 검색 가능

테이블 생성 후 데이터 불러오기

CREATE TABLE new_york_adresses (
	longitude numeric(9,6), 
	latitude numeric(9,6), 
	street_number text , 
	street text, 
	unit text, 
	postcode text, 
	id integer CONSTRAINT new_york_key PRIMARY KEY 
); 

COPY new_york_adresses 
FROM '/Users/beckhyejin/Desktop/data_research/practical-sql-main/Chapter_08/city_of_new_york.csv'
WITH (FORMAT CSV,  HEADER);

지금 보니 addresses 를 adresses로 오타를 냈군요...

940374 rows와
7 columns 이 있는 데이터 셋입니다.

우리는 여기서 STREET 컬럼의 일치하는 항목을 검색하기 위해 인덱스 성능을 탐색할 겁니다.

EXPLAIN ANALYZE 실행 시간

인덱스 추가하기 전/후의 성능을 측정하기 위해 EXPLAIN 명령을 사용하여,
특정 데이터베이터 쿼리에 대한 쿼리 실행계획 (Query Plan) 을 나열하겠습니다.

PostgreSQL에서만 사용가능
쿼리실행계획 : 데이터베이스가 테이블을 스캔할 계획, 인덱스 사용 여부 등이 포함

인덱스를 추가하기 전/후의 쿼리 성능을 분석하겠습니다.

EXPLAIN ANALYZE SELECT * FROM 테이블명
WHERE 컬럼명 = '값';

new_york_adresses테이블의 street컬럼 값이 broadway가 들어가는 쿼리들을 실행하도록 지시하고,

결과 대신, 쿼리 프로세스 및 실행에 걸린 시간에 대한 통계를 표시합니다.

EXPLAIN ANALYZE 
SELECT * FROM new_york_adresses 
WHERE street ILIKE 'broadway';

기본적으로 braodway가 들어있는 행을 찾기위해선 제 컴퓨터상으론 202.026ms 가 소요되는군요.

그렇다면 인덱스를 추가하면 성능이 더 빨라질지 궁금합니다.

인덱스 추가

CREATE INDEX 컬럼명추가 ON 테이블명(기존컬럼명)

CREATE INDEX street_idx ON new_york_adresses (street);

쿼리가 실행됬다고 합니다.
한번만 하면 됩니다.

이제 PostgreSQL이 street 컬럼의 값을 스캔하고,
이 값에서 인덱스를 작성한다고 합니다.

EXPLAIN ANALYZE 재실행

다시 EXPLAIN ANALYZE를 통해 실행시간을 확인해보겠습니다.

EXPLAIN ANALYZE 
SELECT * FROM new_york_adresses 
WHERE street ILIKE 'broadway';

인덱스 만들기전 실행했을땐 쿼리 실행 속도가 202.026ms 가 되었는데, 지금은 197.629ms가 나왔씁니다!
신기합니다.

인덱스 제거

DROP INDEX 컬럼명

DROP INDEX street_idx;

이렇게만 해주면 삭제됩니다.

혹시나 싶어 다시 실행시간을 확인해봤는데 오히려 인덱스 생성해주기 전보다 아주 조금 더 늘었네요;;

인덱스 사용시

인덱스가 성능 향상에 큰 장점이 있습니다만, 모든 열에 인덱스를 추가하기는 좀 그렇습니다.

인덱스 추가시, 쿼리 실행 속도는 빨라지지만 데이터 베이스 크기가 커지고 그에 따른 관리비용이 듭니다.

WHERE절에서 자주 사용되는 열에 인덱스를 추가하는것이, 검색 성능이 크게 향상될 수 있습니다.
EXPLAIN ANALYZE 를 상용해서 다양한 구성에서 성능을 테스트하여, 최적화 하는 과정을 갖습니다.

연습문제

LP 컬렉션을 추적하기위해 만들고 있는 데이터 베이스의 두 테이블을 생각해보세요.
그런다음 CREATE TABLE문을 검토해주세요.
CREATE TABLE albums (
	album_id bigint GENERATED ALWAYS AS IDENTITY, -- 번호가 자동부여되는 컬럼 
	catalog_code text, 
	title text, 
	artist text, 
	release_date date, 
	genre text, 
	description text
); 
albums테이블에는 디스크에 있는 전체노래 모음에 대한 정보가 포함되어있습니다.
CREATE TABLE songs (
	song_id bigint GENERATED ALWAYS AS IDENTITY, 
	title text, 
	composers text, 
	album_id bigint
); 
songs테이블은 앨범의 각 트랙을 분류합니다.
각 노래에는 제목과 작곡가(앨범의 아티스트와 다를수 있음) 열이 있습니다.

문제1

기본 및 외래 키와 두 테이블에 대한 추가 제약조건을 포함하도록 CREATE TABLE 문을 수정하고 그 이유를 말해주세요.

문제2

album_id열을 인조 기본 키로 사용하는 대신, albums 안에 있는 열들 중 자연 키로 사용할 만한 열이 있나요? 결정하려면 무엇을 알아야 하나요?

문제1 풀이

두 테이블에대한 제약조건 (PK, FK포함) 을 위해서는 두 테이블의 각 특성을 파악해야할것 같다.
각각 어느 컬럼을 PK로 지정하고, 또 다른 테이블의 어느 컬럼을 FK로 가져올거냐에 대해서 관계성도 파악해야 할것 같다.
tjfauddptjs

albums 테이블

컬럼명	타입	설명
album_id	bigint	고유의 번호라서 이게 PK에 적합
catalog_code	text	앨범의 카탈로그라서 굳이 song테이블에 영향x UNIQUE값X
title	text	앨범제목과 노래제목은 다르기에 굳이 서로 영향X UNIQUE값도 X
artist	text	이건 좀 songs테이블에 영향을 미칠수 있겠다. UNIQUE값은X
release_date	date	앨범판매날짜도 굳이 영향X
genre	text	앨범장르라 노래장르에 굳이?
descriptipn	text	굳이?

albumns테이블에는 album_id가 PK 로 지정해줘야 한다. 왜냐면 각 row마다 가지고 있는 고유의 번호값이기 때문이다.

songs 테이블

컬럼명	타입	설명
song_id	bigint	고유의번호라서 이게 PK에 적합
title	text	앨범제목과 노래제목은 다르지않나? 굳이 영향없을것같다
composers	text	영향x
album_id	bigint	albums테이블의 album_id와 같아 이게 FK

songs테이블에는 album_id가 FK로 지정해줘야 한다. 왜냐면 albumns테이블에 있는 고유PK을 가져오는 키이기 떄문이다.

그렇다면 이미 만들어진 테이블에 ALTER TABLE로 추가지정해주겠다.

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 PRIMARY KEY (컬럼명)

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 FOREIGN KEY (기존테이블_id) REFERENCES 다른테이블(다른테이블_id);

ALTER TABLE albums ADD CONSTRAINT album_id_pk PRIMARY KEY (album_id);

ALTER TABLE songs ADD CONSTRAINT songs_album_id_fk FOREIGN KEY (album_id) REFERENCES albums(album_id)

문제2 풀이

인조 기본키 ? 자연 키? 잘 모르겠다. 다시한번 확인해봅니다.

기본키 Primary Key 로는

자연키 Natural Key

인조키 Surrogate Key 중 사용해야한다.

자연키

예시로 운전면허증 식별번호같이 부여받는 고유한 id 이다.

그러나 이 경우 국가마다, 주마다 식별번호가 달라 전세계적(?)으로 고유한 id라고 보긴 어렵다. 다른 주에 똑같은 id를 부여받은 사람이 있을 수 있기 때문이다.
이 경우 국가나 도시 이름/번호와 합쳐 복합 기본 키 Composite Primary Key를 만들 수 있다.

인조키

임의의 값을 부여한다. 자연 기본 키를 생성할수 있는 데이터가 없을때 사용한다.
Identity와 Serial로 만든다.
1->2->3->4-> 이렇게 자동으로 하나씩 카운트 하며 부여해준다.

차이점

자연키 인조키
의미가 있는가 있다 없다
데이터와 연관이있는가 있다 없다
공간이 더 필요한가 이미 있는 열 추가시 공간필요

이상적인 테이블이라면 고유한 id 로서 자연키로 사용할 수 있는 열이 하나 이상 있어야 합니다.
테이블 구조를 재고할 수 없는 경우 인조 키를 사용해야 합니다.

우선순위 : 자연키 > 인조키

	자연키	인조키
의미가 있는가	있다	없다
데이터와 연관이있는가	있다	없다
공간이 더 필요한가	이미 있는 열	추가시 공간필요

다시 문제 풀이로 돌아오자면, album_id가 자연키에 조건이 부합하다.

Hyejin Beck

데이터기반 스토리텔링을 통해 인사이트를 얻습니다.

이전 포스트

[PostgreSQL실용]_7_키지정(CONSTRAINT,REFERENCES)과 테이블 병합 (JOIN ON/USING, UNION, INTERSECT, EXCEPT의 차이)

다음 포스트

[PostgreSQL실용]_8_테이블디자인 (네이밍, 제약조건CONTRAINT추가와 제거, 자동증가키 IDENTITY와 SERIAL, CREATE INDEX 로 실행시간단축)

데이터베이스(SQL)

네이밍 컨벤션

"큰따옴표"

테이블생성시,제약조건

자동증가 키

IDENTITY

SERIAL

차이점

FK 원본 삭제행 따라서 삭제되기

제약조건 제거, 추가

조건 제거

조건 추가

NOT NULL 제거

NOT NULL 추가

인덱스로 쿼리속도 향상

B-TREE 기본인덱스

테이블 생성 후 데이터 불러오기

EXPLAIN ANALYZE 실행 시간

인덱스 추가

EXPLAIN ANALYZE 재실행

인덱스 제거

인덱스 사용시

연습문제

문제1

문제2

문제1 풀이

문제2 풀이

자연키

인조키

차이점

[PostgreSQL실용]_7_키지정(CONSTRAINT,REFERENCES)과 테이블 병합 (JOIN ON/USING, UNION, INTERSECT, EXCEPT의 차이)

[PostgreSQL실용]_9_집계함수로 데이터탐색(COUNT,MAX,GROUP BY,SUM,HAVING)

0개의 댓글

관련 채용 정보

[PostgreSQL실용]_8_테이블디자인 (네이밍, 제약조건CONTRAINT추가와 제거, 자동증가키 IDENTITY와 SERIAL, CREATE INDEX 로 실행시간단축)

데이터베이스(SQL)

네이밍 컨벤션

"큰따옴표"

테이블생성시,제약조건

자동증가 키

IDENTITY

SERIAL

차이점

FK 원본 삭제행 따라서 삭제되기

제약조건 제거, 추가

조건 제거

조건 추가

NOT NULL 제거

NOT NULL 추가

인덱스로 쿼리속도 향상

B-TREE 기본인덱스

테이블 생성 후 데이터 불러오기

EXPLAIN ANALYZE 실행 시간

인덱스 추가

EXPLAIN ANALYZE 재실행

인덱스 제거

인덱스 사용시

연습문제

문제1

문제2

문제1 풀이

문제2 풀이

자연키

인조키

차이점

[PostgreSQL실용]_7_키지정(CONSTRAINT,REFERENCES)과 테이블 병합 (JOIN ON/USING, UNION, INTERSECT, EXCEPT의 차이)

[PostgreSQL실용]_9_집계함수로 데이터탐색(COUNT,MAX,GROUP BY,SUM,HAVING)

0개의 댓글

관련 채용 정보

[PostgreSQL실용]_8_테이블디자인 (네이밍, 제약조건CONTRAINT추가와 제거, 자동증가키 IDENTITY와 SERIAL, CREATE INDEX 로 실행시간단축)