테이블 관리 (2)


1. 컬럼 추가

1-1. 컬럼 추가 문법

1
2
3
ALTER TABLE TABLE_NAME
 ADD COLUMN COL_NAME_1 SETTING
 ADD COLUMN COL_NAME_2 SETTING

1-2. 컬럼 추가 실습


ORACLE (오라클):

  • create, drop, alter 명령어 (DDL)를 치는 순간 자동으로 commit이 됩니다. 즉, commit 할 필요가 없습니다.
  • 하지만 delete, update, merge, insert 등 명령어는 commit 필요

PostgreSQL:

  • create, drop, alter, delete, update, merge, insert 명령어 모두 commit 을 필요합니다.

(1) 아래와 같은 테이블을 생성한다

1
2
3
4
CREATE TABLE TB_CUST(
  CUST_ID SERIAL PRIMARY KEY,
  CUST_NAME VARCHAR(50) NOT NULL
);

image-20201210094138951



(2) 폰변호를 저장할 컬럼을 추가한다

1
2
ALTER TABLE TB_CUST
 ADD COLUMN PHONE_NUMBER VARCHAR(13);

image-20201210094238362



(3) 팩스번호 및 이메일 주소를 저장할 컬럼을 추가한다. (한 번에 2개 추가)

1
2
3
ALTER TABLE TB_CUST
 ADD COLUMN FAX_NUMBER VARCHAR(13),
 ADD COLUMN EMAIL_ADDR VARCHAR(50);

image-20201210094317700



>> TB_CUST의 생성 과정(DDL) 확인

image-20201210095902038

(4) NOT NULL 제약 컬럼 추가

>> 문제

테이블에 데이터가 있을 때는 NOT NULL 제약 컬럼을 바로 추가할 수 없다.

추가하는 순간 해당 컬럼에 값이 없어서 NOT NULL 제약을 위반하게 되기 때문이다.


[[ 실습 ]]

  • 먼저 테이블에 데이터를 입력한다

    1
    2
    3
    4
    5
    INSERT INTO TB_CUST
    VALUES
    (1, '홍길동', '010-1234-5678', '02-123-1234', 'dbmsexpert@naver.com');

    COMMIT;
    image-20201210103454365

  • 데이터를 입력 후 아래와 같이 NOT NULL 컬럼을 추가하면 기존에 레코드가 있기 때문에 ERROR가 발생한다.

    1
    2
    ALTER TABLE TB_CUST
     ADD COLUMN ADDRESS VARCHAR NOT NULL;
    image-20201210105714436

>> 해결

이런 경우 해결책은 우선 NULL 조건으로 컬럼을 추가한 다음, 컬럼 값을 부여하는 UPDATE 작업을 진행하고 다시 NOT NULL 제약을 추가하는 것이다.


[[ 실습 ]]

  • 먼저 NULL 조건으로 컬럼을 추가한다.

    1
    2
    ALTER TABLE TB_CUST
     ADD COLUMN ADDRESS VARCHAR NULL;

  • 그 다음 ADDRESS 컬럼을 UPDATE 한다. (컬럼 값 부여)

    1
    2
    3
    4
    5
    UPDATE TB_CUST
       SET ADDRESS = '서울시 영등포구'
     WHERE CUST_ID = 1;
     
    COMMIT;


    image-20201210142716523

  • 마지막으로 ALTER COLUMN으로 NOT NULL로 제약 조건을 준다.

    1
    2
    ALTER TABLE TB_CUST
    ALTER COLUMN ADDRESS SET NOT NULL;
    image-20201210142834604


2. 컬럼 제거

2-1. 컬럼 제거 문법

1
2
3
4
ALTER TABLE TABLE_NAME
  DROP COLUMN COL_NAME_1,
  DROP COLUMN COL_NAME_2, 
  ... ;

1
2
3
-- CASCADE 옵션: 해당 컬럼과 관련 있는 모든 개체들이 함께 삭제(DROP)된다.
ALTER TABLE TABLE_NAME
  DROP COLUMN COL_NAME CASCADE;

2-2. 컬럼 제거 실습

>> 실습 준비

