DDL文
本日もまた、SQL基礎の勉強まとめ。今回は制約の話が中心。
DDL文とは、データベースのデータ構造を定義する言語を用いたSQL文
- CREATE
- DROP
- ALTER
- COMMIT
- TRUNCATE
- PRUGE ...など
実行後に現在のトランザクションをコミットする文を指す。
また、スキーマ・オブジェクトとはデータベースに格納する表やビュー、
索引などの総称で特定のユーザーに所有されるもの。
記憶域やロール、ユーザー等システム全体で共有されるものは
スキーマ・オブジェクトではない。
制約
制約とは、表に格納するデータに関するルールです。
表自体のアクセスを宣言するものではありません。
表に制約を定義することで、ルールに反するデータの追加や、
他の表との間に依存性のあるデータの誤削除を防止、
ルールを満たさなくなるようなデータの更新、削除を行うことができなくなります。
制約は列レベル、表レベルで定義することができます。
なお、制約はチェックのために時間が必要となるため、パフォーマンスは向上しません。
列レベルでの制約
--列レベル制約
CREATE TABLE ['スキーマ名'.]'表名'
(
'列名' 'データ型' [[CONSTRAINT '制約名'] '制約の種類']
[,'列名' 'データ型' [[CONSTRAINT '制約名'] '制約の種類']] ...
)
FOREIGN KEY制約は列レベル、表レベルのどちらでも定義することができます。
但し、複数の列を条件に使用する場合は、表レベルで定義しなければなりません。
表レベルでの制約
--表レベル制約
CREATE TABLE ['スキーマ名'.]'表名'
(
'列名' 'データ型'
[,'列名' 'データ型'] ...
[, [CONSTRAINT '制約名'] '制約の種類' ('列名' [,'列名' ...])]
[, [CONSTRAINT '制約名'] '制約の種類' ('列名' [,'列名' ...])] ...
)
列の組合せに対してPRIMARY KEY制約を定義する場合は、表レベルで定義しなければなりません。
列レベルでPRIMARY KEY制約を定義している場合、エラーとなります。
また、列の組合せに対してFOREIGN KEY制約を定義する場合は、表レベルで定義します。
制約の定義時の注意点
・CONSTRAINT 制約名は省略することができる
・CONSTRAINT 制約名を省略した場合は、「SYS_Cn」という制約名となる（nには一意の番号が振られる）
・1つの列に複数の列レベル制約を定義する場合は、スペースで区切って定義する
例) 列名 データ型 [[CONSTRAINT 制約名1] 制約の種類] [[CONSTRAINT 制約名2] 制約の種類]
・1つの表に複数の表レベル制約を定義する場合は、カンマで区切って定義する
・列レベル制約と表レベル制約では機能に違いはない
・列レベル制約と表レベル制約は1つの表で同時に指定できる
・複数の列の組み合わせに対して制約を定義する場合は、表レベル制約でのみ定義できる
・NOT NULL制約は列レベルでのみ定義できる
制約の種類
▽制約一覧
| 制約名 | 特徴 | NULL |
|---|---|---|
| NOT NULL制約 | NULL値を禁止する | × |
| PRIARY KEY制約 | 重複した値を禁止、NULL値も禁止する | × |
| UNIQUE制約 | 重複した値を禁止、NULL値は許可する | 〇 |
| FOREIGN KEY制約 | 参照先の主キーまたは、一意キーの列の値と一致する値か、NULL値を許可する | 〇 |
| CHECK制約 | 条件に合致した値とNULL値を許可する | 〇 |
●NOT NULL制約
NOT NULL制約は列レベルでしか定義することができません。
表レベルでNOT NULL制約を定義するとエラーとなります。
●PRIARY KEY制約
PRIMARY KEY制約は
・1つの表に1つしか定義することができない
・必ず指定しなければならないわけではない
・複数の列の組合せに対して定義することが可能(その場合は表レベルで定義)
・定義すると、自動的に制約と同じ名前の一意索引が作成され、
定義された列または列の組合せに重複したデータやNULL値を登録することができない
●UNIQUE制約
重複値は許可しないがNULL値は許可する制約です。
複数の列の組合せに対してUNIQUE制約を定義する場合は、
表レベルで定義しなければなりません。
また、表レベルで制約を定義する場合は、
制約を定義する列を指定しなければなりません。
●FOREIGN KEY制約
FOREIGN KEY制約は列レベル、表レベルのどちらでも定義することができます。
列の組合せに対してFOREIGN KEY制約を定義する場合は、表レベルで定義します。
また、FOREIGN KEY制約で参照できる親表の列は、
PRIMARY KEY制約またはUNIQUE制約が定義されている列だけです。
FOREIGN KEY制約の参照先にPRIMARY KEY制約またはUNIQUE制約が
定義されていない列を指定するとエラーとなります。
●CHECK制約
CHECK制約はWHERE句で指定できる条件と同様の指定ができますが、
下記のような制限があります。
・CURRVAL,NEXTVAL,LEVEL,ROWNUM疑似列
・SYSDATE,UID,USER,USERENV関数
・他の行を参照する問合せ
・BETWEEN演算子等は使用可能
オブジェクトの命名規則
オブジェクト名(表名、列名等)は以下の命名規則に従わなければなりません。
・オブジェクト名は30バイト以下
・使用できる文字は、0~9,A~Z,a~z(日本語環境の場合は漢字,ひらがな,カタカナも使用可)
・使用できる記号は、_,$,#のみ
・オブジェクト名の先頭の文字は、数字,記号以外の文字
・Oracleの予約語は使用できない
表の作成時、列のデフォルト値も設定できます。
デフォルト値はDEFAULTオプションで指定します。
'列名' 'データ型' [DEFAULT '式']
デフォルト値を設定すると、データの追加時にデフォルト値を設定した列への
値の入力が省略された場合に、式で指定した値が列に格納されます。
DEFAULTオプションの式には、リテラルの他、DEFAULTオプションを
指定する列や関数を指定できますが、他の列を参照することはできません。
オブジェクト名はスキーマ内で一意である必要があります。
そのため、スキーマ内の表やビューに同じ名前を使用することはできません。
ですが、異なるスキーマ同士では同じオブジェクト名を使用することができます。
スキーマ内でオブジェクト名は一意である必要がありますが、
異なるスキーマ同士では同じオブジェクト名を使用することができます。
列名はスキーマ・オブジェクトでは無い為、表名と同じ名前を使用することができます。
次回
次回は、やっとDDL文の実行について書く予定。長い。。
この記事、書くのに集中して加湿器が止まってたぁぁ。。。
お水を入れ替えて、DDL文編の編集します。
随時更新中＠あんず飴