SQL 테이블 만들기

그동안 이미 만들어진 SQL 테이블을 사용하는 법에 대해서 배웠다면 이젠 직접 테이블을 만들고 그 안에 데이터들을 집어 넣는 과정을 배워보도록 하자.

 

1) 데이터 자료형 복습

먼저 데이터 종류에 대해서 잠깐 복습해 볼 필요가 있다. 데이터 종류에는 숫자형, 문자형, 부울형 등등이 존재하는데, 각각 데이터의 종류에 따라서 필요한 자료형을 선택할 필요가 있다.

 

예컨대, 전화번호 데이터를 저장해야 한다고 하자. 그렇다면 어떤 자료형으로 저장해야 할까? 흔히 생각했을 때는 전화번호는 숫자로 이루어져있으니 숫자형으로 저장해야 되지 않나 생각할 수도 있다. 그러나, 우리는 전화번호로 덧셈이나 뺄셈 등 사칙연산을 적용하는 일이 극히 드물기 때문에, 굳이 정수형으로 저장할 필요가 없이 문자형으로 저장하는 것이 더 편할 것이다. 따라서, 이처럼 어떤 데이터를 어떤 자료형으로 저장해야할 지 고민일 때는 구글링을 해보자.

 

2) Primary key & Foreign key

그 다음으로 primary key와 foreign key에 대해서 알아보자. Primary key란 테이블에 있어서는 필수적인 존재다. (물론 문법상으로 필수적이진 않지만 대체적으로 primary key를 선언할 것을 권장하고 있다.) Primary key는 테이블의 기준이 되는 열이자, 중복이 되서는 안되는 고유한 데이터를 담고 있어야 한다. 예컨대, 지금껏 사용해왔던 데이터베이스의 경우 고객의 id나, 상점의 id, 사원의 id 등등이 있었다. pgAdmin에서는 이러한 primary key들을 열의 이름 옆에 [PK]라는 기호를 적음으로써 해당 열이 primary key임을 알려주고 있다. 또한 primary key의 특성상 null 값이 존재해서 안되고, 고유해야 하기 때문에 많은 테이블에서 SERIAL 타입을 통해서 primary key들을 사용하고 있다.

그렇다면 foreign key란 무엇일까. Foreign key란 해당 테이블의 고유한 정보가 아닌 다른 테이블에 이미 존재하는 데이터를 이식해왔을 때 foreign key라고 한다. 이 foreign key는 테이블을 만들 때 reference를 선언함으로써 그 효과가 영향력을 미치게 된다. 이러한 foreign key를 가지고 있는 테이블을 referencing table 혹은 child table이라고 부른다. 반면에 foreign key가 가장 먼저 선언되어 있는 테이블을 referenced table, parent table이라고 부른다. 테이블은 의존관계에 따른 다양한 foreign key를 보유할 수 있다.

 

그렇다면 foreign key인지 아닌지를 확인할 수 있는 방법은 무엇일까. 우선 해당 테이블이 속해있는 데이터베이스를 선택하고, 그 다음 Schemas를 클릭, public 클릭, 그 다음 확인하고자 하는 테이블 클릭, 그리고 Constraints를 클릭하면 열의 이름 뒤에 "_fkey"라고 붙어있는 열이 바로 foreign key이다. 마찬가지로 "_pkey"라고 써있는 것은 primary key임을 알 수 있다.

 

3) Constraints

Constraints란 제약조건으로, 테이블을 생성할 때,  어떤 특정한 조건을  충족시킨 데이터들만 저장할 것인지 정하는 것을 말한다. 가장 많이 쓰이는 제약조건은 바로 "NOT NULL",  "UNIQUE", "PRIMARY KEY", "FOREIGN KEY"이다. 

 

(1) 개별 열에 적용하는 Constraints

(i) NOT NULL 제약조건은 데이터가 NULL일 수 없는 것을 의미한다. 따라서 무조건 데이터를 의도적으로 삽입해줘야 한다.

