テーブルを作成します。
Synopsis
CREATE TABLE table (table-element-commalist)
table-element ::=
[%DESCRIPTION string] |
[%FILE string] |
[%NUMROWS integer] |
[%ROUTINE string]
{ fieldname datatype
{
UNIQUE | NULL | NOT NULL |
DEFAULT [(]default-spec[)] |
[COLLATE] sqlcollation |
%DESCRIPTION literal
} , }
[CONSTRAINT identifier]
UNIQUE (identifier-commalist) |
[CONSTRAINT identifier]
PRIMARY KEY (identifier-commalist) |
[CONSTRAINT identifier]
FOREIGN KEY (identifier-commalist) REFERENCES table
(identifier-commalist) [triggered action]
sqlcollation::=
%ALPHAUP |
%EXACT |
%MINUS |
%PLUS |
%SPACE |
%SQLSTRING [(maxlen)] |
%SQLUPPER [(maxlen)] |
%STRING [(maxlen)] |
%UPPER
このコマンド概要には、互換性目的でのみ解析されオペレーションを伴わないキーワードは記載されていません。サポートされる空命令キーワードの一覧は、後述の別セクションで示します。
埋め込み SQL 内では
CREATE TABLE 文を使用する前に、
%msql ローカル変数を %CREATE_TABLE 特権を持つユーザに設定する必要があります。この設定を行わないと、SQLCODE -99 (特権違反) になります。%CREATE_TABLE 特権は、
GRANT コマンドを使用して割り当てます。ただし、
Caché 構成マネージャの
[SQL セキュリティ有効] オプションが “いいえ” に設定されている場合は、ユーザを指定しなくてもテーブルを作成できます。この場合、SQL マネージャはユーザとして “_SYSTEM” を、埋め込み SQL はユーザとして "" (空文字列) を割り当てます。SQL セキュリティの構成の詳細は、"
Caché セキュリティ" の技術情報を参照してください。
以下の例は、Employee テーブルを作成します。
SET %msql="_SYSTEM"
&sql(CREATE TABLE Employee (
EMPNUM INT NOT NULL,
NAMELAST CHAR(30) NOT NULL,
NAMEFIRST CHAR(30) NOT NULL,
STARTDATE TIMESTAMP,
SALARY MONEY,
ACCRUEDVACATION INT,
ACCRUEDSICKLEAVE INT,
CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM))
)
WRITE !,"SQL code is: ",SQLCODE
Employee という名前のこのテーブルには、いくつかの定義済みフィールドがあります。(会社の従業員の ID 番号を含む) EMPNUM フィールドは、NULL ではない整数です。 また、テーブルの主キーとして宣言されています。従業員の姓と名にはそれぞれフィールドが定義され、どちらも最大 30 文字までの非 NULL 文字列で指定されます。また、従業員の入社日や有給休暇、病欠日のフィールド (TIMESTAMP と INT
データ型 を使用) もあります。
テーブル名は、
識別子 の規則に従い、以下のような制約を受けます。既定では、テープル名は (フィールド名と同様に) 単純な識別子です。同じスキーマ内では、テーブルとビューに同じ名前を使用することはできません。テーブル名は 128 文字を超えることはできません。
テーブル名は修飾、未修飾のどちらでもかまいません。テーブル名を修飾する場合は、以下の構文を使用します。
未修飾のテーブル名には、既定として SQLUser スキーマ (または事前構成されたシステム既定のスキーマ名) が使用されます。例えば、上記の例では、未修飾のテーブル名 Employee からクラス User.Employee が生成されます。スキーマ名はクラス・パッケージ名となり、既定のクラス・パッケージ名は User です。スキーマ名と既定値の詳細は、"
テーブル参照" を参照してください。
Caché はテーブル名を使用して、対応するクラス名を生成します。クラス名には英数字 (文字および数字) のみが使用され、その長さは最大 25 文字です。このクラス名を生成するには、Caché は最初にテーブル名から句読点を削除し、次に 25 文字 (未満) の一意の識別子を生成します。その際、クラス名の一意性を保証するために、必要に応じて最後の文字を数字に置き換えます。Caché は有効なテーブル名から一意のクラス名を生成しますが、テーブルの命名時には、この名前の生成に伴う以下の制約について考慮する必要があります。
-
テーブル名には、最低でも 1 文字を含める必要があります。テーブル名の先頭の文字または最初の句読点に続く文字は、数字以外の文字にする必要があります。
-
生成されたクラス名には句読点が含まれないため、句読点のみが異なるテーブル名の作成は可能ですが、お勧めできません。
-
テーブル名は 25 文字よりも大幅に長くすることができますが、最初の 25 英数文字が異なるようにテーブル名を作成すると処理がはるかに容易になります。
Caché は Unicode システムで、16 ビット (ワイド) 文字列のテーブルやフィールドの名前付けをサポートします。
$ZNAME テストに合格した文字は、有効な文字です。以下の例に示すように、テーブル名にはアクセント記号付き文字を使用でき、生成されるクラス名にもアクセント記号が含まれます。
SET %msql="_SYSTEM"
&sql(CREATE TABLE ñáàéèíìóòúù (
TESTNUM INT NOT NULL,
FIRSTWORD CHAR (30) NOT NULL,
LASTWORD CHAR (30) NOT NULL,
CONSTRAINT ñáàéèíìóòúùPK PRIMARY KEY (TESTNUM))
)
WRITE !,"SQL code is: ",SQLCODE
既存のテープルと同じ名前のテーブルを作成しようとしたときの反応は、
Caché 構成マネージャの設定により異なります。[詳細] タブの [SQL] オプションを展開し、
[既存テーブルに DDL CREATE TABLE を許可する] の現在の値を表示します。既定は “いいえ” です。既定では、Caché は既存のテーブルと同じ名前のテーブルを作成することを拒否し、SQLCODE -201 エラーを返します。この設定を推奨します。
%DESCRIPTION、%FILE、%NUMROWS、%ROUTINE
これらのオプション・キーワード句は、テーブル要素のコンマ区切りリスト内の任意の場所で指定できます。
Caché SQL が提供する %DESCRIPTION キーワードは、テーブルまたはフィールドを文書化する説明を挿入する目的で使用されます。%DESCRIPTION の後には、一重引用符で囲まれたテキスト文字列が続きます。このテキストの長さに制限はなく、空白スペースを含むすべての文字を使用できます。(説明文字列内の一重引用符は、
'Joe"s Table' のように、二重引用符で代用します。)1 つのテーブルには 1 つの %DESCRIPTION を使用できます。テーブルの各フィールドには、データ型の後にそれぞれ独自の %DESCRIPTION を指定できます。1 つのテーブルに対してテーブル全体の %DESCRIPTION を複数指定すると、SQLCODE –82 が返されます。1 つのフィールドに対して %DESCRIPTION を複数指定すると、最後に指定された %DESCRIPTION のみが保持されます。
Caché スタジオでは、説明は、対応するテーブル (クラス) またはフィールド (プロパティ) の 1 つ前の行に、3 つのスラッシュに伴われて表示されます。以下はその例です。
Caché SQL が提供する %FILE キーワードは、テーブルを文書化するファイル名を指定する目的で使用されます。1 つのテーブルに使用できるのは 1 つの %FILE のみです。複数の %FILE を指定すると、SQLCODE –83 が返されます。
Caché SQL が提供する %NUMROWS キーワードは、このテーブルの予想される行数を示す整数を格納するのに使用されます。既知のデータ行数でテーブルが作成される場合、特に行数の初期値が以降に変更される可能性が低い場合 (都道府県のテーブルなど)、%NUMROWS を設定するとスペースを節約して、パフォーマンスを向上させることができます。1 つのテーブルに使用できるのは 1 つの %NUMROWS キーワードのみです。複数の %NUMROWS を指定すると、SQLCODE –84 が返されます。
Caché SQL が提供する %ROUTINE キーワードは、このベース・テーブルに対して生成されるルーチン名の接頭語を指定するのに使用できます。例えば、%ROUTINE “myname” では、myname1、myname2 というように以降も順次続く一連の名前のルーチンにコードが生成されます。外部関数は、%ROUTINE から呼び出せません。1 つのテーブルに使用できるのは 1 つの %ROUTINE のみです。複数の %ROUTINE を指定すると、SQLCODE –85 が返されます。
Caché スタジオでは、ルーチン名の接頭語は
SqlRoutinePrefix 値として表示されます。
Caché SQL は、構文解析の目的にのみ以下の
CREATE TABLE オプションをサポートし、既存の SQL コードから Caché SQL への変換を支援します。これらのオプションは、実際には機能しません。
{ON | IN} dbspace-name
LOCK MODE [ROW | PAGE]
[CLUSTERED | NONCLUSTERED]
WITH FILLFACTOR = literal
MATCH [FULL | PARTIAL]
CHARACTER SET identifier
COLLATE identifier /* But note use of COLLATE keyword, described below */
テーブル名の後に、括弧に囲まれたテーブルの全フィールド (列) 定義があります。フィールドの定義は、コンマで区切られています。規則によって、各フィールドの定義は通常、別の行にインデントされて表示されます。 これは必須ではありませんがお勧めします。最後のフィールドを定義した後は、フィールド定義の閉じ括弧を必ず記述してください。
フィールド定義部分は空白で分けられています。フィールド名は最初に記述され、その後にそのデータ特性が続きます。フィールドのデータ特性は、データ型、データ・サイズ (オプション)、データ制約 (オプション) の順序で表示されます。
Note:
Caché では、400 列を超えるテーブルの作成はお勧めできません。400 列を超える場合は、データベースを設計し直します。これには、列を行に置き換える方法、列を複数の関連テーブルに分割する方法、データを文字ストリームまたはビット・ストリームとして保存し、列を減らす方法などがあります。
フィールド名は、
識別子 規約に従い、テーブル名と同様の制約を受けます。フィールド名は 128 文字を超えることはできません。既定のフィールド名は、簡単な識別子です。
Caché はフィールド名を使用して、対応するプロパティ名を生成します。プロパティ名には英数字 (文字および数字) のみが使用され、その長さは最大 31 文字です。このプロパティ名を生成するには、Caché は最初にフィールド名から句読点を削除し、次に 31 文字 (未満) の一意の識別子を生成します。その際、プロパティ名の一意性を保証するために、必要に応じて最後の文字を数字に置き換えます。
Caché SQL は、多くの標準 SQL
データ型 をサポートしています。サポートされているデータ型の完全なリストは、
データ型 セクションを参照してください。
Caché は DDL Datatype Mapping Table を使用して、これらの標準 SQL データ型を Caché データ型にマップします。このテーブルには標準マッピングが含まれており、追加のユーザ定義のデータ型を含めるために、ユーザによって (SetDDLDatatype を使用して) 変更されます。現在のデータ型マッピングを参照するには、
Caché 構成マネージャにアクセスします。[詳細] タブの [SQL オプション] を展開し、
[システム DDL データ型マッピング] および
[ユーザ DDL データ型マッピング] の現在の値を表示します。
SQL で、存在する Caché のデータ型に対応しないデータ型を指定する場合、SQL データ型の名前はそれに対応するクラス・プロパティのデータ型として使用されます。DDL ランタイム (SQL Execute) 前に、このユーザ定義の Caché データ型を作成する必要があります。
また、ひとつのパラメータ値に対して、データ型のマッピングを上書きできます。例えば、VARCHAR(100) を、与えられた %String(MAXLEN=100) 標準マッピングにマップしたくないとします。この場合、テーブルに 'VARCHAR(100)' の DDL データ型を追加して、これを上書きし、対応する Caché のタイプを指定します。以下はその例です。
VARCHAR(100) maps to MyString100(MAXLEN=100)
データ型の後に続けて、(precision,scale) のように括弧内で使用可能なデータ・サイズを表示できます。precision は、許可された最大文字数や桁数を表示します。scale は、小数桁数を指定します。例えば以下のようになります。
ProductName VARCHAR (64)
UnitPrice NUMERIC (6,2)
この例では、UnitPrice の最大許可サイズは 6 桁で、そのうち 2 桁は小数点の右側です。したがって、使用できる最高金額は $9999.99 になります。
データ制約によって、フィールドに許可される値、フィールドの既定値、およびデータ値に使用される照合タイプが規定されます。これらのデータ制約は、すべてオプションです。データ制約は、どのような順序でも指定できます。詳細は、"
フィールド制約" を参照してください。
NOT NULL データ制約キーワードは、そのフィールドが NULL 値を受け入れないように指定します。 つまり、すべてのレコードがこのフィールドに対して指定された値を持つ必要があります。NULL と空文字列 (") は、Caché では異なる値です。フィールドが NOT NULL 制約で定義されているとしても、文字列を受け入れるフィールドに空文字列を挿入することができます。数値フィールドに空文字列を挿入することはできません。詳細は、このドキュメントの SQL の概念 の "
NULL" のページを参照してください。
NULL データ制約キーワードは、このフィールドが NULL 値を受け入れることを明示的に指定します。これは、フィールドの既定の定義です。
UNIQUE データ制約は、このフィールドが一意の値のみ受け入れることを指定します。したがって、このフィールドには同じ値を持つレコードは存在しないことになります。
空文字列 (") はデータ値とみなされるため、UNIQUE データ制約が適用されている場合は、このフィールドの複数のレコードを空文字列値にすることはできません。
NULL はデータ値とみなされないため、UNIQUE データ制約は複数の NULL に対して適用されません。フィールドに対して NULL を制限するには、NOT NULL キーワード制約を使用します。
DEAFAULT データ制約は、レコードにユーザ提供のデータ値によって上書きされていなければ、このフィールドに対して自動的に与えられている既定データ値を指定します。既定値として与えられている文字列は定数で、一重引用符で囲みます。数値既定値には一重引用符は必要ありません。例えば以下のようになります。
Member_status CHAR(13) DEFAULT 'M',
Membership_term INT DEFAULT 2
DEAFAULT データ制約は、その値を定義するキーワード・オプションを受け入れることができます。サポートされているオプションは、NULL、USER、CURRENT_USER、SESSION_USER、SYSTEM_USER、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP です。
また、DEFAULT データ制約は OBJECTSCRIPT
literal を受け入れることもできます。このオプションでは、以下の例に示すように、Caché ObjectScript コードを含む引用符付き文字列を指定することで既定値を生成できます。
CREATE TABLE mytest
(ID NUMBER(12,0) NOT NULL,
CREATE_DATE DATE DEFAULT OBJECTSCRIPT '+$HOROLOG' NOT NULL,
LOGNUM NUMBER(12,0) DEFAULT OBJECTSCRIPT '$INCREMENT(^LogNumber)')
オプションの照合パラメータは、フィールドの値のソート時に使用される照合タイプを指定します。Caché SQL は、9 タイプの照合をサポートします。プログラミングを明確にする目的で、照合パラメータの前にオプション・キーワードの COLLATE を指定することを推奨します。ただし、このキーワードは必須ではありません。照合を指定しない場合、既定は %EXACT 照合です。
%ALPHAUP、%SQLUPPER、%STRING、および %UPPER 照合は、照合目的ですべての文字を大文字に変換します。大文字と小文字を区別しない照合の詳細は、"
%SQLUPPER" 関数を参照してください。
%SQLSTRING、%SQLUPPER、および %STRING 照合は、オプションの
maxlen パラメータを提供します。これは切り捨てを命令する整数で、照合の実行時に対象とする最大文字数を指定します。このパラメータは、サイズの大きなデータ値を持つフィールドにインデックスを作成するときに便利です。
フィールドには、説明用のテキストを追加できます。このオプションに適用される規則は、テーブルの説明テキストと同じです。それらの規則は、その他のテーブル要素とともに前述されています。
SQL では、すべてのレコードは RowID という一意の値によって識別される必要があります。Caché SQL では、RowID フィールドを指定する必要はありません。テーブルを作成して希望のデータ・フィールドを指定するとき、追加の RowID フィールドが自動的に作成されます。この RowID は内部的に使用されますが、クラス・プロパティにマップはされません。これはクラスが SQL に投影される場合にのみ、表示されます。この投影された SQL では、追加の RowID フィールドが表示されます。ユーザが上書きしない限り、このフィールドは常に "ID" という名前です。
例えば、
CREATE TABLE 文は次の 3 つのフィールドを指定します。それに対応するクラスは、Name、Age、Address の 3 つのクラス・プロパティを含みます。しかし、そのクラスから投影された SQL には、ID、Name、Age、Address の 4 つのフィールドが含まれます。
RowID に対して生成された値は、以下の制約を持っています。各値は一意です。NULL 値は許可されていません。対照は EXACT です。値は変更できません。
PRIMARY KEY 節を使用して、フィールドを主レコード識別子として明示的に定義することができます。主キーは一意の値のみを受け取り、NULL 値は受け取りません。主キーの照合タイプは、フィールドそのものの定義で指定されます。
定義されたフィールドを以下のように主キーとして指定します。
CREATE TABLE A1 (Field1 INT, Field2 INT, PRIMARY KEY (Field1))
CDL では、レコード識別子を IDKEY として識別することができます。多くの場合、SQL 主キーは CDL IDKEY に対応します。主キーと IDKEY 間のやりとりは、
Caché 構成マネージャの設定によって異なります。[詳細] タブの [SQL] オプションを展開し、
[DDL 経由で作成された主キーが ID キー] の現在の値を表示します。このオプションが "いいえ" (既定) に設定されている場合、主キーは IDKEY には対応しません。IDKEY ではない主キーを使用したレコードへのアクセスはかなり非効率的です。 しかし、主キーの値は変更することができます。このオプションが "はい" に設定されている場合、データ・アクセスはより効率的ですが、一度設定されたキー値は二度と変更することはできません。
定義済みの主キーを持つクラスが SQL に投影される場合、追加の "ID" という RowID フィールドが表示されます。 その値は IDKEY フィールドの値と同じになります。
1 フィールドに 1 つの主キーしか定義できません。2 つ以上の主キーを指定するとどうなるかは、
Caché 構成マネージャの設定によって異なります。[詳細] タブの [SQL] オプションを展開し、
[キーが存在する時に DDL を通して主キーを作成することを許可する] の現在の値を表示します。このオプションの設定が "いいえ" (既定) の場合、テーブルにすでに主キー制約が存在するのに DDL で主キー制約を追加しようとすると、Caché は SQLCODE -307 エラーを返します。主キーの第 2 定義が最初の定義と同じ場合もエラーを返します。
CREATE TABLE MyTable (f1 VARCHAR(16),
CONSTRAINT MyTablePK PRIMARY KEY (f1))
ALTER TABLE MyTable ADD CONSTRAINT MyTablePK PRIMARY KEY (f1)
上記の例は、SQLCODE -307 エラーを返します。
外部キーは他のテーブルを参照するフィールドです。 外部キー・フィールドに保存された値は、他のテーブル内のレコードに対する主キー値です。指定された外部キーは必ず存在し、一意である必要があります (主キーは重複値を持てないため)。外部キーが存在しないテーブルを参照する場合は、SQLCODE=–310 が返されます。外部キーが一意でないフィールドを参照する場合は、SQLCODE=–314 が返されます。
テーブルに外部キーが含まれる場合は、1 つのテーブルでの変更が他のテーブルにも影響します。外部キーを定義するときにデータの整合性を維持するには、外部キーの元となっているレコードを変更したときに外部キー値に与える影響も定義します。
外部キー定義は、以下の 2 つの参照動作節を含みます。
ON DELETE 節は、参照されるテーブルに対する DELETE ルールを定義します。参照されるテーブルからの行の削除が試みられるとき、ON DELETE 節は参照しているテーブルの行に対してどのような動作を行うべきかを定義します。
ON UPDATE 節は、参照されるテーブルに対する UPDATE ルールを定義します。参照されるテーブルから行の主キー値を変更 (更新) しようとする場合、参照しているテーブルの行に対してどのように作用するかは ON UPDATE 節に定義します。
Caché SQL は以下の外部キー参照動作をサポートします。
NO ACTION — 参照されるテーブル内の行が削除されたり、キー値が更新されるとき、参照されるすべてのテーブルは、削除や更新される行を参照している行があるかどうかをチェックします。参照している行がある場合は、削除や更新は失敗します。NO ACTION は既定です。
SET NULL — 参照されるテーブル内の行が削除されたり、そのキー値が更新されるとき、参照されるすべてのテーブルは、削除や更新される行を参照している行があるかどうかをチェックします。参照している行がある場合は、削除や更新されている行を参照する外部キー・フィールドが NULL に設定されます。外部キー・フィールドは、NULL 値を許可します。
SET NULL — 参照されるテーブル内の行が削除されたり、そのキー値が更新されるとき、参照されるすべてのテーブルは、削除や更新される行を参照している行があるかどうかをチェックします。参照している行がある場合は、削除や更新されている行を参照する外部キー・フィールドがそのフィールドの既定値に設定されます。外部キー・フィールドが既定値を持たない場合は、NULL に設定されます。行は参照されるテーブル内に存在しなければならないことに注意してください。 また、参照されるテーブルには、既定値に対するエントリが含まれます。
CASCADE — 参照されるテーブル内の行が削除される場合、参照するすべてのテーブルは、削除されている行を参照している行があるかどうかをチェックされます。参照している行がある場合は、削除することによって、削除される行を参照している外部キー・フィールドの行も削除されます。
参照されるテーブル内のキー値が更新されるとき、参照するすべてのテーブルは、更新されている行を参照している行があるかどうかをチェックされます。 参照している行がある場合は、更新することによって、更新されている行を参照する外部キー・フィールドが、参照している行すべてに対して引き続き更新を行います。
SET %msql="_SYSTEM"
&sql(CREATE TABLE MyPatients (
PatNum VARCHAR(16),
Name VARCHAR(30),
DOB DATE,
Primary_Physician VARCHAR(16) DEFAULT 'A10001982321',
CONSTRAINT Patient_PK PRIMARY KEY (PatNum),
CONSTRAINT Patient_Physician_FK FOREIGN KEY
Primary_Physician REFERENCES Physician (PatNum)
ON UPDATE CASCADE
ON DELETE SET NULL)
)
WRITE !,"SQL code: ",SQLCODE