[mariadb] auto_increment를 활룡한 primary key 구성방법

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;

 

2. 결론 

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

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

 

매핑테이블에 유니크키 추가로 PK랑 복합으로 엮다가 에러나서 검색한 내용으로 기억남.


출처: https://adbancedteam.tistory.com/149 [aDBanced Team]

감사합니다.