(ii) UNIQUE 제약조건은 데이터가 고유해야 한다는 것을 의미한다. 예컨대, 회원 id 같은 것들은 중복돼서는 안되므로 주로 UNIQUE 제약조건을 통해서 통제한다.

(iii) PRIMARY KEY는 위에서 언급했듯이 테이블의 기준이 되는 열로서 중복이 되어서도 안되고 NULL이 되어서는 안된다. 따라서 primary key선언을 해주는 것이 중요하다.

(ix) FOREIGN KEY 또한 테이블 간 의존관계에 의해서 필요에 따라 선언을 해야할 때 사용되는 제약조건이다.

 

(2) Table Constraints

지금까지는 개별 열에 적용될 수 있는 constraints에 대해서 알아봤다. 이제부터는 전체 열에 적용할 수 있는 제약조건에 대해서 알아보도록 하자.

(i) CHECK(condition) : 데이터를 저장하기 전에 조건을 확인하는 제약조건.

(ii) REFERENCES : 저장하려고 하는 열의 데이터가 다른 테이블의 열에 존재해야 함.

(iii) UNIQUE(column_list) : 표 전체의 열에 적용이 되고, 괄호 속 세로단에 존재하는 열은 고유한 값을 가져야만 함. 또한 해당 열에서만 고유한 것이 아니라 모든 열에서 고유해야 한다는 점이 특징이다.

(ix) PRIMARY KEY(column_list) : 다양한 열로 구성된 primary key를 선언할 수 있도록 함.

 

4) CREATE TABLE

이제 실제로 테이블을 만드는 코드에 대해서 알아보자. 먼저 수도코드로 표현한 식은 다음과 같다.

CREATE TABLE table_name(
col_name1 TYPE col_constraints,
col_name2 TYPE col_constraints,
table_constraint1 table_constraint2
)INHERITS existing_table_name;

먼저 CREATE TABLE을 적은 뒤에 테이블의 이름을 적는다. 그리고 괄호 안에 해당 테이블을 구성하는 열의 이름, 자료형, 그리고 제약조건들을 적고, 다른 열을 생성하고 싶을 때에는 꼭 콤마(,)를 잊지 말고 찍어야 한다. 테이블을 구성하는 열들을 전부 적고 난 뒤에는 테이블 전체에 영향을 미치는 테이블 조건들을 적고 괄호를 닫는다. 만약 테이블 안의 열 중에서 다른 테이블과 연관관계가 있는 경우에는 INHERITS 조건문을 적어준다.

 

위에서 잠깐 언급했던 자료형 중 숫자형에서 SERIAL형을 살펴보자. SERIAL 형은 연속적으로 숫자를 대입해주는 것을 의미한다. 그럼 과연 만약 이 SERIAL형으로 된 데이터를 포함한 행이 지워진다면 나머지 열들에는 어떤 영향이 있을까? 정답은 지워진 열 자리에는 데이터가 삭제되었음을 나타내는 문자가 들어간다. 예컨대, 기존에 SERIAL로 된 1,2,3,4,5,6의 데이터가 있었다고 하고, 3이 포함된 데이터 행을 지웠다고 해보자. 그렇다면 삭제 이후에는 1,2,4,5,6으로 구성된 데이터가 남는다. 3이 삭제되었다고 1,2,3,4,5로 자동으로 열을 재구성하지는 않는 것이다.

 

잠깐 예를 들어, 운동선수들의 정보가 담긴 테이블을 만들어보자. 우선, 테이블의 이름을 players로 짓고, 테이블에는 선수 id, 종목, 선수 이름, 나이를 저장하는 열들이 있다고 해보자. 그렇다면 다음과 같은 코드로 작성하면 된다.

CREATE TABLE players(
player_id SERIAL PRIMARY KEY,
sport_type VARCHAR(20) NOT NULL,
player_name VARCHAR(50) NOT NULL,
player_age SMALLINT NOT NULL
)

