테이블 관리 (1)

1. 데이터 타입
- >> Boolean, Character, Numeric
- >> Time, Arrays, JSON
2. 테이블 생성
3. CTAS (CREATE TABLE AS SELECT)
4. 테이블 구조 변경
5. 테이블 이름 변경

1. 데이터 타입

테이블은 컬럼으로 이루어져 있고 컬럼은 다양한 데이터 타입을 지원한다. 이는 RDBMS가 제 역할을 하는데 있어서 매우 중요하다.

다양한 데이터 타입 지원:

Boolean
Character
Numeric
Time
Arrays
JSON

>> Boolean, Character, Numeric

데이터 타입	세부 타입	설명
Boolean	Boolean	참과 거짓의 값을 저장한다.
Character	CHAR	고정형 길이의 문자열을 저장한다. 만약 CHAR(10)인데 'ABCDE’만 입력할 경우 실제로는 'ABCDE '로 뒤에 공백을 붙여 저장한다.
	VARCHAR	가변형 길이의 분자열을 저장한다. 만약 VARCHAR(10) 인데 'ABCDE’만 입력할 경우 실제로 'ABCDE’만 저장한다. (뒤에 공백을 붙이지 않는다)
	TEXT	대용량의 문자데이터를 저장한다.
Numeric	INT	정수형데이터를 저장한다. 크기는 4byte이다. (범위는 -2,147,483,648 to 2,147,483,647)
	SMALLINT	정수형 데이터를 저장한다. 크기는 2byte이다. (범위는 -32,768 to 32,767)
	float	부동 소수점의 데이터를 저장한다. 크기는 8byte이다.
	numeric	NUMERIC(15, 2)와 같이 전체 크기와 소수점의 자리를 지정할 수 있다.

[실습]

CREATE TABLE
DATA_TYPE_TEST_1
(
  A_BOOLEAN BOOLEAN,
  B_CHAR CHAR(10),
  C_VARCHAR VARCHAR(10),
  D_TEXT TEXT,
  E_INT INT,
  F_SMALLINT SMALLINT,
  G_FLOAT FLOAT,
  H_NUMERIC NUMERIC(15, 2)
);

INSERT INTO 
DATA_TYPE_TEST_1
VALUES
(
  TRUE,        -- A_BOOLEAN
  'ABCDE',     -- B_CHAR
  'ABCDE',     -- C_VARCHAR
  'TEXT',      -- D_TEXT
  1000,        -- E_INT
  10,          -- F_SMALLINT
  10.12345,    -- G_FLOAT
  10.25        -- H_NUMERIC
);

COMMIT;

1	SELECT * FROM DATA_TYPE_TEST_1;

>> Time, Arrays, JSON

데이터 타입	세부 타입	설명
Time	Date	일자 데이터를 저장한다.
	TIME	시간 데이터를 저장한다.
	TIMESTAMP	일자와 시간 데이터를 모두 저장한다.
Arrays	array	배열 형식의 데이터를 저장한다. 한개의 컬럼에 여러개의 데이터를 동시에 저장할 수 있으며 저장한 데이터의 순서로 조회할 수 있다.
JSON	JSON	JSON형식의 데이터를 저장한다. JSON형식의 데이터를 입력해서 JSON형식대로 각 LEVEL의 데이터를 저장할 수 있다.

[실습]

CREATE TABLE DATA_TYPE_TEST_2
(
  A_DATE DATE,
  B_TIME TIME,
  C_TIMESTAMP TIMESTAMP,
  D_ARRAY TEXT[],
  E_JSON JSON
);

INSERT INTO DATA_TYPE_TEST_2
VALUES
(
 CURRENT_DATE,        -- A_DATE
 LOCALTIME,           -- B_TIME
 CURRENT_TIMESTAMP,   -- C_TIMESTAMP
 ARRAY ['010-1234-1234', '010-4321-4321'],  -- D_ARRAY
 '{"customer": "John Doe", "items": {"product": "Beer", "qty": 6}}'  -- E_JSON
);

COMMIT;

1	SELECT * FROM DATA_TYPE_TEST_2;

2. 테이블 생성

테이블은 데이터를 담는 그릇으로써 반드시 생성해야만 데이터를 저장할 수 있다.

>> 테이블 생성 시 컬럼의 제약 조건

제약조건명	설명
NOT NULL	해당 제약 조건이 있는 컬럼은 NULL이 저장될 수 없다.
UNIQUE	해당 제약 조건이 있는 컬럼의 값은 테이블 내에서 유일해야 한다.
PRIMARY KEY	해당 제약 조건이 있는 컬럼의 값은 테이블 내에서 유일해야 하고 반드시 NOT NULL이어야 한다.
CHECK	해당 제약 조건이 있는 컬럼은 지정하는 조건에 맞는 값이 들어가야 한다.
REFERENCES	해당 제약 조건이 있는 컬럼의 값은 참조하는 테이블의 특정 컬럼에 값이 존재해야 한다.

>> 테이블 생성 실습

