시퀀스에 대해 알아보자 (feat. SEQUENCE, GENERATED AS IDENTITY, IDENTITY, AUTO_INCREMENT)

안녕하세요, 끙정입니다.

 

오늘은 시퀀스에 대해서 알아보고자 합니다.

시퀀스는 DBMS마다 문법이 전부 다르기 때문에 다소 헷갈리고 귀찮을 수 있습니다.

역시나 대표 3인방인 Oracle, SQL Server, MariaDB의 문법을 비교해가면서 살펴보겠습니다.

 

1. 들어가기 전에

기본키는 레코드의 유일성을 보장할 뿐만 아니라 관계를 형성하는 중요한 역할을 합니다.

그러나 기본키가 강제 규정은 아니기도 하고,

마땅한 필드가 없는 경우도 있습니다.

 

이럴때 사용하는 것이 일련번호입니다.

계속 증가하는 일련번호를 붙이면 NULL도 아니고 고유성도 부여할 수 있습니다.

또한 정수형이기 때문에 테이블 용량이 늘어나더라도 크게 부담되지는 않습니다.

 

2. 시퀀스 SEQUENCE (오라클, SQL Server Only)

오라클과 SQL Server는 일련번호를 관리하는 시퀀스 (SEQUENCE) 객체를 지원합니다.

아쉽게도 Maria DB는 지원하지 않습니다.

시퀀스는 객체를 초기화해 놓기만 하면 요청할 때마다 증가한 값을 자동으로 돌려줍니다.

 

/* 옵션이 존재하지만 대부분 DEFAULT 값을 취한다. */
CREATE SEQUENCE 이름 [AS 타입] START WITH 시작값 INCREMENT BY 증가값
MINVALUE 최소값 MAXVALUE 최대값 CYCLE | NOCYCLE CACHE | NOCACHE

 

오라클의 시퀀스는 NUMBER 타입이고,

SQL Server는 BIGINT 타입입니다.

SQL Server는 이름 뒤에 타입을 밝힐 수 있는데 AS INT로 지정하면 메모리를 아낄 수는 있지만 큰 의미는 없습니다.

최소, 최대값의 디폴트는 타입의 표현 범위만큼이고,

CYCLE 옵션을 주면 이 범위를 순환합니다.

CACHE 옵션은 성능 향상을 위해 현재값을 메모리에 저장하는데 디폴트로 선택되어 있습니다.

 

증가값은 디폴트가 1이지만 시작값의 디폴트는 조금 다릅니다.

오라클은 1부터 시작하는데 비해 SQL Server는 범위의 첫값인 음수에서 시작합니다.

DBMS에 상관없이 1부터 순서대로 증가시키고 싶다면 START WITH 옵션을 지정해야 합니다.

 

CREATE SEQUENCE seqSale START WITH 1 INCREMENT BY 1;

 

시퀀스는 테이블과는 상관없는 독립적인 객체이므로 필요 없다면 DROP SEQUENCE 명령으로 직접 삭제해야 합니다.

 

DROP SEQUENCE seqSale;

 

 

아래와 같은 판매 테이블을 생성해보겠습니다.

 

CREATE TABLE tSale
(
    saleno INT PRIMARY KEY,
    customer VARCHAR(10),
    product VARCHAR(10)
);

 

판매번호인 saleno는 INT 타입이며 기본키로 지정했습니다.

레코드를 삽입할 때 시퀀스로부터 일련번호를 구해 saleno 필드에 저장합니다.

그러나 일련번호를 구하는 방법은 Oracle과 SQL Server에서 약간의 차이가 있습니다.

 

/* 오라클은 시퀀스 객체의 NEXTVAL 값을 읽어 구한다. */
INSERT INTO tSale VALUES (seqSale.NEXTVAL, '단군', '지팡이');
INSERT INTO tSale VALUES (seqSale.NEXTVAL, '고주몽', '고등어');

/* SQL Server는 NEXT VALUE FOR 구분으로 다음 일련번호를 구한다. */
INSERT INTO tSale VALUES (NEXT VALUE FOR seqSale, '단군', '지팡이');
INSERT INTO tSale VALUES (NEXT VALUE FOR seqSale, '고주몽', '고등어');

 

saleno가 1부터 순서대로 입력되는 것을 알 수 있다.

 

