2010年12月08日

11.表の作成と管理

●データベースオブジェクト
表やビュー、索引などのデータベースの中で扱われるものをデータベースオブジェク
トという。

データベースオブジェクト
・表…データを格納する。
・ビュー…1つ以上の表からのデータのサブセット。データは保持しない
・順序…一意な数値を生成する
・索引…問合せのパフォーマンスを向上させる目的で使用される。
・シノニム…オブジェクトの別名

・ネーミング規則
データベースオブジェクトは、スキーマ内で一意でなければならない。

オブジェクト名のネーミングルール
・文字で開始する必要がある
・長さ30バイト以下
・英文字、記号が使用可能
・Oracleの予約語(例:CREATE,TABLE)は使用できない。
・同一のスキーマ内で一意な表名、同一表内で一意な列名でなければならない
・大文字/小文字は区別されない

●スキーマ
スキーマ…オブジェクトの集合
例)販売管理システム=商品、売上などの複数の表=オブジェクトの集合
Oracleでは、スキーマを所有するユーザー名をスキーマ名とする。
ユーザーが、SQLにおいてオブジェクト名を指定する場合、自スキーマ内のオブジェ
クトとして解釈される。
他スキーマのオブジェクトを扱うときには、次のようにオブジェクト名の前にスキー
マ名を指定する
スキーマ名.オブジェクト名

例)SYSTEMユーザーが他スキーマ(STUDY)の部門表を検索する
SQL>SELECT * FROM study.部門;

※ユーザーはそれぞれ1つのスキーマを所有する

●表
表は、リレーショナルデータベースのデータを格納するモデル。
表を作成するときには、表名、列名、列のデータ型、制約などを指定する。
・表の作成
表の作成には、CREATE TABLE文を使用する。
 
構文)
CREATE TABLE 表名
   [列名 データ型 [ DEFAULT 式]
   [ ,列名 データ型 ] ・・・)
   [ TABLESPACE 表領域名 ]
 
例)
SQL>CREATE TABLE 大阪社員
    ( 社員番号 NUBMER,
     社員名  VARCHAR2(20),
     入社日  DATE;
 
・DEFAULTオプション
DEFAULTオプションを使用することにより、列にデフォルト値を設定することができる。
列に値がない行を挿入すると、DEFAULTオプションで指定された値が設定される。
これにより、NULL値が挿入されることを防止できる。
デフォルト値には、リテラル、式、またはSYSDATEなどのSQL関数を使用できる。

※デフォルト値に指定する式や値は列のデータ型に一致させる必要がある。
デフォルト値を設定してある列にNULLを挿入したい場合は、明示的にNULLを指定する。
 
●データ型
CHAR(size)・・・固定長文字データ
VARCHAR2(size)・・・可変長文字データ
LONG・・・可変長文字データ(最大2GB)
CLOG・・・文字データ(最大4GB)
NUMBER(p,s)・・・可変長数値データ
DATE・・・日付と時刻の値
RAW および LONG RAW・・・RAWバイナリデータ
BLOB・・・バイナリデータ(最大4GB)
BFILE・・・外部ファイルに格納されるバイナリデータ(最大4GB)
ROWID・・・表名の行の一意のアドレスを表す、Base64の記数法
 
 
●制約
・制約の作成
制約には、列単位に定義する「列制約構文」と、表単位に定義する「表制約構文」がある。
制約を作成するには、CREATE TABLE文で、制約条件を列または表単位に定義する。
列制約構文と表制約構文は混在して使用することができる。
NOT NULL制約は列制約でのみ定義可能。
複数の列を組み合わせて1つの制約を定義する場合は表制約で定義する必要がある
 
列制約構文)
CREATE TABLE 表名
( 列名 データ型 [ CONSTRAINT 制約名 ] 制約
        [[ CONSTRAINT 制約名 ] 制約 ・・・]
[ , 列名 データ型 [ CONSTRAINT 制約名 ] 制約
        [[ CONSTRAINT 制約名 ] 制約 ・・・]])
 
表制約構文)
CREATE TABLE 表名
( 列名 データ型
 [ , 列名 データ型 ] ・・・
  , [ CONSTRAINT 制約名 ] 制約
  [ , [ CONSTRAINT 制約名 ] 制約 ・・・])