1
2
3
4
CREATE TABLE PUBLISHERS (
  PUBLISHER_ID SERIAL PRIMARY KEY,
  NAME VARCHAR NOT NULL
);
1
2
3
4
CREATE TABLE CATEGORIES (
  CATEGORY_ID SERIAL PRIMARY KEY,
  NAME VARCHAR NOT NULL
);
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE BOOKS (
  BOOK_ID SERIAL PRIMARY KEY,
  TITLE VARCHAR NOT NULL,
  ISBN VARCHAR NOT NULL,
  PUBLISHED_DATE DATE NOT NULL,
  DESCRIPTION VARCHAR,
  CATEGORY_ID INT NOT NULL,
  PUBLISHER_ID INT NOT NULL,
  FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORIES (CATEGORY_ID),
  FOREIGN KEY (PUBLISHER_ID) REFERENCES PUBLISHERS (PUBLISHER_ID)
);
image-20201217140819313

3개의 TABLE을 하나의 뷰로 생성한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE VIEW BOOK_INFO AS 
SELECT
  B.BOOK_ID,
  B.TITLE,
  B.ISBN,
  B.PUBLISHED_DATE,
  P.NAME
FROM
  BOOKS B,
  PUBLISHERS P
WHERE
  P.PUBLISHER_ID = B.PUBLISHER_ID
ORDER BY 
  B.TITLE;

image-20201217140850918



>> 컬럼 제거 실습

image-20201217140819313

관계:

  • 한 개의 카테고리가 여러 개의 책을 가진다. 한 개의 책은 반드시 카테고리를 가진다.
  • 한 개의 출판사는 여러 개의 책을 출판한다. 한 개의 책은 반드시 출판사를 가진다.

[실습 1] BOOKS 테이블에서 CATEGORY_ID 컬럼을 제거한다

1
SELECT * FROM BOOKS;

image-20201217142424062


1
2
3
ALTER TABLE BOOKS DROP COLUMN CATEGORY_ID;

SELECT * FROM COOKS;

image-20201217142727702

  • BOOKS 테이블은 자식 테이블이므로 CATEGORY_ID 컬럼은 제거가 가능하다.
  • 컬럼이 제거되면서 CATEGORY_ID의 FK (Foreign Key) 도 함께 삭제된다.

[실습 2] BOOKS 테이블에서 PUBLISHER_ID 컬럼을 제거한다

1
ALTER TABLE BOOKS DROP COLUMN PUBLISHER_ID;
image-20201217143419783
  • PUBLISHER_ID 컬럼을 제거하고자 하는 경우 위와 같은 에러가 발생한다.
  • 해당 컬럼은 BOOK_INFO 뷰에서 참조하고 있기 때문이다.

이런 경우에는 CASCADE 옵션을 줘서 삭제한다.

1
2
3
ALTER TABLE BOOKS DROP COLUMN PUBLISHER_ID CASCADE;

SELECT * FROM BOOKS;

image-20201217144023588


1
SELECT * FROM BOOK_INFO;
image-20201217144204332
  • 컬럼 삭제에는 성공했지만 BOOK_INFO 뷰도 같이 DROP 되었다.

[실습 3] 동시에 N개의 컬럼을 제거한다

1
2
3
ALTER TABLE BOOKS
  DROP COLUMN ISBN,
  DROP COLUMN DESCRIPTION;

image-20201217144804982



3. 컬럼 데이터 타입 변경

3-1. 컬럼 데이터 타입 변경 문법

1
2
3
4
ALTER TABLE TABLE_NAME
  ALTER COLUMN COL_NAME_1 TYPE NEW_TYPE,
  ALTER COLUMN COL_NAME_2 TYPE NEW_TYPE,
  ... ;

3-2. 컬럼 데이터 타입 변경 실습

>> 실습 준비

1
2
3
4
5
6
7
8
CREATE TABLE ASSETS (
  ID SERIAL PRIMARY KEY,
  NAME TEXT NOT NULL,
  ASSET_NO VARCHAR(10) NOT NULL,
  DESCRIPTION TEXT,
  LOCATION TEXT,
  DATE_ACQUIRED DATE NOT NULL
);
1
2
3
4
5
6
7
8
9
10
11
INSERT INTO ASSETS (
  NAME,
  ASSET_NO,
  LOCATION,
  DATE_ACQUIRED
)
VALUES
('Server', '10001', 'Server room', '2017-01-01'),
('UPS', '10002', 'Server room', '2017-01-02');

