STRICT 테이블

STRICT 테이블 (STRICT Tables)

SQLite는 저장하는 콘텐츠의 데이터타입에 대해 유연한 편이지만, 3.37.0부터 테이블별로 엄격한 타입 강제 모드를 지원해요. STRICT 테이블 옵션을 쓰면 모든 컬럼이 데이터타입을 명시해야 하고, 지정된 타입으로 손실 없이 변환할 수 없는 값은 SQLITE_CONSTRAINT_DATATYPE 오류를 내요. 이 문서는 STRICT 테이블의 규칙, ANY 데이터타입, 그리고 하위 호환성에 대해 설명해요.

출처: 문서

본문

1. 도입

SQLite는 저장하는 콘텐츠의 데이터타입에 대해 유연하려고 노력해요. 예를 들어, 테이블 컬럼 타입이 "INTEGER"라면, SQLite는 그 컬럼에 삽입되는 모든 것을 정수로 변환하려 해요. 그래서 문자열 '123'을 삽입하려 하면 정수 123이 삽입돼요. 하지만 콘텐츠를 손실 없이 정수로 변환할 수 없다면, 예를 들어 입력이 'xyz'라면, 원래 문자열이 그대로 삽입돼요. 자세한 내용은 Datatypes In SQLite 문서를 참고하세요.

어떤 개발자들은 SQLite의 유연한 타입 규칙이 주는 자유를 높이 평가하고 그 자유를 유리하게 활용해요. 하지만 다른 개발자들은 SQLite의 규칙 위반에 경악하며, 다른 모든 SQL 데이터베이스 엔진과 SQL 표준에서 볼 수 있는 전통적인 엄격한 타입 시스템을 선호해요. 후자 그룹을 위해, SQLite는 3.37.0 (2021-11-27) 버전부터 테이블별로 개별적으로 활성화되는 엄격한 타입 모드를 지원해요.

2. STRICT 테이블

CREATE TABLE 문에서 마지막 닫는 ")" 뒤에 "STRICT" 테이블 옵션 키워드를 추가하면, 엄격한 타입 규칙이 그 테이블에 적용돼요. STRICT 키워드는 다음과 같은 차이를 발생시켜요:

  • 모든 컬럼 정의는 그 컬럼에 대한 데이터타입을 명시해야 해요. 데이터타입 없이 컬럼을 지정하는 자유가 제거돼요.

  • 데이터타입은 다음 중 하나여야 해요:

    • INT
    • INTEGER
    • REAL
    • TEXT
    • BLOB
    • ANY

    다른 데이터타입 이름은 허용되지 않아요. 다만 향후 SQLite 릴리스에서 새 타입이 추가될 수는 있어요.

  • ANY가 아닌 데이터타입을 가진 컬럼에 삽입되는 콘텐츠는 NULL이거나(컬럼에 NOT NULL 제약이 없다고 가정) 지정된 타입이어야 해요. SQLite는 PostgreSQL, MySQL, SQL Server, Oracle이 모두 하듯이 일반적인 affinity 규칙을 사용해 데이터를 적절한 타입으로 강제 변환하려 해요. 값을 지정된 데이터타입으로 손실 없이 변환할 수 없다면 SQLITE_CONSTRAINT_DATATYPE 오류가 발생해요.

  • 데이터타입이 ANY인 컬럼은 모든 종류의 데이터를 받을 수 있어요(물론 NOT NULL 제약이 있으면 NULL 값은 거부해요). STRICT 테이블에서 ANY 타입 컬럼에는 타입 강제 변환이 일어나지 않아요.

  • PRIMARY KEY의 일부인 컬럼은 암시적으로 NOT NULL이에요. 하지만 PRIMARY KEY가 암시적 NOT NULL 제약을 가짐에도, INTEGER PRIMARY KEY 컬럼에 NULL 값이 삽입되면, 일반적인 비엄격 테이블의 INTEGER PRIMARY KEY와 동일한 규칙을 사용해 NULL이 자동으로 고유 정수로 변환돼요.

  • PRAGMA integrity_check와 PRAGMA quick_check 명령은 STRICT 테이블의 모든 컬럼 콘텐츠 타입을 검사하고, 문제가 있으면 오류를 보여줘요.

STRICT 테이블의 다른 모든 것은 일반적인 비엄격 테이블과 동일하게 작동해요:

  • CHECK 제약은 동일하게 작동해요.
  • NOT NULL 제약은 동일하게 작동해요.
  • FOREIGN KEY 제약은 동일하게 작동해요.
  • UNIQUE 제약은 동일하게 작동해요.
  • DEFAULT 절은 동일하게 작동해요.
  • COLLATE 절은 동일하게 작동해요.
  • 생성된 컬럼(Generated columns)은 동일하게 작동해요.
  • ON CONFLICT 절은 동일하게 작동해요.
  • 인덱스는 동일하게 작동해요.
  • AUTOINCREMENT는 동일하게 작동해요.
  • INTEGER PRIMARY KEY 컬럼은 rowid의 별칭이지만, INT PRIMARY KEY 컬럼은 그렇지 않아요.
  • 테이블 데이터의 디스크 형식(on-disk format)은 동일해요.

3. ANY 데이터타입