중간에 번호 하나가 빠지더라도 일련번호가 빈칸을 채우지는 않습니다.

시퀀스는 테이블과 별개의 객체로 서로 관련이 없기 때문에,

테이블의 레코드가 삭제되었음을 알지 못합니다.

 

/* 2번 레코드를 삭제해본다. */
DELETE FROM tSale WHERE saleno = 2;

/* 새로운 레코드를 입력해본다. */
INSERT INTO tSale VALUES (seqSale.NEXTVAL, '박혁거세', '계란'); /* 오라클 */
INSERT INTO tSale VALUES (NEXT VALUE FOR seqSale, '박혁거세', '계란'); /* SQL Server */

 

2번을 채우지 않고, 다음 순서인 3번으로 넘어간다.

 

만일 중간에 비어 있는 일련번호로 레코드를 채워주고 싶다면 시퀀스 대신 원하는 번호를 입력해주면 됩니다.

 

INSERT INTO tSale VALUES (2, '고주몽', '고등어');

 

saleno 가 2인 레코드가 입력되었다.

 

3. 자동 생성 시퀀스 GENERATED AS IDENTITY (오라클 Only)

시퀀스는 보통 특정 테이블의 필드 하나를 위해 생성하며 공유하는 경우가 거의 없습니다.

그렇다면 따로 객체를 만드는 것보다 테이블을 만들 때 아예 시퀀스 필드로 지정하는 것이 효율적입니다.

단, 이 기능은 오라클만 지원합니다.

 

GENERATED [ALWAYS | BY DEFAULT | BY DEFALUT ON NULL] AS IDENTITY
[START WITH 시작값 INCREMENT BY 증가값 CACHE]

 

옵션을 다 생략하면 ALWAYS를 적용하며,

이 경우 ID열에 값을 직접 지정할 수 없고,

시작값과 증가값은 1로 생성합니다.

 

CREATE TABLE tSaleSeq
(
    saleno INT GENERATED AS IDENTITY PRIMARY KEY,
    customer VARCHAR(20),
    product VARCHAR(30)
);

 

GENERATED AS IDENTITY를 사용하면 별도의 시퀀스 객체를 만들 필요가 없어 편리합니다.

그러나 이 경우도 내부적으로 시퀀스를 자동 생성하며 따라서 시퀀스 생성 권한이 있어야 합니다.

 

삽입할 때는 saleno 필드를 생략하고 나머지 값만 입력합니다.

 

INSERT INTO tSaleSeq  (customer, product) VALUES ('단군', '지팡이');
INSERT INTO tSaleSeq  (customer, product) VALUES ('고주몽', '고등어');

 

자동으로 입력이 잘 됩니다.

 

ALWAYS 옵션으로 생성한 시퀀스 필드는 오라클이 알아서 일련번호를 생성하며,

saleno를 직접 지정하면 에러 처리됩니다.

만약 값을 직접 지정하려면 다음 둘 중 하나의 옵션으로 ID열을 정의해야 합니다.

 

/* 테이블 선언 시에 아래와 같이 정의해준다. */
saleno INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
saleno INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,

 

BY DEFAULT 옵션을 통해 ID를 직접 지정해줄 수 있습니다.

BY DEFAULT ON NULL 을 통해 ON NULL 을 지정해주면,

NULL 값이 들어오더라도 시퀀스 값으로 레코드를 생성합니다.

그러나 ON NULL이 없으면 NULL 값이 들어오면 에러가 발생합니다.

 

왼쪽은 에러, 우측은 ON NULL 옵션으로 2번으로 입력이 된다.

 

4. ID 필드 IDENTITY (SQL Server Only)

SQL Server는 일련번호를 붙이는 더 간편한 방법을 제공합니다.

자동 생성 시퀀스는 지원하지 않지만 비슷한 기능의 IDENTITY 속성이 있습니다.

 

CREATE TABLE tSaleId
(
	saleno INT IDENTITY PRIMARY KEY,
	customer VARCHAR(20),
	product VARCHAR(30)
);

 

아래와 같이 레코드를 삽입해보겠습니다.

 

INSERT INTO tSaleId (customer, product) VALUES ('단군', '지팡이');
INSERT INTO tSaleId (customer, product) VALUES ('고주몽', '고등어');

 

 

중간 레코드를 삭제하고 새 레코드를 입력해보겠습니다.

 

 

