S-JIS[2023-10-09/2024-08-31]

Tsurugi create table

TsurugiのSQLのcreate tableのメモ。

概要

TsurugiのSQLでテーブルを作成するのがcreate table文。

create table [if not exists] テーブル名 (
  カラム定義1,
  カラム定義2,
  …
  カラム定義N
  [, primary key (カラム名, …)]
)

※create table 〜 as selectは未実装。

→DDLを実行する際のトランザクション種別

テーブルのカラム定義では、カラム名の後ろにデータ型を指定する。

  カラム名 データ型 [カラム制約…]

Tsurugiサーバーの内部では、テーブルのデータはKVS（Key Value Store）で格納されている。（実際はMasstreeという構造らしい）
つまりキー（プライマリキー）が必須である。
create tableでプライマリキーを指定しない場合、暗黙にキー（シーケンス）が生成される。

他のRDBMSではプライマリキー項目に対して（暗黙に）インデックスを作成するかもしれないが、
Tsurugi内部はKVSでありキーの昇順でデータが格納されるので、プライマリキーに対してインデックスという特別な何かは作成されない。

TsurugiはインメモリーDB（データはメモリー上に置く）なので、他のRDBMSのtablespace（データの格納先ディレクトリーやファイルサイズの上限等）のような指定は無い。

ただ、永続化の為にデータをファイル（ディスク）に保存することは行う。
その場所は構成定義ファイルのdatastoreセクションのlog_locationで指定する。

create tableの例

create table test (
  foo int primary key,
  bar bigint,
  zzz varchar(10)
);

create table test (
  foo int,
  bar bigint,
  zzz varchar(10),
  primary key (foo)
);

→指定できるデータ型

create table if not existsの例

テーブルが存在しない場合のみテーブルを作成する。[2023-12-07]
Tsurugi 1.0.0-BETA2以降。

create table if not exists test (
  foo int primary key,
  bar bigint,
  zzz varchar(10)
);

テーブルが既に存在している場合は何もしない。（エラーにならない）

generated〜as identityの例

generated〜as identityを指定したカラムは、insert時に自動的に値を採番する。[2024-08-31]
Tsurugi 1.0.0以降。

  カラム名 データ型 generated {always | by default} [(シーケンスオプション…)]

alwaysを指定すると、insert時に必ず値を採番する。このカラムに明示的に値を入れることは出来ないし、updateも出来ない。
by defaultを指定すると、insert時に（採番される以外の）値を明示して入れることが出来る。updateすることも出来る。

シーケンスオプションを全て省略すると、開始値1・増分+1になる。

create table test (
  id bigint primary key generated always as identity
);
insert into test default values; → 1
insert into test default values; → 2
insert into test default values; → 3

（増分が+1で）開始値を0にしたい場合は、minvalueを0にする。
（incrementが正の場合、startのデフォルト値はminvalueなので、startは省略できる）

create table test (
  id bigint primary key generated always as identity (minvalue 0)
);
insert into test default values; → 0
insert into test default values; → 1
insert into test default values; → 2

minvalueを指定せずにstartを0にするとエラーになる。
採番される値はminvalueとmaxvalueの間に収まる必要があり、incrementが+1の場合のminvalueのデフォルトは1だから、範囲外になる為。

tgsql> create table test (
     |   id bigint primary key generated always as identity (start with 0)
     | );
COMPILE_EXCEPTION (SQL-03000: compile failed with error:invalid_sequence_value message:"initial value is out of range: 0 (min=1, max=9223372036854775807)" location:<input>:2:25+43)

by defaultで自動生成するカラムを定義した場合、そのカラムに明示的に値をinsertすることが出来る。
そして 自動採番するカラムがプライマリキーの場合、採番された値のレコードが既に存在していたら一意制約違反になる。
しかしエラーになっても採番される値が戻ることは無く、進んでいく。

create table test (
  id   bigint      primary key generated by default as identity (start with 1 increment by +1),
  memo varchar(10) default 'default'
);

insert into test default values;        → 1
insert into test values(2, 'explicit'); → 2
insert into test default values;        → UNIQUE_CONSTRAINT_VIOLATION_EXCEPTION
insert into test default values;        → 3

tgsql> select * from test;
[id: INT8, memo: CHARACTER]
[1, default]
[2, explicit]
[3, default]
(3 rows)