단일 컬럼에 어떤 타입의 데이터든 담을 수 있는 능력은 수년간 놀랍도록 유용함이 입증됐어요. 이 능력을 STRICT 테이블에서도 계속 지원하기 위해 새 ANY 데이터타입 이름이 도입됐어요. 컬럼 데이터타입이 "ANY"일 때는 정수, 부동소수점 값, 문자열, 바이너리 BLOB 등 어떤 종류의 데이터든 그 테이블에 삽입될 수 있고, 값과 데이터타입이 삽입된 그대로 정확히 보존돼요. 우리가 아는 한, SQLite는 이 고급 기능을 지원하는 유일한 SQL 데이터베이스 엔진이에요.

ANY의 동작은 STRICT 테이블과 일반적인 비엄격 테이블에서 약간 달라요. STRICT 테이블에서 ANY 타입 컬럼은 받은 그대로 데이터를 항상 정확히 보존해요. 일반적인 비엄격 테이블에서 ANY 타입 컬럼은 숫자처럼 보이는 문자열을 숫자 값으로 변환하려 시도하고, 성공하면 원래 문자열 대신 숫자 값을 저장해요. 예를 들어:

CREATE TABLE t1(a ANY) STRICT;
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: text '000123'

CREATE TABLE t1(a ANY);
INSERT INTO t1 VALUES('000123');
SELECT typeof(a), quote(a) FROM t1;
-- result: integer 123

4. 하위 호환성

CREATE TABLE 문 끝의 STRICT 키워드는 SQLite 3.37.0 (2021-11-27) 이후 버전에서만 인식돼요. STRICT 키워드가 들어 있는 데이터베이스를 이전 버전의 SQLite로 열려 하면, 그 버전은 키워드를 인식하지 못하고 (아래에 언급된 경우를 제외하고) 오류를 보고해요. 하지만 추가된 STRICT 키워드 외에는 데이터베이스의 근본적인 파일 형식이 동일해요.

따라서 일반적으로 하나 이상의 STRICT 테이블을 포함하는 데이터베이스 파일은 SQLite 3.37.0 이상에서만 읽고 쓸 수 있어요. 하지만 SQLite 3.37.0 이상으로 만든 데이터베이스는, 그 데이터베이스에 STRICT 테이블이나 그 이후에 도입된 다른 기능이 없는 한, 3.0.0 (2004-06-18)까지 거슬러 올라가는 이전 버전의 SQLite로도 계속 읽고 쓸 수 있어요.

STRICT 키워드는 여전히 식별자(identifier)로 사용될 수 있어요. (구문의 특정 부분에서만 키워드로 취급되고, sqlite3_keyword_check(..)는 이를 일반 키워드로 인식하지 않아요.)

4.1. 이전 버전의 SQLite에서 STRICT 테이블 접근

SQL 언어 파서의 특이함 때문에, 3.37.0 이전의 SQLite 버전은 데이터베이스 파일을 연 직후, 스키마를 알아야 하는 다른 작업을 하기 전에 "PRAGMA writable_schema=ON"을 설정하면 여전히 STRICT 테이블을 읽고 쓸 수 있어요. PRAGMA writable_schema=ON의 특징 중 하나는 스키마 파서의 오류를 비활성화한다는 것이에요. 이는 의도적인데, writable_schema=ON을 두는 큰 이유가 손상된 스키마를 가진 데이터베이스 파일의 복구를 돕기 위해서이기 때문이에요. 그래서 writable_schema=ON 상태에서 스키마 파서가 STRICT 키워드에 도달하면 "이걸 어떻게 처리해야 할지 모르지만, 여기까지는 유효한 테이블 정의처럼 보이니 있는 그대로 쓰겠다"고 스스로 말해요. 따라서 STRICT 키워드는 사실상 무시돼요. STRICT 테이블에 대해 파일 형식의 다른 부분은 달라지지 않으므로, 다른 모든 것은 정상적으로 작동해요. 물론 이전 SQLite 버전은 그 방법을 모르기 때문에 엄격한 타입 강제는 발생하지 않아요.

CLI의 .dump 명령은 손상된 데이터베이스 파일에서도 최대한 많은 콘텐츠를 추출하도록 설계됐기 때문에 PRAGMA writable_schema=ON을 설정해요. 따라서 이전 버전의 SQLite를 사용 중이고, CLI에서 STRICT 테이블이 있는 데이터베이스를 열고, 다른 작업을 하기 전에 ".dump" 명령을 실행한다면, 엄격한 타입 강제 없이 STRICT 테이블을 읽고 쓸 수 있어요. 이는 잘못된 타입이 STRICT 테이블에 들어가도록 허용함으로써 데이터베이스를 손상시킬 수도 있어요. 더 새 버전의 SQLite로 데이터베이스를 다시 열고 "PRAGMA quick_check"를 실행하면 그러한 모든 손상을 감지하고 보고해요.

5. 다른 테이블 옵션

SQLite 파서는 CREATE TABLE 문의 마지막 닫는 괄호 뒤에 쉼표로 구분된 테이블 옵션 목록을 받아들여요. 이 글을 작성하는 시점(2021-08-23)에 인식되는 옵션은 두 가지뿐이에요:

  • STRICT
  • WITHOUT ROWID

여러 옵션이 있으면 어떤 순서로도 지정할 수 있어요. 단순함을 위해 현재 파서는 중복 옵션을 불평 없이 받아들이지만, 이는 향후 릴리스에서 바뀔 수 있으므로 애플리케이션은 그에 의존하면 안 돼요.

더 알아보기 (Learn more)