player_id를 primary key로 지정한 이유는 테이블 내에서 겹칠 일이 없기 때문이다. 또한 종목이나 이름 같은 경우에는 문자열을 저장해야 하기 때문에 VARCHAR타입으로 작성하고 각각 종목의 이름은 20자 이내, 선수 이름은 50자 이내로 적도록 설정했다. 선수의 나이는 아무리 많아도 100살을 넘기기 힘들기 때문에 메모리 효율성을 위해 SMALLINT형으로 설정했다. 그리고, 빈칸이 없도록 모두 NOT NULL 제약조건을 걸어주었다. 필요에 따라서는 TIMESTAMP 형식으로 선수 등록 일자를 저장할 수도 있겠다. 열의 제약조건이 하나가 아닌 여러개일 때에는 콤마 없이 여러개를 모두 나열하면 된다. ex) player_name VARCHAR(50) UNIQUE NOT NULL. 참고로 제약조건의 순서에는 상관이 없다.

 

여기서 또 다른 테이블을 만든다고 해보자. 이번에 만들 테이블은 선수의 담당코치가 누구인지 저장하는 테이블이라고 해보자. 테이블의 이름은 coaches이고 해당 테이블을 구성하는 열은, 선수 id와 코치의 이름이라고 해보자. 그럼 다음과 같이 코드를 적을 수 있다.

CREATE TABLE coaches(
player_id INTEGER REFERENCES players(player_id),
coach_name VARCHAR(50)
)

주의깊게 볼 부분은 바로 INTEGER와 REFERENCES이다. 우선 우리는 player_id를 이미 만들어놓은 players 테이블에서 가져오고자 하는데, players 테이블에서 player_id는 선언될 때 SERIAL로 선언이 되었기 때문에 다른 테이블에서 가져다 사용할 때에는 INTEGER 형태로 받아주면 된다. 그리고 players 테이블의 player_id 열을 foreign key로 가져오는 것이기 때문에 REFERENES라는 키워드를 사용하고 parent table을 적고 그 뒤 괄호 안에 참조할 열을 적으면 된다. 

 

5) INSERT

위에서는 테이블을 생성하는 법에 대해서 배웠다. 이제는 생성된 테이블에 데이터를 삽입하는 방법에 대해서 알아보자. 기본적인 수도코드는 다음과 같다.

INSERT INTO table(col1, col2, ...)
VALUES
(val1, val2),
(val3, val4)

만약 다른 테이블의 열을 가져와 삽입하고자 할 때는 다음과 같은 수도코드를 사용하면 된다.

INSERT INTO table(col1, col2, ...)
SELECT col1, col2, ...
FROM another_table
WHERE condition;

INSERT에서 주의할 점은, 만들어 놓았던 테이블의 제약조건과 자료형에 맞는 값을 삽입해야 한다는 것이다. 만약 제약조건과 자료형에 맞지 않는 값을 삽입한다면 오류를 반환한다. 참고로 SERIAL 값은 일일히 삽입하지 않아도 자동적으로 삽입해준다.

 

6) UPDATE

UPDATE 키워드는 표의 값을 바꾸는데 사용된다. 기본적인 수도코드는 다음과 같다.

UPDATE table
SET column1 = value1,
column2 = value2, ...
WHERE condition;

역시나 다른 테이블의 값을 이용해서 업데이트를 할 수도 있다. 흔히 다음과 같은 상황을 update join이라고 부른다.

UPDATE tableA
SET origin_col = tableB.new_col
FROM tableB
WHERE tableA.id = tableB.id

만약 값이 바뀐 데이터들을 확인하고 싶다면 마지막에 RETURNING 키워드를 삽입하고 어떤 데이터들을 확인하고 싶은지 적으면 된다.

UPDATE table
SET col1 = val1,
col2 = val2,...
WHERE condition
RETURNING col1, col2, ...

7) DELETE

