[MariaDB][Admin]AUTO_INCREMENT를 활용한 PK구성 방법

[테스트 환경]

  - Windows 7

  - MariaDB 10.1.12 

1. AUTO_INCRMENT컬럼의 PK 활용 방법

  - 오라클의 SEQUENCE는  테이블에 종속되지 않고 사용 할수 있는 반면 MariaDB(Mysql)는 테이블 종속적으로 SEQUENCE처럼 사용 가능한 AUTO_INCREMENT 기능이 존재 한다. 오늘은 AUTO_INCREMENT를 활용하여 PK를 구성방법에 대해서 얘기하려고 한다.

1.1. AUTO_INCREMENT컬럼 단일 PK

  - AUTO_INCREMENT컬럼을 사용하여 단일 PK를 구성하는 것은 전혀 문제가 없음

CREATE TABLE DA_AUTO_INCREMENT_TEST(
  DA_NO int(11) NOT NULL AUTO_INCREMENT,
  DA_ID VARCHAR(11) NOT NULL,
  DA_NM VARCHAR(20) ,
  PRIMARY KEY(DA_NO) /*AUTO_INCREMENT컬럼 단일 PK */
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.2. AUTO_INCREMENT컬럼이 선행컬럼으로 복합 PK

  - AUTO_INCREMENT컬럼이 선행컬럼으로 복합 PK를 구성하는 것도 전혀 문제가 없음 

CREATE TABLE DA_AUTO_INCREMENT_TEST(
  DA_NO int(11) NOT NULL AUTO_INCREMENT,
  DA_ID VARCHAR(11) NOT NULL,
  DA_NM VARCHAR(20) ,
  PRIMARY KEY(DA_NO,DA_ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.3. AUTO_INCREMENT컬럼을 제외한 단일 PK

  - AUTO_INCREMENT컬럼을 제외하고 PK컬럼을 구성하려고 할때 에러 발생.

CREATE TABLE DA_AUTO_INCREMENT_TEST(
  DA_NO int(11) NOT NULL AUTO_INCREMENT,
  DA_ID VARCHAR(11) NOT NULL,
  DA_NM VARCHAR(20) ,
  PRIMARY KEY(DA_ID,DA_NO)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

/* 
  에러 발생 : Incorrect table definition; there can be only one auto column and it must be defined as a key
*/

1.4. AUTO_INCREMENT컬럼이 후행컬럼으로 복합 PK

  - AUTO_INCREMENT컬럼을 후행컬럼으로 PK컬럼을 구성하려고 할때 에러 발생.

 CREATE TABLE DA_AUTO_INCREMENT_TEST(
 DA_NO int(11) NOT NULL AUTO_INCREMENT,
  DA_ID VARCHAR(11) NOT NULL,
  DA_NM VARCHAR(20) ,
  PRIMARY KEY(DA_ID,DA_NO)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/* 
  에러 발생 : Incorrect table definition; there can be only one auto column and it must be defined as a key
*/

1.5. AUTO_INCREMENT컬럼  후행컬럼으로 결합 PK  + AUTO INCREMENT컬럼 UNIQUE인덱스 추가

  - AUTO_INCREMENT컬럼에 UNIQUE인덱스를 생성함으로써 PK구성의 제약조건이 해결됨

CREATE TABLE DA_AUTO_INCREMENT_TEST(
  DA_NO int(11) NOT NULL AUTO_INCREMENT,
  DA_ID VARCHAR(11) NOT NULL,
  DA_NM VARCHAR(20) ,
  PRIMARY KEY(DA_ID,DA_NO),
  UNIQUE KEY DA_AUTO_INCREMENT_TEST_IDX01(DA_NO) /*핵심*/
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.6. AUTO_INCREMENT컬럼을 제외한 단일 PK  + AUTO INCREMENT컬럼 UNIQUE인덱스 추가

  - AUTO_INCREMENT컬럼에 UNIQUE인덱스를 생성함으로써 PK구성의 제약조건이 해결됨

CREATE TABLE DA_AUTO_INCREMENT_TEST(
  DA_NO int(11) NOT NULL AUTO_INCREMENT,
  DA_ID VARCHAR(11) NOT NULL,
  DA_NM VARCHAR(20) ,
  PRIMARY KEY(DA_ID),
  UNIQUE KEY DA_AUTO_INCREMENT_TEST_IDX01(DA_NO) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2. 결론 

  -  AUTO_INCREMENT컬럼은 UNIQUE KEY로 생성 될 경우, 결합 PK의 후행컬럼으로 생성되거나 PK에서 제외 되어도 상관 없다.

  - 단, 결합PK에서 선행컬럼으로 구성 할 때는 별도의 UNIQUE인덱스가 필요 없는데, 결합PK에서 후행컬럼으로 구성 할 때는 필요하다. 데이터의 유일성 때문에라면 선행이든 후행이든 모두 UNIQUE 인덱스가 필요 할 것인데 좀 더 확인이 필요 하다.