※制約名はオブジェクトのネーミング規則に従う。また、スキーマ内の他のオブジェクト名と重複してはいけない。
制約名を省略すると、SYS_Cn(nには一意な数値)の形式で名前が付けられる。
制約はデータディクショナリーに格納される。
 
●NOT NULL制約
NOT NULL制約はNULL値を禁止する。列制約構文のみで指定可能
 
例)NOT NULL制約の定義
SQL>CREATE TABLE test1
    (col1 NUMBER CONSTRAINT test1_col1_nn NOT NULL,
     col2 VARCHAR2(10));
 
●UNIQUE制約
指定された列または列の組み合わせに同じ値は入力されることを禁止する
同じ列にNOT NULLが定義されていない限り、1つ以上のNULL値を入力できる
→UNIQUE制約はNULL以外の値が重複していないかを評価する
Oracleは、値が重複していないことを評価するために、UNIQUE制約を定義すると、
暗黙的に制約名と同じ名前の一意索引を作成する。
 
例)列制約構文を使用して制約を定義する
SQL>CREATE TABLE test2
    (col2 NUMBER CONSTRAINT test2_col2_U UNIQUE,
     col2 VARCHAR2(10));
 
例)複合UNIQUE制約の定義
SQL>CREATE TABLE test3
    (col1 NUMBER,
     col2 VARCHAR2(10),
     CONSTRAINT col1_col2_U UNIQUE(col1,col2));
 
※複合UNIQUE制約の場合、列単独で見ると値が重複していも、列の組み合わせで値が重複していなければ、行を挿入することができる。
 
●CHECK制約
明示的に入力条件を定義し、条件を満たすデータのみが入力可能。 
 
例)列制約構文と表制約構文を使用したCHECK制約
SQL>CREATE test4
    (col1 NUMBER CONSTRAINT test4_col1_CK
               CHECK (col1 BETWEEN 100 AND 999),
    col2 CHAR(1),
    CONSTRAINT test4_col2_CK CHECK (col2 IN ('M','F')));
 
CHECK制約の場合は、CHECKキーワードの後ろに「(列名 条件)」を記述するので、
列制約構文でも表制約構文でも記述の仕方は同じ。

CHECK制約は問合わせの条件と同じ要素を使用できるが、次の指定はできない。
・CURRVAL,NEXTVAL,LEVEL,ROWNUM疑似列の参照
・SYSDATE,UID,USER,USERENV関数の使用
・別の行の値を参照する問合せ
 
●PRIMARY KEY制約
表名の行データを一意に特定できる列または列の組み合わせの中で主たるもおを主キー(PRIMARY KEY)という。
PRIMARY KEY制約は、自動的にUNIQUE制約とNOT NULL制約が不可される。
複数の列を組み合わせた複合主キーは表制約構文のみで指定可能。
Oracleは、値が重複していないことを評価するために、PRIMARY KEY制約を定義すると、制約名と同じ名前の一意索引を暗黙的に作成する。
 
例)PRIMARY KEY制約を列制約構文で定義する
SQL>CREATE TABLE test5
    (col1 NUMBER CONSTRAINT test5_PK PRIMARY KEY,
     col2 VARCHAR2(10));

※PRIMARY KEY制約を宣言できるのは、表にただ1つだけ。
 
●FOREIGN KEY(外部キー)制約
FOREIGN KEY制約は、行と行の間の関係付けを保つために使用される。
子表(参照する側)の参照列のことを外部キー(FOREIGN KEY)という。
親表(参照される側)の参照される列のことを親キーという。
親キーはREFERENCES句を用いて指定する。
外部キーが参照する先は、同じ表の異なる列でもかまわない。
親キーは、主キー(PRIMARY KEY)または一意キー(UNIQUE)のいずれかでなくてはいけない。
 
列制約構文)
CREATE TABLE 表名
 (列名 データ型 CONSTRAINT 制約名 REFERENCES 参照表[(参照列)],・・・) 
※参照列は省略することができる。省略した場合は、参照表の主キー列を参照する。
例)部門表を参照するFOREIGN KEY制約
SQL>CREATE TABLE 部門
(部門番号 NUMBER CONSTRAINT 部門_PK PRIMARY KEY,
部門名 VARCHAR2(20));