CREATE TABLE ACCOUNT
(
  USER_ID SERIAL PRIMARY KEY,
  USERNAME VARCHAR (50) UNIQUE NOT NULL,
  PASSWORD VARCHAR (50) NOT NULL,
  EMAIL VARCHAR (355) UNIQUE NOT NULL,
  CREATED_ON TIMESTAMP NOT NULL,
  LAST_LOGIN TIMESTAMP
);

CREATE TABLE ROLE
(
  ROLE_ID SERIAL PRIMARY KEY,
  ROLE_NAME VARCHAR (255) UNIQUE NOT NULL
);

CREATE TABLE ACCOUNT_ROLE
(
  USER_ID INTEGER NOT NULL,
  ROLE_ID INTEGER NOT NULL,
  GRANT_DATE TIMESTAMP WITHOUT TIME ZONE,
  PRIMARY KEY (USER_ID, ROLE_ID),   -- 기본키는 USER_ID + ROLE_ID로 한다
  CONSTRAINT ACCOUNT_ROLE_ROLE_ID_FKEY FOREIGN KEY (ROLE_ID)  -- ROLE_ID 컬럼은 ROLE 테이블의 ROLE_ID 컬럼을 참조한다
  REFERENCES ROLE (ROLE_ID) MATCH SIMPLE  -- ROLE_ID 컬럼은 ROLE 테이블의 ROLE_ID컬럼에 대한 삭제 혹은 변경 시 아무것도 하지 않는다
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT ACCOUNT_ROLE_USER_ID_FKEY FOREIGN KEY (USER_ID)  -- USER_ID 컬럼은 ACCOUNT 테이블의 USER_ID 컬럼을 참조한다
  REFERENCES ACCOUNT (USER_ID) MATCH SIMPLE  -- USER_ID 컬럼은 ACCOUNT 테이블의  USER_ID 컬럼에 대한 삭제 혹은 변경 시 아무것도 하지 않는다
  ON UPDATE NO ACTION ON DELETE NO ACTION
);

ACCOUNT 테이블과 ROLE 테이블은 다대다의 매칭관계이다.
두 테이블의 내용이 매칭될 수 있도록 ACCOUNT_ROLE 테이블을 생성하여 1대다의 관계를 만들어 준다.

INSERT INTO ACCOUNT
VALUES
(1, '홍길동', '1234', 'honggd@naver.com', CURRENT_TIMESTAMP, NULL);

COMMIT;

1	SELECT * FROM ACCOUNT;

INSERT INTO ROLE
VALUES (1, 'DBA');

COMMIT;

1	SELECT * FROM ROLE;

INSERT INTO ACCOUNT_ROLE 
VALUES (1, 1, CURRENT_TIMESTAMP);

COMMIT;

1	SELECT * FROM ACCOUNT_ROLE;

>> 에러

(1) 참조 누락성 제약 조건 위반

참조키(Foreign Key)에 해당 데이터가 존재하지 않는 경우

1 2	INSERT INTO ACCOUNT_ROLE VALUES (2, 1, CURRENT_TIMESTAMP);

1 2	INSERT INTO ACCOUNT_ROLE VALUES (1, 2, CURRENT_TIMESTAMP);

(2) 고유 제약 조건 위반

중복값이 없어야 하는 PRIMARY KEY에 중복이 발생한 경우

1 2	INSERT INTO ACCOUNT_ROLE VALUES (1, 1, CURRENT_TIMESTAMP);

(3) 참조 시 갱신/삭제 불가

데이터가 참조되어 있을 때 해당 데이터를 갱신/삭제 불가한다.

1
2
3

UPDATE ACCOUNT
SET USER_ID = 2
WHERE USER_ID = 1;

1 2	DELETE FROM ACCOUNT WHERE USER_ID = 1;

3. CTAS (CREATE TABLE AS SELECT)

3-1. 개념

CTAS는 CREATE TABLE AS SELECT의 약어로써 SELECT문을 기반으로 CREATE TABLE을 할 수 있는 CREATE문이다.

3-2. CTAS 문법

1
2
3

CREATE TABLE NEW_TABLE   -- 새로운 테이블의 이름을 설정한다
AS
SELECT문                 -- SELECT문을 작성한다

1
2
3

CREATE TABLE NEW_TABLE (NEW_COLUMN_1, NEW_COLUMN_2)  -- 새로운 테이블명의 이름과 컬럼명을 설정한다
AS
SELECT문                 -- SELECT문을 작성한다

1
2
3

CREATE TABLE IF NOT EXISTS NEW_TABLE   -- 기존에 테이블이 존재하지 않는 경우만 생성한다
AS
SELECT문                 -- SELECT문을 작성한다

3-3. CTAS 실습

[MISSION] 액션영화의 정보만으로 신규 테이블을 생성

1	SELECT * FROM CATEGORY WHERE CATEGORY_ID = 1;

SELECT 
  A.FILM_ID, 
  A.TITLE,
  A.RELEASE_YEAR,
  A.LENGTH,
  A.RATING
FROM
  FILM A,
  FILM_CATEGORY B
WHERE A.FILM_ID = B.FILM_ID
AND B.CATEGORY_ID = 1;