시퀀스와 마찬가지로 중간에 빠진 번호를 채우지 않고 증가합니다.

ID 필드는 서버가 관리하는 것이어서 사용자가 직접 번호를 지정할 수는 없습니다.

다음 쿼리를 입력해 보겠습니다.

 

 

에러가 발생합니다.

다만 에러 설명에 IDENTITY_INSERT가 OFF이기 때문에 명시적 값을 삽입할 수 없다고 되어 있습니다.

따라서 아래와 같은 방법으로 명시적 입력이 가능합니다.

다만 ID 직접 지정 기능은 꼭 필요할 때만 사용해야 하며,

다 사용한 후에는 즉시 옵션을 끄는 것이 좋습니다.

또한 ID를 강제로 변경하면 이후에는 지정한 ID 이후의 번호를 사용합니다.

예를 들어 100번 일련번호를 사용하고 직접 지정 옵션을 끈 후 새 레코드를 삽입하면 101번부터 할당됩니다.

 

SET IDENTITY_INSERT tSaleId ON;

INSERT INTO tSaleId (saleno, customer, product) VALUES (2, '고주몽', '고등어');

SET IDENTITY_INSERT tSaleId OFF;

 

9로 입력하면, 다음은 10이 자동으로 입력된다. 2를 입력했는데 3이 이미 존재하면 4가 입력된다.

 

ID의 현재값은 @@IDENTITY 시스템 함수로 조사합니다.

ID관리는 서버에게 맡기되 최후 삽입한 레코드의 번호를 알아야 한다면 삽입 직후 @@IDENTITY 함수를 호출합니다.

왕건이 가게에 와서 너구리를 구입했다가 마음이 바뀌어 짜파게티로 교환했다고 한다면,

아래와 같이 @@IDENTITY로 직전 레코드의 PK를 찾을 수 있습니다.

 

INSERT INTO tSaleId VALUES ('왕건', '너구리');

UPDATE tSaleId SET product = '짜파게티' WHERE saleno = @@IDENTITY;

 

 

5. AUTO_INCREMENT (MariaDB Only)

MariaDB는 시퀀스를 지원하지 않습니다.

대신 IDENTITY와 비슷한 AUTO_INCREMENT 구문을 지원합니다.

필드 선언문에 AUTO_INCREMENT 라고 선언하면 자동 증가하는 일련번호가 매겨집니다.

 

CREATE TABLE tSale
(
    saleno INT AUTO_INCREMENT PRIMARY KEY,
    customer NCHAR(20),
    product NCHAR(30)
);

 

마찬가지로 1부터 1씩 증가하는 일련번호를 부여합니다.

AUTO_INCREMENT는 초기값과 증가값을 지정할 수 없습니다.

삽입할 때는 saleno 는 생략하고 나머지 필드만 지정합니다.

 

INSERT INTO tSale (customer, product) VALUES ('단군', '지팡이');
INSERT INTO tSale (customer, product) VALUES ('고주몽', '고등어');

 

 

시퀀스나 IDENTITY와 마찬가지로 삭제한 일련번호는 재사용하지 않습니다.

 

DELETE FROM tSale WHERE saleno = 2;

INSERT INTO tSale (customer, product) VALUES ('박혁거세', '계란');

 

 

시퀀스나 IDENTITY와는 다르게 일련번호를 명시적으로 입력해주는 것은 항상 가능합니다.

다만 PK 제약상 중복되지 않도록 하여야 합니다.

 

INSERT INTO tSale (saleno, customer, product) VALUES (2, '고주몽', '고등어');

 

 

일련번호를 특정값으로 리셋할 때는 ALTER 명령을 사용합니다.

일련번호를 리셋하면 이후 삽입하는 레코드는 리셋한 번호부터 순서대로 증가하는 값을 받습니다.

 

ALTER TABLE tSale AUTO_INCREMENT = 100;

 

 

최후값을 알아낼 때는 LAST_INSERT_ID() 함수를 사용합니다.

김유신의 구매 기록을 찾아 말을 짜파게티로 변경해보겠습니다.

 

UPDATE tSale SET product = '짜파게티' WHERE saleno = LAST_INSERT_ID();

 

 

짜파게티로 바뀌었습니다.

 

그만 알아보겠습니다.

 

 

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24