SQL>CREATE TABLE 社員
(社員番号 NUMBER CONSTRAINT 社員_PK PRIMARY KEY,
社員名 VARCHAR2(20),
部門番号 NUMBER CONSTRAINT 社員_部門_FK
                REFERENCES 部門(部門番号));
※外部キーの値は、親表に存在する値またはNULLでなければならない。
(NOT NULL制限が同時に定義されていない場合に限る)

表制約構文)
CREATE TABLE 表名
(列名 データ型,
・・・,
CONSTRAINT 制約名 FOREIGN KEY(外部キー列) REFERENCES 参照表[(参照列)],
・・・)
 
表制約構文の場合は「FOREIGN KEY(外部キー列)」を記述する必要がある。
参照先の参照列は省略することができる。省略した場合、参照先の主キー列を参照する。

例)
SQL>CREATE TABLE 社員2
    (社員番号 NUMBER CONSTRAINT 社員2_PK PRIMARY KEY,
     社員名 VARCHAR2(20),
     上司番号 NUMBER,
     部門番号 NUMBER,
     CONSTRAINT 社員2上司_FK FOREIGN KEY (上司番号)
     REFERENCES 社員2(社員番号));
 


●ON DELETEキーワード
FOREIGN KEY制約には、親表の行が削除されたら、子表の該当行も一緒に削除するという定義ができる。
ON DELETE CASCADEキーワードを明記することで、参照されている親表の行を削除する際に、
エラーを起こさずに、参照している行も一緒に削除することができる。
 
例)CREATE TABLE 注文明細
(注文明細番号 NUMBER CONSTRAINT 注文M_PK PRIMARY KEY,
注文番号    NUMBER CONSTRAINT 注文M_注文番号_FK PREFERENCE 注文ヘッダー(注文番号)
                                          ON DELETE CASCADE,
商品名      VARCHAR2(20),
数量        NUMBER);
 
FOREIGN KEY制約で指定できるキーワード
ON DELETE CASCADE 参照している親表の行が削除されたら、子表の該当行も一緒に削除する
ON DELETE SET NULL 参照している親表の行が削除されたら、子表の該当行の外部キー列の値をNULLにする


●既存表からの新しい表の作成
 
構文)
CREATE TABLE 表名
[(列名,列名,・・・)]
AS 副問合せ
 
例)
CLEATE TABLE 社員_営業 AS
SELECT 社員番号,社員名,給与 FROM 社員
WHERE 部門バング = 30;

 
列名を明記する方法)
CLEATE TABLE 部門別給与合計
(部署NO,給与合計,人数)
AS SELECT 部門番号,SUM(給与),COUNT(*)
FROM 社員
GROUP BY 部門番号;







 
SELECT リストに列別名を指定する方法)
CLEATE TABLE 部門別給与合計
AS SELECT 部門番号 AS 部門NO,
         SUM(給与) AS 給与合計,
         COUNT(*) AS 人数
 FROM 社員
 GROUP BY 部門番号;
 
<注意点>
・列定義には、列名、デフォルト値、整合性制約を含めることができる。
・列を指定する場合、列数を副問合せのSELECTリストと一致させなければならない。
・表は指定された列名で作成され、副問合せによって取り出された行が表に挿入される。
・列を指定しない場合、表の列名は、副問合せのSELECTリストの列名と同じ。
・副問合せのSELECTリストに式を指定する場合は、列別名を指定する。
・NOT NULL制約とデータ型のみコピーされる
・NOT NULL以外の制約はコピーされない。














tetora119 at 22:59│Comments(4)TrackBack(0)

トラックバックURL

この記事へのコメント

1. Posted by カサハンラ   2011年06月02日 21:22
まとめが素晴らしいですね!
2. Posted by スタービーチ   2011年09月28日 23:42
日本一出会えるスタービーチで本当の恋を探してみませんか?恋人がいる人はみんなこのサイトで作っている!
3. Posted by モバゲー   2011年12月12日 02:19
モバゲーで異性と出会える攻略法!モバゲーするなら知らないと損する情報満載のモバゲー攻略サイト!
4. Posted by ガンガンガン速   2012年06月05日 20:21
んー、難しそうだけど、ためになりますな

コメントする

名前
URL
 
  絵文字