>> 액션 영화 테이블 생성

CREATE TABLE ACTION_FILM AS
SELECT 
  A.FILM_ID, 
  A.TITLE,
  A.RELEASE_YEAR,
  A.LENGTH,
  A.RATING
FROM
  FILM A,
  FILM_CATEGORY B
WHERE A.FILM_ID = B.FILM_ID
AND B.CATEGORY_ID = 1;

1	SELECT * FROM ACTION_FILM;

4. 테이블 구조 변경

한번 만들어진 테이블이라고 하더라도 데이블 구조를 변경할 수 있다. 이 기능으로 인해 업무변화에 유연하게 대처할 수 있다.

>> 테이블 구조 변경 실습

CREATE TABLE LINKS 
(
  LINK_ID SERIAL PRIMARY KEY,
  TITLE VARCHAR (512) NOT NULL,
  URL VARCHAR (1024) NOT NULL UNIQUE
);

1
2
3

-- 1) ACTIVE 컬럼을 추가
ALTER TABLE LINKS ADD COLUMN ACTIVE BOOLEAN;
SELECT * FROM LINKS;

1
2
3

-- 2) ACTIVE 컬럼을 제거
ALTER TABLE LINKS DROP COLUMN ACTIVE;
SELECT * FROM LINKS;

1
2
3

-- 3) TITLE 컬럼을 LINK_TITLE 컬럼으로 변경
ALTER TABLE LINKS RENAME COLUMN TITLE TO LINK_TITLE;
SELECT * FROM LINKS;

1
2
3

-- 4) TARGET 컬럼을 추가
ALTER TABLE LINKS ADD COLUMN TARGET VARCHAR(10);
SELECT * FROM LINKS;

-- 5) TARGET 컬럼의 DEFAULT값을 "_blank"로 설정
ALTER TABLE LINKS ALTER COLUMN TARGET
SET DEFAULT '_blank';
SELECT * FROM LINKS;

데이터 추가 해보기

1
2
3

INSERT INTO LINKS (LINK_TITLE, URL)
VALUES
('PostgreSQL Tutorial', 'http://www.postgresqltutorial.com/');

1	SELECT * FROM LINKS;

1 2	-- 6) 체크 제약 조건 추가 ALTER TABLE LINKS ADD CHECK (TARGET IN ('_self', '_blank', '_parent', '_top'));

TARGET 컬럼의 체크 제약 조건에 없는 ‘whatever’ 값으로 INSERT 시도

1 2	INSERT INTO LINKS (LINK_TITLE, URL, TARGET) VALUES('PostgreSQL', 'http://www.postgresql.org/', 'whatever');

TARGET 컬럼의 체크 제약 조건에 없는 ‘whatever’ 값으로 INSERT 시도

1 2	INSERT INTO LINKS (LINK_TITLE, URL, TARGET) VALUES('PostgreSQL', 'http://www.postgresql.org/', '_self');

5. 테이블 이름 변경

한번 만들어진 테이블이라고 하더라도 테이블 이름을 변경할 수 있다. 이 기능으로 인해 업무변화에 유연하게 대처할 수 있다.

5-1. 테이블 이름 변경 문법

1 2	ALTER TABLE OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME

5-2. 테이블 이름 변경 실습

>> 테이블 이름 변경 실습 (1)

[MISSION] VENDORS 테이블을 SUPPLIERS 테이블로 변경

-- VENDORS 테이블 생성
CREATE TABLE VENDORS
(
  ID SERIAL PRIMARY KEY,
  NAME VARCHAR NOT NULL
);

SELECT * FROM VENDORS;

1 2	-- VENDORS 테이블의 이름을 SUPPLIERS 로 변경 ALTER TABLE VENDORS RENAME TO SUPPLIERS;

1	SELECT * FROM SUPPLIERS;

1	SELECT * FROM VENDORS;

>> 테이블 이름 변경 실습 (2)

-- SUPPLIER_GROUPS 테이블 생성
CREATE TABLE SUPPLIER_GROUPS
(
  ID SERIAL PRIMARY KEY,
  NAME VARCHAR NOT NULL
)

-- SUPPLIERS 테이블에 컬럼 추가 후 FK 생성
ALTER TABLE SUPPLIERS ADD COLUMN GROUP_ID INT NOT NULL;  -- SUPPLIERS 테이블에 GROUP_ID 컬럼 추가

ALTER TABLE SUPPLIERS ADD FOREIGN KEY (GROUP_ID)  
REFERENCES SUPPLIER_GROUPS (ID);   -- SUPPLIER_GROUPS 테이블의 ID 컬럼을 참조하여 SUPPLIERS 테이블의 GROUP_ID 컬러의 값을 지정

-- 아래와 같은 뷰를 생성 (뷰는 실체하는 데이터가 아닌 보기용)
CREATE VIEW SUPPLIER_DATA AS
SELECT
  S.ID,
  S.NAME,
  G.NAME "GROUP"
FROM
  SUPPLIERS S, SUPPLIER_GROUPS G
WHERE G.ID = S.GROUP_ID;