이제 행을 삭제하는 방법에 대해서 배워보자. 행을 삭제하기 위해서는 DELETE라는 키워드를 사용하고, 수도코드는 다음과 같다.

DELETE FROM table
WHERE condition;

다른 표에 존재하는지 여부에 따라서도 삭제를 할 수 있는데, 다음과 같이 적을 수 있다.

DELETE FROM tableA
USING tableB
WHERE tableA.id = tableB.id

만약 모든 데이터를 삭제하고 싶다면 조건 없이 다음과 같이 적으면 된다.

DELETE FROM table

UPDATE 때와 마찬가지로 DELETE 키워드를 사용할 때에도 RETURNING 키워드를 통해 삭제된 행을 호출할 수 있다.

 

8) ALTER

이번에는 이미 존재하는 테이블의 구조를 변형하는 ALTER 키워드에 대해서 배워보자.

ALTER 키워드로 열을 추가하거나, 삭제하거나, 이미 존재하는 열의 이름을 바꿀 수 있다. 또한 열의 자료형을 바꿀 수 있다. 그리고, 열에 대한 기본값을 설정할 수도 있다. ALTER 키워드를 통해서 CHECK 제약조건을 추가할 수 있고, 테이블의 이름을 바꿀 수도 있다. 

그럼 ALTER 키워드를 이용한 수도코드를 살펴보자.

ALTER TABLE table_name action

새로운 열을 더할 때는 다음과 같이 적으면 된다.

ALTER TABLE table_name
ADD COLUMN new_col TYPE

열을 삭제할 때는 다음과 같이 적을 수 있다.

ALTER TABLE table_name
DROP COLUMN col_name

다음과 같이 열의 기본값을 설정하거나 열의 제약조건을 바꿀 수도 있다.

ALTER TABLE table_name
ALTER COLUMN col_name

SET DEFAULT val

SET NOT NULL

DROP NOT NULL

ADD CONSTRAINT constraint_name

테이블의 이름을 바꾸는 방법은 다음과 같다. 

ALTER TABLE table_name
RENAME TO new_name

열의 이름을 바꾸는 방법은 다음과 같다.

ALTER TABLE table_name
RENAME COLUMN col_name TO new_name

9) DROP

DROP 키워드는 테이블에서 열을 완전히 삭제할 수 있는 키워드이다. 또한 삭제할 열에 적용되어 있던 제약조건들도 자동으로 삭제된다.

수도코드로 살펴보자.

ALTER TABLE table_name
DROP COLUMN col_name CASACDE

 

col_name 뒤에 있는 CASACADE는 모든 의존관계를 끊겠다는 키워드이다. 만약 삭제하고자 하는 열이 다른 열에 의존관계를 갖고 있을 때에는 CASCADE를 적어줌으로써 관계를 끊어주어야 한다. 왜냐하면 CASCADE 키워드를 적지 않았을 때에는 의존관계가 계속 남아있기 때문이다.

 

이 DROP 키워드는 만약 삭제의 대상이 되는 열이 존재하지 않으면 에러를 발생시킨다. 따라서 에러를 피하기 위해서는 IF EXISTS를 같이 적어주는 것이 좋다.

ALTER TABLE table_name
DROP COLUMN IF EXISTS col_name

만약 여러 열을 삭제 하고 싶을 때에는 콤마를 사용하면 된다.

ALTER TABLE table_name
DROP COLUMN col1,
DROP COLUMN col2

10) CHECK

CHECK 제약조건은 조금 더 커스터마이즈 된 제약조건을 만들 수 있게끔 해준다. 수도코드를 통해서 보다 잘 이해해보자.

CREATE TABLE table_name(
id SERIAL PRIMARY KEY,
age SMALLINT CHECK(age > 21),
parent_age SMALLINT CHECK(parent_age > age)
);

이렇듯 CHECK 키워드를 쓰고 괄호 안에 제약 조건을 추가하면 된다.