[MariaDB] 4. DDL - table schema 조회, 생성, 삭제

DDL - table schema

Data Definition Language

table은 RDBMS에서 사용하는 데이터 모델로, 엑셀과 같은 2차원 형태의 Object입니다.

table은 실질적으로 데이터가 저장된 객체로, RDBMS에서 CRUD를 행하는 대상입니다.

이번 포스팅에서는 table schema를 조회/생성/삭제하는 방법을 알아볼 것입니다.

1. table schema 조회
2. table schema 생성
   1. table_option
   2. create_definition
      1. 컬럼 정의
         1. 컬럼 데이터 타입
         2. 컬럼 옵션
         3. 컬럼 정의 예시
      2. 인덱스 정의
         1. PLAIN 인덱스 정의
         2. PK 정의
         3. UNIQUE 인덱스 정의
         4. FK 정의
         5. FK 정의 - ON DELETE reference_option
         6. FK 정의 - ON UPDATE reference_option
      3. CHECK 제약조건 정의
3. table schema 삭제

---

1. table schema 조회

테이블 스키마를 조회하는 방법은 2가지 있습니다.

• DESC 테이블명;
• SHOW CREATE TABLE 테이블명;

두 방식의 차이점은 다음의 예제를 통해 알아보도록 합시다.
아래의 ddl을 이용하여 생성된 users 테이블이 있다고 할 때,

create table users (
  id int(10) unsigned auto_increment,
  name varchar(255) not null,
  age int(10) unsigned,
  status char(1) default 'Y',
  primary key(id),
  constraint CK_AGE_20 check(age > 20)
) comment '사용자';

desc로 조회한 결과는 아래와 같고

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

show create table로 조회한 결과는 아래와 같습니다.

