생성된 컬럼

생성된 컬럼 (Generated Columns)

생성된 컬럼(가끔 "계산된 컬럼(computed columns)"이라고도 불러요)은 값이 같은 행의 다른 컬럼들의 함수인 테이블의 컬럼이에요. 생성된 컬럼은 읽을 수 있지만, 그 값을 직접 쓸 수는 없어요. 생성된 컬럼의 값을 바꾸는 유일한 방법은 그 생성된 컬럼을 계산하는 데 쓰이는 다른 컬럼들의 값을 수정하는 거예요.

출처: Generated Columns

본문

1. 소개 (Introduction)

2. 문법 (Syntax)

문법적으로 생성된 컬럼은 "GENERATED ALWAYS" 컬럼 제약로 지정해요. 예를 들어:

CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
   e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
);

위 문에는 세 개의 일반 컬럼 "a"(PRIMARY KEY), "b", "c"와 두 개의 생성된 컬럼 "d", "e"가 있어요.

제약의 시작에 있는 "GENERATED ALWAYS" 키워드와 끝에 있는 "VIRTUAL" 또는 "STORED" 키워드는 모두 선택사항이에요. 필수인 것은 "AS" 키워드와 괄호로 묶인 표현식뿐이에요. 끝의 "VIRTUAL" 또는 "STORED" 키워드를 생략하면 기본값은 VIRTUAL이에요. 따라서 위 예문은 다음과 같이 단순화할 수 있어요:

CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT AS (a*abs(b)),
   e TEXT AS (substr(c,b,b+1)) STORED
);

2.1. VIRTUAL 컬럼과 STORED 컬럼

생성된 컬럼은 VIRTUAL 또는 STORED가 될 수 있어요. VIRTUAL 컬럼의 값은 읽을 때 계산되는 반면, STORED 컬럼의 값은 행이 기록될 때 계산돼요. STORED 컬럼은 데이터베이스 파일에서 공간을 차지하고, VIRTUAL 컬럼은 읽을 때 더 많은 CPU 사이클을 사용해요.

SQL 관점에서 STORED 컬럼과 VIRTUAL 컬럼은 거의 동일해요. 두 종류의 생성된 컬럼에 대한 쿼리는 같은 결과를 만들어 내요. 유일한 기능적 차이는 ALTER TABLE ADD COLUMN 명령을 사용해 새로운 STORED 컬럼을 추가할 수 없다는 점이에요. ALTER TABLE을 사용해서는 VIRTUAL 컬럼만 추가할 수 있어요.

2.2. 기능 (Capabilities)

생성된 컬럼은 데이터타입을 가질 수 있어요. SQLite는 일반 컬럼과 같은 affinity 규칙을 사용해 생성 표현식의 결과를 그 데이터타입으로 변환하려고 해요.
생성된 컬럼은 일반 컬럼과 마찬가지로 NOT NULL, CHECK, UNIQUE 제약과 외래 키 제약을 가질 수 있어요.
생성된 컬럼은 일반 컬럼과 마찬가지로 인덱스에 참여할 수 있어요. STORED 생성된 컬럼을 사용하는 인덱스는 그냥 일반 인덱스이지만, 하나 이상의 VIRTUAL 생성된 컬럼을 사용하는 인덱스는 표현식 인덱스 (expression index)가 돼요.
생성된 컬럼의 표현식은 표현식이 직접 또는 간접적으로 자기 자신을 다시 참조하지 않는 한, 다른 생성된 컬럼을 포함해 테이블의 다른 선언된 컬럼을 참조할 수 있어요.
생성된 컬럼은 테이블 정의의 어디든 올 수 있어요. 생성된 컬럼은 일반 컬럼들 사이에 섞여 있을 수 있어요. 위 예제에서처럼 테이블 정의의 컬럼 목록 끝에 생성된 컬럼을 둘 필요는 없어요.

2.3. 제한 사항 (Limitations)

생성된 컬럼은 기본값 (default value)을 가질 수 없어요 ("DEFAULT" 절을 사용할 수 없어요). 생성된 컬럼의 값은 항상 "AS" 키워드 뒤의 표현식이 지정하는 값이에요.
생성된 컬럼은 PRIMARY KEY의 일부로 사용될 수 없어요. (SQLite의 향후 버전에서는 STORED 컬럼에 대해 이 제약을 완화할 수도 있어요.)
생성된 컬럼의 표현식은 상수 리터럴과 같은 행 안의 컬럼만 참조할 수 있고, 스칼라 결정적 함수 (deterministic functions)만 사용할 수 있어요. 표현식은 서브쿼리, 집계 함수, 윈도우 함수, 테이블 값 함수를 사용할 수 없어요.
생성된 컬럼의 표현식은 같은 행의 다른 생성된 컬럼을 참조할 수 있지만, 어떤 생성된 컬럼도 직접 또는 간접적으로 자기 자신에 의존할 수 없어요.
생성된 컬럼의 표현식은 ROWID를 직접 참조할 수 없어요. 다만 INTEGER PRIMARY KEY 컬럼은 참조할 수 있는데, 둘은 종종 같은 것이에요.
모든 테이블은 적어도 하나의 비-생성 컬럼을 가져야 해요.
ALTER TABLE ADD COLUMN으로 STORED 컬럼을 추가할 수 없어요. VIRTUAL 컬럼은 추가할 수 있어요.
생성된 컬럼의 데이터타입과 정렬 순서 (collating sequence)는 컬럼 정의의 데이터타입과 COLLATE 절에 의해서만 결정돼요. GENERATED ALWAYS AS 표현식의 데이터타입과 정렬 순서는 컬럼 자체의 데이터타입과 정렬 순서에 영향을 미치지 않아요.
생성된 컬럼은 PRAGMA table_info 문이 제공하는 컬럼 목록에 포함되지 않아요. 하지만 더 새로운 PRAGMA table_xinfo 문의 출력에는 포함돼요.

3. 호환성 (Compatibility)

생성된 컬럼 지원은 SQLite 버전 3.31.0 (2020-01-22)에서 추가되었어요. 더 이전 버전의 SQLite가 스키마에 생성된 컬럼이 포함된 데이터베이스 파일을 읽으려고 하면, 그 이전 버전은 생성된 컬럼 문법을 오류로 인식하고 데이터베이스 스키마가 손상되었음을 보고할 거예요.

정리하면: SQLite 버전 3.31.0은 SQLite 3.0.0 (2004-06-18)까지 거슬러 올라가는 이전 버전이 만든 모든 데이터베이스를 읽고 쓸 수 있어요. 그리고 3.31.0 이전의 SQLite 버전은 데이터베이스 스키마에 생성된 컬럼과 같은 이전 버전이 이해하지 못하는 기능이 포함되어 있지 않은 한, 3.31.0 이상의 버전이 만든 데이터베이스를 읽고 쓸 수 있어요. 문제는 생성된 컬럼을 포함하는 새 데이터베이스를 SQLite 3.31.0 이상으로 만든 다음, 생성된 컬럼을 이해하지 못하는 이전 버전의 SQLite로 그 데이터베이스 파일을 읽거나 쓰려고 할 때만 발생해요.

생성된 컬럼