楽教　Oracle table

整合性制約の種類

  NOT NULL制約/一意制約/主キー制約/参照整合性制約/チェック制約
  主キー制約はNULLでもOKですが、一意制約はNG

整合性制約のチェックのタイミング

  即時制約・・・DML文を発行した直後にチェックされるため、制約違反が発生しても、
                そのDML文がロールバックされるだけ。
  遅延制約・・・COMMIT時までチェックされないため、制約違反が発生すると、
                トランザクションがロールバックされる。

  ・パラメータ

    NOT DEFERRABLE     ・・・即時にチェック(デフォルト)
    DEFERRABLE         ・・・遅延可能
    INITIALLY IMMEDIATE・・・即時制約(デフォルト)
    INITIALLY DEFERRED ・・・遅延制約

CREATE TABLE [スキーマ名.]表名
　　・・・
  [CONSTRAINT 制約名]
       {PRIMARY KEY (列名 [,列名...]) [USING INDEX 索引句]
       |UNIQUE (列名 [,列名...]) [USING INDEX 索引句]
       |FOREIGN KEY (列名 [,列名...])
        REFERENCES [スキーマ名.]表名 [(列名)] [ON DELETE CASCADE]
       |CHECK (条件) }
  [NOT DEFERRABLE
  | DEFERRABLE [INITIALLY {IMMEDIATE | DEFERRAED}] ]
  [DISABLE | ENABLE [VALIDATE | NOVALIDATE] ];

表の作成後に制約を変更

ALTER TABLE [スキーマ名.]表名
  [CONSTRAINTS 制約名
      [NOT DEFERRABLE
      | DEFERRABLE [INITIALLY {IMMEDIATE | DEFERRAED}] ]
      [DISABLE | ENABLE [VALIDATE | NOVALIDATE] ];

制約違反のタイミングの変更

ALTER SESSION SET CONSTRAINT[S] = 
   {IMMEDIATE | DEFERRED | DEFAULT};
SET CONSTRAINT[S] {制約名 [,制約名] | ALL }
   {IMMEDIATE | DEFERRED}

制約の状態

  DISABLE NOVALIDATE・・・使用禁止(ディクショナリに情報としてあるだけ。チェックされない)
  DISABLE VALIDATE  ・・・妥当性検査済み使用禁止(既存データのチェックのみ行う)
  ENABLE NOVALIDATE ・・・妥当性検査なし使用可能(新規データのみチェックを行う)
  ENABLE VALIDATE   ・・・妥当性検査済み使用可能(新規データ、既存データともにチェックを行う)

制約の使用禁止

ALTER TABLE [スキーマ名.]表名
  DISABLE {CONSTRAINTS 制約名
          | PRIMARY KEY
          | UNIQUE (列名 [,列名...])}
  [CASCADE];

制約と索引

  一意制約と主キー制約に索引を利用する。
  すでに索引がある場合はそれを使用し、無い場合は制約名と同じ名前で索引を作成する。

制約の使用可能可

ALTER TABLE [スキーマ名.]表名
  ENABLE [VALIDATE | NOVALIDATE]
    {CONSTRAINTS 制約名
    | PRIMARY KEY
    | UNIQUE KEY (列名 [,列名...]}
  [USING INDEX 索引句]
  [EXCEPTIONS INTO [スキーマ名.]例外表名];

例外表(EXCEPTION表)

  制約に違反するレコードの情報をセットしてくれる。
  
  作業手順

    1. 例外表を作成する。utlexcpt.sqlを実行。$ORACLE_HOME/rdbms/adminにある。
    2. EXCEPTION句月のALTER TABLE文を実行して、制約違反データを検出する。 
    3. 例外表を問い合わせして、違反データを確認する。 
    4. データを修正する。 
    5. 再度ALTER TABLE文を実行する。

制約の削除

ALTER TABLE [スキーマ名.]表名
  DROP {CONSTRAINTS 制約名
       | PRIMARY KEY
       | UNIQUE (列名 [,列名...])}
  [CASCADE];

制約に関する情報の取得

  DBA_CONSTRAINTS/DBA_CONS_COLUMNSから取得。