[MariaDB] 5. DDL - table schema 변경

DDL - table schema 변경

1. 테이블명 변경
2. 테이블 옵션 변경
3. 컬럼 설정 변경
   1. 컬럼 추가 (ADD COLUMN)
   2. 컬럼 제거 (DROP COLUMN)
   3. 컬럼 변경
      • ALTER : 기본값만 변경
      • MODIFY : 컬럼정의 & 기본값 변경
      • CHANGE : 컬럼명 & 컬럼정의 & 기본값 변경
      • RENAME COLUMN : 컬럼명 변경
4. 인덱스 설정 변경 1. PK
   1. PK 설정 (ADD PRIMARY KEY)
   2. PK 제거 (DROP PRIMARY KEY)
5. 인덱스 설정 변경 2. FK
   1. FK 추가 (ADD FOREIGN KEY)
      1. ON DELETE reference_option
      2. ON UPDATE reference_option
   2. FK 제거 (DROP FOREIGN KEY)
6. 인덱스 설정 변경 3. INDEX
   1. INDEX 추가(+ UNIQUE)
   2. INDEX 제거
   3. INDEX 명 변경

---

table schema(구조)를 변경하는 ddl을 알아봅시다.

컬럼을 추가, 변경 또는 제거하거나 컬럼에 PK/FK/INDEX를 설정하거나 설정된 INDEX를 해제하는 방법이 이에 해당됩니다.

ALTER [ONLINE] [IGNORE] TABLE [IF EXISTS] tbl_name
  [WAIT n | NOWAIT]
  alter_specification [, alter_specification] ...

alter_specification은 테이블 옵션 설정, 컬럼 추가/삭제/변경, {PK|FK|인덱스} 추가/삭제, {인덱스명|컬럼명} 변경 등 다양한 명세를 제공합니다.

스키마 변경은 ,로 구분지어 여러 설정을 한꺼번에 변경하는 것이 가능합니다.

ALTER TABLE 절을 이용하여 테이블 스키마를 변경하는 문법과 옵션들은 매우 방대하기 때문에 이 포스팅에서는 데이터베이스 사용중 자주 이용되는 옵션들에 대해서만 다룹니다.

---

1. 테이블명 변경

RENAME TABLE [IF EXISTS] tbl_name
  [WAIT n | NOWAIT]
  TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

IF EXISTS 절을 이용할 경우, 기존 테이블명이 존재하지 않을시 테이블명 변경을 시도하지 않아 에러 발생을 하지 않습니다.

※ 단, IF EXISTS 절은 MariaDB 10.5.2 버전에서 새로 추가된 구문으로, 이하버전을 사용할 경우에는 해당 문법을 사용하면 안됩니다.

rename table if exists users to users2;

---

2. 테이블 옵션 변경

아래의 sql을 통해 테이블 옵션을 변경하는 방법을 알아봅시다.

ALTER TABLE users
  AUTO_INCREMENT = 1,
  CHARSET = utf8,
  COLLATE = utf8_unicode_ci
  COMMENT = '사용자';

• AUTO_INCREMENT
  • 자동 증가 설정된 INDEX의 다음 인덱스값
  • 1로 설정해도 에러 없이 테이블 내의 MAX(id) + 1 로 설정되므로 편의상 AUTO_INCREMENT = 1로 설정해도 됩니다.
• CHARSET
  • 테이블 기본 문자 인코딩 방식
  • 컬럼에 직접 CHARSET을 설정했을 경우 테이블의 CHARSET은 영향을 주지 못하니 주의해야 합니다.
• COLLATE
  • 테이블 기본 정렬 방식
  • CHARSET와 마찬가지로, COLLATE가 직접 설정된 컬럼에는 영향을 주지 못합니다.
• COMMENT
  • 테이블 설명

alter_specification에 설정할 수 있는 ddl 문법에 대해 더 자세히 알고 싶다면 MariaDB Server Documentation - ALTER TABLE Syntax를 참고해주세요.

---

3. table schema 변경 - 컬럼

3.1. 컬럼 추가 (ADD COLUMN)

... ADD COLUMN [IF NOT EXISTS] (col_name column_definition, ...);

위에서 생성한 users 테이블에 컬럼 몇개를 추가해봅시다.

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS
  (`created_dt` DATETIME,
    `status` CHAR(2),
    `type` CHAR(1));