show create table users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` int(10) unsigned DEFAULT NULL,
  `status` char(1) COLLATE utf8_unicode_ci DEFAULT 'Y',
  PRIMARY KEY (`id`),
  CONSTRAINT `CK_AGE_20` CHECK (`age` > 20)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='사용자'

desc sql은 표형태로 출력되어 한눈에 알아보기 편하다는 장점이 있으나, 주요 제약조건만 출력된다는 단점이 있고,
show create table sql은 한눈에 알아보기는 불편하나, check 제약조건을 비롯한 모든 제약조건을 볼 수 있다는 장점이 있습니다.

필요에 따라 두 sql 중 이용하면 됩니다.

---

2. table schema 생성

테이블 스키마를 생성하는 방법은 아래와 같습니다.

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options    ]...
    [partition_options]

create_definition:
  { col_name column_definition | index_definition | period_definition | CHECK (expr) }

필수적으로 입력해야 create_definition 구문과 필요에 따라 추가적으로 설정하는 table_option(테이블 옵션), partition_options(파티셔닝 옵션)이 있습니다.

※ 파티셔닝 옵션은 파티셔닝에 관하여 다룰 때 따로 다룰 예정으로 이번 포스팅에서는 설명하지 않습니다.

2.1. table_option

매우 다양한 table_option이 존재하지만, 이번 포스팅에서는 테이블을 생성 후 SHOW CREATE TABLE sql에서 확인되는 옵션들만 설명하도록 하겠습니다.

• ENGINE [=] engine_name
  • storage engine 종류
• AUTO_INCREMENT
  • auto_increment 설정된 PK의 다음 id값
• [DEFAULT] CHARACTER SET [=] charset_name
  • 테이블의 기본 문자 인코딩 방식
  • ex) utf8, utf8mb4
• [DEFAULT] COLLATE [=] collation_name
  • 데이터 정렬 방식
  • ex) utf8_general_ci, utf8mb4_general_ci
• COMMENT [=] 'string'
  • 테이블에 관한 설명

테이블 생성시 스토리지엔진 종류, 기본 문자인코딩방식 및 데이터 정렬방식 옵션은 별도로 지정하지 않아도 mariaDB 버전별 기본값으로 설정됩니다.
기본 설정값을 변경하고 싶으면 my.cnf을 수정한 후 MariaDB 서버를 재시작 하면됩니다.

---

2.2. create_definition

테이블 생성에서 반드시 설정해야할 정의 부분으로 컬럼, 인덱스, 체크 제약조건을 설정하는 곳입니다.

create_definition:
  { col_name column_definition | index_definition | period_definition | CHECK (expr) }

2.2.1. 컬럼 정의

컬럼은 컬럼명 데이터타입 [컬럼옵션] 형식으로 정의합니다.

컬럼옵션은 필요에 따라 설정하는 옵션값이지만, 옵션을 잘 설정해두면 부적절한 데이터가 데이터베이스로 저장되지 않도록 사전에 방지할 수 있습니다.

column_definition:
  data_type
    [NOT NULL | NULL] [DEFAULT default_value | (expression)]
    [ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]
    [AUTO_INCREMENT] [ZEROFILL] [UNIQUE [KEY] | [PRIMARY] KEY]
    [INVISIBLE] [{WITH|WITHOUT} SYSTEM VERSIONING]
    [COMMENT 'string'] [REF_SYSTEM_ID = value]
    [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
    [reference_definition]
  | data_type [GENERATED ALWAYS]
  AS { { ROW {START|END} } | { (expression) [VIRTUAL | PERSISTENT | STORED] } }
      [UNIQUE [KEY]] [COMMENT 'string']


constraint_definition:
   CONSTRAINT [constraint_name] CHECK (expression)

2.2.1.1. 컬럼 데이터 타입

• 정수형
  • tinyint
    • 1byte ($2^8$)
    • signed : -128(-$2^7$) ~ 127($2^7$-1)
    • unsigned : 0 ~ 255($2^8$-1)
  • int
    • 4byte ($2^{32}$)
    • signed : -21억($2^{31}$) ~ 21억($2^{31}$-1)
    • unsigned : 0 ~ 43억($2^{32}$-1)
  • bigint
    • 8byte ($2^{64}$)
    • signed : -9경($2^{63}$) ~ 9경($2^{63}$-1)
    • unsigned : 0 ~ 18경($2^{64}$-1)
• 실수형(길이, 소수점 이하 자리수)
  • float(size, d)
    • 4byte 부동소수점
  • double(size, d)
    • 8byte 부동소수점
  • decimal(size, d)
    • 고정소수
• 문자열
  • char
    • 고정길이 문자열
    • 최대 255자
  • varchar
    • 가변길이 문자열
    • 최대 65,535자
• text 문자열
  • text
    • 최대 65,636자
  • mediumtext
    • 최대 16,777,215자
  • longtext
    • 최대 4,294,967,295자
• blob(Binary Large Object)
  • blob
    • 최대 65,535자
  • mediumblob
    • 최대 16,777,215자
  • longblob
    • 최대 4,294,967,295자
• 날짜, 시간 타입
  • date
    • yyyy-MM-dd
  • time
    • hh:mm:ss
  • datetime
    • yyyy-MM-dd hh:mm:ss
  • timestamp
    • yyyy-MM-dd hh:mm:ss

text문자열의 경우 다른 DBMS에서는 CLOB라는 데이터타입으로 이용되기도 합니다.
날짜, 시간타입은 Unix Epoch(1970-01-01 00:00:00) 이후의 시간을 표현하는데, 만일 Unix Epoch 이전의 시간을 사용하고 싶다면 별도의 설정을 해야 합니다.

2.2.1.2. 컬럼 옵션

• [NOT NULL | NULL]
  • NULL 허용 여부
  • NOT NULL(허용하지 않는다), NULL(허용한다)
• [DEFAULT default_value]
  • insert 시 값을 지정하지 않을 경우 설정될 기본값
• [ON UPDATE [NOW() | CURRENT_TIMESTAMP()]]
  • row 업데이트시 현재시각으로 업데이트합니다.
• [AUTO_INCREMENT]
  • 해당 컬럼을 자동증가 시킵니다.
  • 인덱스가 설정된 컬럼에만 설정할 수 있는 컬럼옵션입니다.
• [ZEROFILL]
  • 정수 데이터타입에서 설정한 자릿수만큼 0을 채웁니다.
  • 데이터타입에 unsigned를 자동으로 설정합니다.
• [[UNIQUE [KEY]] | [PRIMARY] KEY]
  • PK와 UNIQUE키는 테이블 내에서 중복을 허용하지 않는 키입니다.
  • PRIMARY KEY
    • 관례적으로 컬럼명은 id, 데이터타입은 int로 설정하며, AUTO_INCREMENT 제약조건도 함께 설정합니다. (CoC)
    • PK를 이용하여 테이블 내에서 row를 반드시 식별할 수 있습니다.
    • row 식별을 위해 NULL을 허용하지 않습니다.(= NOT NULL 포함)
    • 테이블 내에서 여러개의 PK를 설정할 수 없습니다.
  • UNIQUE
    • 중복을 허용하지 않는 그밖의 컬럼들(ex. email)에 설정합니다.
• [COMMENT 'string']
  • 컬럼에 대한 설명
• [CHARACTER SET charset_name]
  • 해당 컬럼에 설정할 인코딩 방식
• [COLLATE collation_name]
  • 해당 컬럼에 설정할 데이터 정렬 방식
• [CHECK expression]
  • 컬럼 제약조건
    
    

컬럼 정의시 PK나 INDEX를 바로 설정할수도 있습니다.

다만, 컬럼 옵션으로 인덱스 설정하는 것은 단일 컬럼 인덱스 만 가능하며, 인덱스명도 설정할 수 없기 때문에 가급적 인덱스 정의를 통해 정의하기를 권장합니다.

마찬가지로 다중 컬럼 PK를 설정하고자 한다면 인덱스 정의에서 해야합니다.

컬럼에 설정하는 제약조건은 CHECK 절을 통해 설정할 수 있는데, 컬럼에 직접 설정하는 경우 CONSTRAINT로 생성되지 않고 컬럼 자체에 생성됩니다.(다른 테이블에서 재사용 불가)

2.2.1.3. 컬럼 정의 예시

CREATE TABLE test_users (
  id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email varchar(100) NOT NULL UNIQUE,
  name varchar(100) DEFAULT NULL,
  month tinyint(2) ZEROFILL DEFAULT 02 CHECK (month <= 12),
  active tinyint(1) UNSIGNED DEFAULT 1 COMMENT '1: 일반, 0: 탈퇴',
  create_dt datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  memo varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '메모'
);

※ 테이블명은 복수형으로 만드는(user → users) 관례가 있으니 참고해주세요 (CoC)

---

2.2.2. 인덱스 정의

PK나 FK, 그리고 기타 INDEX를 정의하는 방법을 알아봅시다.
INDEX는 또다른 말로 KEY라고도 불리우는데, PK와 FK를 제외하고는 INDEX와 KEY를 혼용해서 사용할 수 있습니다.

index_definition:
    {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | CLUSTERING={YES| NO}

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

인덱스 타입에 BTREE, HASH, RTREE가 있지만, InnoDB 스토리지 엔진에서는 BTREE 만을 지원 하기 때문에 InnoDB를 사용할 경우에는 고려할 필요가 없습니다.

InnoDB는 Transaction을 지원하는 스토리지 엔진으로, 주로 사용하는 스토리지 엔진입니다.
이 밖에 MyISAM과 MyISAM의 업그레이드버전인 Aria 스토리지 엔진과 MEMORY, HEAP이 있습니다.

인덱스는 RDBMS에서 매우 중요한 요소로 자세히 다뤄야할 부분이 방대하므로, 이번 포스팅에서는 가장 빈번히 이용되는 PK, FK, UNIQUE, 일반 인덱스 에 관해서만 소개하도록 하고 나머지 사항은 인덱스 포스팅에서 상세히 다루도록 하겠습니다.

2.2.2.1. PLAIN 인덱스 정의

{INDEX|KEY} [index_name] [index_type] (index_col_name,...)

인덱스는 CONSTRAINT 절 없이 이름을 정의합니다.
인덱스명 설정을 생략할 경우 MariaDB에서 자동으로 인덱스명을 생성하지만, 관리의 용이를 위해 명명규칙을 세워서 직접 설정하는 것을 권장합니다.

위에서 소개했듯, InnoDB 스토리지 엔진은 BTREE 타입만을 지원하기 때문에 index_type은 설정하지 않아도 됩니다.
(나머지 인덱스들도 마찬가지이며, 이후부터는 이에 대한 설명은 생략합니다.)

• 예시
  • INDEX IX_TEST_USERS_NAME (name)
  • INDEX IX_USER_CARDS_USER_ID_CARD_ID (user_id, card_id)

2.2.2.2. PK 정의

PK는 테이블 내에서 row를 대표하는 식별자를 정의하는 인덱스입니다.

PRIMARY KEY [index_type] (index_col_name,...) [index_option]

PK는 테이블 내에서 단 하나만 정의할 수 있습니다.
최신버전 MariaDB에서는 PK명을 설정하는 기능은 제거되었습니다.

• 예시
  • PRIMARY KEY (id)
  • PRIMARY KEY (user_id, role_name)

2.2.2.3. UNIQUE INDEX 정의

UNIQUE INDEX(KEY)는 중복을 허용하지 않는 컬럼에 설정하는 인덱스입니다.

[CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option]

INDEX 키워드는 생략할 수 있습니다.
인덱스명은 UNIQUE키워드 앞에 CONSTRAINT index_name로 설정하거나 UNIQUE키워드 뒤에 설정하면 됩니다.

• 예시
  • CONSTRAINT UX_TEST_USERS_EMAIL UNIQUE(email)
  • UNIQUE KEY UX_TEST_USERS_EMAIL(email)
  • UNIQUE(user_id, role_name)

2.2.2.4. FK 정의

FK는 다른 테이블의 PK를 참조하는 인덱스입니다.

FK명을 입력하지 않으면 DB버전별 명명규칙에 따라 자동으로 FK명이 생성되지만, 관리의 용이를 위해 직접 설정하는 것을 권장합니다.
FK명은 FK_참조테이블명_테이블명로 짓는것이 관례입니다.(CoC)

[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

UNIQUE 인덱스와 마찬가지로 인덱스명을 FOREIGN KEY 키워드 앞에 CONSTRAINT index_name로 설정하거나 FOREIGN KEY 키워드 뒤에 설정할 수 있습니다.

FK 설정구문에는 참조옵션을 설정하는 구문이 있는데, 참조하고 있는 키가 수정되거나 삭제되었을때 취할 행동을 정의하는 옵션입니다.

기본 값은 RESTRICT로, 참조되고 있는 row는 제거 시도시 에러를 발생시킵니다.(RESTRICT로 설정되어있는 경우, 해당키를 참조하고 있는 row를 제거한 후 삭제를 진행해야 합니다.)

2.2.2.5. FK 정의 - 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값으로 설정됩니다.

2.2.2.6. FK 정의 - 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로 업데이트 합니다.
  • 

• PK 및 FK 정의 예시

  • CREATE TABLE stores (
      id int(10) unsigned NOT NULL,
      user_id int(10) unsigned DEFAULT NULL,
      name varchar(255) NOT NULL,
      store_code varchar(255),
      PRIMARY KEY (id),
      FOREIGN KEY FK_USERS_STORES(user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
    );
    

  • CREATE TABLE user_roles (
      user_id int(11) unsigned NOT NULL,
      role_name varchar(50) NOT NULL,
      create_dt datetime DEFAULT current_timestamp(),
      PRIMARY KEY (user_id, role_name),
      FOREIGN KEY FK_USER_ROLES_USERS(user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
    );
    

2.2.2.7. FK 정의 - MATCH 옵션fixme

추후 내용 추가...

---

2.2.3. CHECK 제약조건 정의

CONSTRAINT [constraint_name] CHECK (expression)

month가 들어가는 컬럼에 12이하의 수만 들어가게 한다던가
특정 날짜보다 작은 timestamp만 들어갈 수 있는 등의 제약을 설정할 수 있습니다.

CHECK 제약조건으 컬럼옵션으로 설정하지 않고 직접 정의할 경우 자동으로 CHECK제약조건의 이름이 설정됩니다.

CHECK CONSTRAINT는 여러 테이블에서 사용할 수 있으므로(ex. month컬럼을 가진 다른 테이블에도 동일한 제약조건을 설정) 인덱스 명명규칙을 설정하여 관리하는것을 권장합니다.

• 예시

  • CREATE TABLE test_users (
      id int UNSIGNED AUTO_INCREMENT ,
      email varchar(100) NOT NULL UNIQUE,
      name varchar(100) DEFAULT NULL,
      month tinyint(2) ZEROFILL DEFAULT 02,
      active tinyint(1) UNSIGNED DEFAULT 1 COMMENT '1: 일반, 0: 탈퇴',
      sex char(1) NOT NULL DEFAULT '1',
      create_dt datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
      memo varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '메모',
      PRIMARY KEY(id),
      CONSTRAINT CK_MONTH CHECK (1 <= month and month <= 12),
      CONSTRAINT CK_SEX CHECK (sex = '1' or sex = '2')
    );
    

---

3. table schema 삭제

DROP TABLE 절을 이용하여 테이블 스키마를 제거합니다.
데이터 뿐만아니라 구조(스키마)도 함께 제거되므로 주의해야 합니다.

DROP TABLE tbl_name;