COMMIT;
1
SELECT * FROM ASSETS;

image-20201218185044584


>> 컬럼 데이터 타입 변경 실습

image-20201218185255265

[MISSION 1] NAME, DESCRIPTION, LOCATION 컬럼의 데이터 타입을 TEXT에서 VARCHAR로 바꾸기

1
2
-- 1개 컬럼의 데이터 타입 변경
ALTER TABLE ASSETS ALTER COLUMN NAME TYPE VARCHAR(50);
1
2
3
4
-- 한번에 N개 컬럼의 데이터 타입 변경
ALTER TABLE ASSETS 
  ALTER COLUMN DESCRIPTION TYPE VARCHAR(100),
  ALTER COLUMN LOCATION TYPE VARCHAR(500);
image-20201218190150074

[MISSION 2] ASSET_NO 컬럼의 데이터 타입을 VARCHAR 에서 INT로 바꾸기

  • 그냥 TYPE INT 로 바꾸면 ERROR 가 발생한다

    1
    ALTER TABLE ASSETS ALTER COLUMN ASSET_NO TYPE INT;
    image-20201218190725819

  • [주의] USING col_name::integer 구문을 추가해야 함

    1
    2
    ALTER TABLE ASSETS
      ALTER COLUMN ASSET_NO TYPE INT USING ASSET_NO::INTEGER;
    image-20201218190942598


4. 컬럼 이름 변경

4-1. 컬럼 이름 변경 문법

1
2
ALTER TABLE TABLE_NAME
  RENAME COLUMN COL_NAME_OLD TO COL_NAME_NEW;

4-2. 컬럼 이름 변경 실습

>> 실습 준비

1
2
3
DROP TABLE CUSTOMER_GROUPS;
DROP TABLE CUSTOMERS;
DROP VIEW CUSTOMER_DATA;
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE CUSTOMER_GROUPS (
  ID SERIAL PRIMARY KEY,
  NAME VARCHAR NOT NULL
);

CREATE TABLE CUSTOMERS (
  ID SERIAL PRIMARY KEY,
  NAME VARCHAR NOT NULL,
  PHONE VARCHAR NOT NULL,
  EMAIL VARCHAR,
  GROUP_ID INT,
  FOREIGN KEY (GROUP_ID) REFERENCES CUSTOMER_GROUPS (ID)
);
1
SELECT * FROM CUSTOMER_GROUPS;

image-20201218220746648

1
SELECT * FROM CUSTOMERS;

image-20201218220814142


1
2
3
4
5
6
7
8
9
CREATE VIEW CUSTOMER_DATA
AS SELECT
  C.ID,
  C.NAME,
  G.NAME CUSTOMER_GROUP
FROM 
  CUSTOMERS C,
  CUSTOMER_GROUPS G
WHERE C.GROUP_ID = G.ID;
1
SELECT * FROM CUSTOMER_DATA;

image-20201218220315994


>> 컬럼 이름 변경 실습

[MISSION 1] CUSTOMERS 테이블 EMIAL 컬럼의 이름을 바꾸기: EMAIL --> CONTACT_EMAIL

1
2
ALTER TABLE CUSTOMERS
  RENAME COLUMN EMIAL TO CONTACT_EMAIL;
1
SELECT * FROM CUSTOMERS;

image-20201218221154310


[MISSION 2] CUSTOMER_GROUPS 테이블의 NAME 컬럼의 이름을 바꾸기: NAME --> GROUP_NAME

1
2
ALTER TABLE CUSTOMER_GROUPS
  RENAME COLUMN NAME TO GROUP_NAME;
1
SELECT * FROM CUSTOMER_GROUPS;

image-20201218221509265


뷰 CUSTOMER_DATA 에서 참조중인 CUSTOMER_GROUP 테이블의 NAME 컬럼의 이름도 바뀌었는지 살펴본다

1
SELECT * FROM CUSTOMER_DATA;

image-20201218221834951

  • 컬럼명이 바뀐 것이 뷰에 자동으로 적용된 것을 확인할 수 있다.