MariaDB에서는 IF NOT EXISTS 조건절을 제공하기 때문에 테이블에 없는 컬럼만 추가하여 유연합니다.
(기존에 동일한 이름의 컬럼이 존재할 경우 에러 메시지 출력 없이 해당 컬럼의 추가를 pass 합니다.)

desc users;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| age        | int(11)      | YES  |     | NULL    |                |
| status     | char(1)      | YES  |     | Y       |                |
| created_dt | datetime     | YES  |     | NULL    |                |
| type       | char(1)      | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

3.2. 컬럼 제거 (DROP COLUMN)

... DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT];

컬럼에 인덱스가 설정되어있을 경우, 컬럼이 제거된`` 후 컬럼에 설정된 인덱스가 제거됩니다.
특수 인덱스 중 UNIQUE 키와 단일 컬럼 PK도 제거가 가능한데, PK의 경우 다른 테이블에서 참조하고 있을 경우(= 다른 테이블에서 FK로 사용되고 있는 경우) 삭제가 되지 않습니다.

multi-column UNIQUE 키나 PK는 DROP COLUMN sql로 제거할 수 없습니다.

따라서, 아래의 테이블에서 role_name 컬럼을 바로 삭제하는것은 불가능 합니다.

CREATE TABLE `user_roles` (
  `user_id` int(11) NOT NULL,
  `role_name` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `create_dt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`user_id`,`role_name`),
  CONSTRAINT `FK_USER_ROLES_USERS` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

다중 컬럼 UNIQUE 및 PK 제약조건이 설정된 컬럼을 제거하고자 한다면, 먼저 해당 제약조건을 제거해야합니다.

그리고 FK도 마찬가지로 FK 제약조건을 먼저 삭제해야 합니다.

※ PK나 UNIQUE 키, FK 제약조건을 삭제하는 sql은 아래를 참고해주세요.

3.3 컬럼 변경

컬럼을 변경하는 ddl은 총 3가지가 있습니다.

• 기본값 변경
• 컬럼정의 & 기본값 변경
• 컬럼명 & 컬럼정의 & 기본값 변경
• 컬럼명 변경

컬럼 정의(column_definition)에는 데이터타입 [제약조건] 가 들어가며, 제약조건을 설정하지 않을시 설정했던 제약조건이 모두 제거되므로, 기존의 제약조건을 유지하고 싶다면 컬럼 변경 ddl 실행시 옵션도 반드시 함께 설정해야 합니다.

컬럼 변경시 기본값을 설정하지않으면 DEFAULT NULL로 설정되므로 기존의 기본값을 유지하고 싶다면 반드시 함께 설정해야 합니다.

컬럼 변경 1 (ALTER) : 기본값만 변경

ALTER TABLE tbl_name ALTER col_name DEFAULT옵션;

alter table ... alter ddl은 DEFAULT 옵션 속성만 변경합니다.

• ddl 예시
  • ALTER TABLE user_roles ALTER role_name DEFAULT 'ROLE_VIEW';
  • ALTER TABLE user_roles ALTER role_name DEFAULT NULL;

컬럼 변경 2 (MODIFY) : 컬럼정의 & 기본값 변경

ALTER TABLE tbl_name
  MODIFY col_name 변경할데이터타입 [제약조건] [DEFAULT옵션];

modify는 컬럼의 데이터 타입만 변경할때 사용하는 sql입니다.
제약조건(= DEFAULT옵션을 제외한 컬럼옵션)을 유지 또는 추가 설정하고 싶을 경우 반드시 함께 써줘야 합니다.

alter table test
  modify id bigint unsigned not null auto_increment;

데이터 타입 변경 시, 기존에 저장된 데이터와 충돌이 있을시 변경이 불가능하므로 주의해야 합니다.
데이터 타입 cast 에러에 관한 간단한 예시를 살펴보도록 합시다

+----+------+------+--------+------------+------+
| id | name | age  | status | created_dt | type |
+----+------+------+--------+------------+------+
|  1 | jini |   29 | Y      | NULL       | 1    |
|  2 | sol  |   33 | Y      | NULL       | 1    |
+----+------+------+--------+------------+------+

• alter table users modify type int;
  • Query OK
  • type컬럼은 varchar타입이지만 내부에 들어있는 값이 int로 cast됙기 때문에 변경됩니다.
• alter table users modify status int;Error!!
  • Truncated incorrect INTEGER value: 'Y'
  • 이미 status에는 int로 cast할 수 없는 문자열이 들어있기 때문에 int로 변경할 수 없습니다.

이 외에도, 저장된 데이터보다 짧은 길이의 varchar로 변경하는 등의 시도시 변경이 되지 않습니다.

컬럼 변경 3 (CHANGE) : 컬럼명 & 컬럼정의 & 기본값 변경

ALTER TABLE tbl_name
 CHANGE old_col_name new_col_name 변경할데이터타입 [제약조건] [DEFAULT옵션];

MODIFY와 마찬가지로 컬럼 옵션을 유지하고 싶다면 함께 설정해줘야 합니다.

• ddl 예시
  • alter table test change id test_id int(10) unsigned not null auto_increment;

컬럼 변경 4 (RENAME COLUMN) : 컬럼명 변경MariaDB 10.5.2 이상 버전부터 제공

ALTER TABLE tbl_name
 RENAME COLUMN old_col_name TO new_col_name;

MariaDB 10.5.2 버전에 추가된 ALTER TABLE ... RENAME COLUMN ... TO ddl에서는 컬럼명만 변경하는 기능을 제공합니다.

기존의 컬럼옵션을 유지하기 위해 일일히 함께 설정하지 않아도 되어서 매우 편리합니다.

• ddl 예시
  • alter table test rename column id to test_id;

---

4. table schema 변경 - PK

4.1. PK 설정

ALTER TABLE tbl_name
 ADD PRIMARY KEY(col_name);

PK를 설정할 컬럼명을 넣어 PK를 생성합니다.
PK는 테이블에서 단하나 존재할 수 있기 때문에 제약조건명을 지정하지 않습니다.

• ddl 예시
  • alter table test add primary key(id);

4.2. PK 제거

PK는 테이블에 하나만 존재할 수 있기 때문에 drop primary key 명령어로 제거할 수 있습니다.

ALTER TABLE tbl_name
 DROP PRIMARY KEY;

다만, primary key로 설정되어있던 키에 auto_increment 제약조건이 설정되어있을 경우, auto_increment 제약조건도 함께 제거해야 PK를 제거할 수 있습니다.

이렇게 한꺼번에 변경해도 되고,

ALTER TABLE tbl_name
  CHANGE col_name col_name data_type [auto_increment를 제외한 컬럼 제약조건],
  DROP PRIMARY KEY;

auto_increment를 제거한 후 primary key를 제거해도 됩니다.

ALTER TABLE tbl_name
  CHANGE col_name col_name data_type [auto_increment를 제외한 컬럼 제약조건]
ALTER TABLE tbl_name
  DROP PRIMARY KEY;

• ddl 예시
  • alter table test change id id int(10) unsigned not null, drop primary key;

---

5. table schema 변경 - FK

5.1. FK 설정

ALTER TABLE tbl_name
  ADD [CONSTRAINT fk_name] FOREIGN KEY(col_name, ...)
  REFERENCES 참조_tbl_name(참조_col_name, ...)
  [ON DELETE reference_option]
  [ON UPDATE reference_option];

reference_option:
  RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

FK명을 입력하지 않으면 DB버전별 명명규칙에 따라 자동으로 FK명이 생성됩니다.
FK명은 FK_참조테이블명_테이블명로 짓는것이 관례입니다.(CoC)

5.1.1. ON DELETE reference_option

FK로 이용되고 있는 참조테이블의 해당 row를 delete 시도시 취할 행동을 설정합니다.

reference_option에 대해서는 아래의 예를 통해 자세히 알아보도록 합시다.
아래 테이블은 user_id는 users 테이블의 id를 FK로 참조하고 있는 test 테이블입니다.

만일 users 테이블에서 delete from users where id = 4;를 행할 경우

• RESTRICT (= No Action)
  • 삭제 시도시 에러가 발생되며 삭제가 이뤄지지 않습니다.
  • 
• CASCADE
  • 참조테이블의 row 삭제 후 FK 설정된 row도 제거 합니다.
  • 
• SET NULL
  • 참조테이블의 row 삭제 후 FK 설정된 컬럼에 NULL로 업데이트 합니다.
  • 

※ reference_option 중 SET DEFAULT 는 PBXT storage engine에서만 지원되는 옵션이기 때문에 소개하지 않습니다. SET NULL 옵션과 유사하며, 참조테이블의 row가 제거될 경우 FK가 설정된 컬럼의 default값으로 설정됩니다.

5.1.2. ON UPDATE reference_option

수정도 삭제와 유사합니다.

만일 users 테이블에서 update users set id = 5 where id = 4;를 행할 경우

• RESTRICT (= No Action)
  • 수정 시도시 에러가 발생되며 수정이 이뤄지지 않습니다.
  • 
• CASCADE
  • 참조테이블의 row 수정 후 FK 설정된 row도 수정 됩니다.
  • 
• SET NULL
  • 참조테이블의 row 수정 후 FK 설정된 컬럼에 NULL로 업데이트 합니다.
  • 

• ddl 예시

  alter table test
    add constraint FK_USERS_TEST foreign key (user_id)
    references users(id)
    on update cascade
    on delete set null;
  

---

4.2. FK 제거

ALTER TABLE tbl_name
  DROP FOREIGN KEY fk_name;

• ddl 예시
  • alter table test drop foreign key FK_USERS_TEST;

※ FK를 수정하는 ddl은 따로 존재하지 않습니다.
따라서, FK 수정을 원할시엔 FK를 제거한 후 FK 추가 ddl을 실행하면 됩니다.

---

6. table schema 변경 - INDEX

6.1. INDEX 추가(+ UNIQUE)

테이블에 INDEX를 추가하는 ddl은 ADD INDEX 입니다.

ALTER TABLE tbl_name
  ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name(index_col_name, ...);

인덱스는 총 4가지 종류가 있으며, 일반 인덱스를 제외한 나머지 인덱스의 경우 INDEX 키워드를 생략하고도 생성할 수 있습니다.

ALTER TABLE tbl_name
  ADD {UNIQUE|FULLTEXT|SPATIAL} [{INDEX|KEY}] index_name(index_col_name, ...);

테이블에 인덱스를 ALTER TABLE ddl로 설정해도 되지만, CREATE INDEX ddl로 직접 생성해도 됩니다.
이때에는 INDEX 키워드를 반드시 함께 사용해야 합니다.

CREATE [OR REPLACE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
  ON tbl_name(index_col_name, ...);

인덱스 명은 그대로 유지하면서 인덱스가 설정된 컬럼만 변경하고 싶다면 CREATE OR REPLACE INDEX를 이용하면 됩니다.(단, 기존 인덱스 타입도 함께 작성해야 합니다.)

• ddl 예시
  • alter table users add unique UX_USERS_EMAIL(email)
  • create or replace unique index UX_USERS_EMAIL on users(id, email);

※ CREATE OR REPLACE INDEX 절은 MariaDB 10.1.4 버전에서 새로 추가된 구문으로, 만일 버전이 낮을 경우에는 기존 인덱스를 삭제한 후 추가해야 합니다.

alter table users
  drop index UX_USERS_EMAIL,
  add unique index UX_USERS_EMAIL(email, status);

※ 참고로 SPATIAL INDEX(공간 인덱스)는 Geometry Type에 설정할 수 있는 인덱스입니다.

Geometry Type: point, linestring, polygon, multipoint, multilinestring, multipolygon, geometrycollection, geometry

6.2. INDEX 제거

UNIQUE INDEX를 비롯한 INDEX를 제거하는 명령어는 다음과 같습니다.

ALTER TABLE tbl_name  
  DROP INDEX index_name;

인덱스는 수정 ddl을 별도로 제공하지 않습니다.

만일 인덱스명의 수정을 원할 경우 기존의 INDEX를 제거한 후 새로 추가해주면 됩니다.

• ddl 예시
  • alter table users drop index UX_USERS_EMAIL;

6.3. INDEX 명 변경 MariaDB 10.5.2 이상 버전부터 제공

RENAME INDEX ... TO 절을 이용하여 인덱스명을 변경합니다.

ALTER TABLE tbl_name  
  RENAME {INDEX|KEY} old_index_name TO new_index_name;

• ddl 예시
  • alter table users rename index UX_USERS_EMAIL to IX_USERS_EMAIL;