07-02.テーブル【テーブル制約条件(Constraint)】

■制約条件（Constraint）とは？

　制約条件とはテーブルへ不適切なデーターが入力されるのを防ぐため、

　規則を適用しているのと考えればよい。

　テーブル内でデーターの性格を定義するのが制約条件だ。

　-制約条件はデーターの整合性維持のためユーザーが指定するもの

　-すべての制約はDATA DICTIONARYへ保存される。

　-制約はテーブル作成時または作成後ALTERコマンドで定義可能だ。

　-NOT NULL制約条件は必ずコラムレベルのみで定義可能だ。

■NOT NULL条件

　コラムを必須フィールド化する時、定義する。

--NOt NULL制約条件を設定すると、enameコラムには必ずデーターを入力しなければいけない。
--emp_nn_enameを制約条件名と設定した。

SQL> CREATE TABLE emp3(
  2  ename VARCHAR2(20) CONSTRAINT emp_nn_ename NOT NULL );

表が作成されました。


--制約条件はUSER_CONSTRAINTSビューで確認できる。

SQL> SELECT CONSTRAINT_NAME
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME = 'EMP3';

CONSTRAINT_NAME
------------------------------
EMP_NN_ENAME

SQL>

■UNIQUE条件
　同じ値を持つ行（レコード）を許さない。自動でインデックスが指定される。

--deptnoコラムへUNIQUE制約条件を定義

SQL> ALTER TABLE emp2
  2  ADD CONSTRAINT emp2_uk_deptno UNIQUE (deptno);

表が変更されました。


--制約条件を削除

SQL> ALTER TABLE emp2
  2  DROP CONSTRAINT emp2_uk_deptno;

表が変更されました。

SQL>

■CHECK条件
　コラムの値を特定の範囲に制限できる。

--comnコラムへ1～100の値のみ入れるCHECK条件を定義

SQL> ALTER TABLE emp2
  2  ADD CONSTRAINT emp2_ck_comn
  3  CHECK (comn >=1 AND comn <= 100);

表が変更されました。

SQL>


--制約条件の削除

SQL> ALTER TABLE emp2
  2  DROP CONSTRAINT emp2_ck_comn;

表が変更されました。

SQL>


--10000,20000,30000,40000,50000の値のみ入れるCHECK条件を定義

SQL> ALTER TABLE emp2
  2  ADD CONSTRAINT emp2_ck_comn
  3  CHECK (comn IN (10000, 20000, 30000, 40000, 50000));

表が変更されました。

SQL>

■DEFAULT(コラム基本値)指定
　データーを入力しなくても指定された値が基本的に入力される。

--hiredateコラムに値を入力しなくても今日の日付が入る。

SQL> CREATE TABLE emp4(
  2  EMPNO NUMBER CONSTRAINT emp4_pk_empno PRIMARY KEY,
  3  ENAME VARCHAR2(20),
  4  JOB VARCHAR2(40),
  5  MGR NUMBER,
  6  HIREDATE DATE DEFAULT SYSDATE );

表が作成されました。

SQL>

■PRIMARY KEY指定
　-主キーはUNIQUEとNOT NULLの結合と言える。
　-主キーはそのデーター行を代表するコラムの役割を果たし、他テーブルから外部キーとして参照できる。
　-UNIQUE条件同様、主キーを定義すると自動的にINDEXが作られ、その名前は主キー制約条件の名前と同じだ。
　-INDEX検索キーで検索速度を向上させる
　　（UNIQUE、PRIMARY KEY作成時、自動的に作成される）

--PRIMARY KEY作成例文

SQL> CREATE TABLE emp5(
  2  empno NUMBER CONSTRAINT emp5_pk_empno PRIMARY KEY );

表が作成されました。

SQL>

--ALTER TABLEコマンドでPRIMARY KEY作成例文

SQL> ALTER TABLE emp2
  2  ADD CONSTRAINT emp2_pk_empno  PRIMARY KEY (empno) ;

■FOREIGN KEY(外部キー)指定
　-主キーを参照するコラムまたはコラムの集まり。
　-外部キーを持つコラムのデーター形は外部キーが参照する主キーのコラムとデーター形と一致しなければならない。
　-外部キーにより参照する主キーは削除できない。
　-ON DELETE CASCADEで定義された外部キーのデーターはその主キーが削除される時一緒に削除される。

--deptテーブルのdeptnoコラムを主キーに設定する。

SQL> ALTER TABLE dept
  2  ADD CONSTRAINT dept_pk_depno PRIMARY KEY (deptno);

表が変更されました。


--empテーブルのdeptnoコラムがdeptテーブルのdeptnoコラムを参照する外部キーを作成

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_fk_deptno
  2  FOREIGN KEY (deptno)  REFERENCES dept(deptno);

表が変更されました。

SQL>

■制約条件の確認
　-USER_CONS_COLUMNS : コラムに設定された制約条件確認
　-USER_CONSTRAINTS : ユーザーが持つすべての制約条件確認

SQL> SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,
  2  DECODE(B.CONSTRAINT_TYPE,
  3  'P','PRIMARY KEY',
  4  'U','UNIQUE KEY',
  5  'C','CHECK OR NOT NULL',
  6  'R','FOREIGN KEY') CONSTRAINT_TYPE,
  7  A.CONSTRAINT_NAME
  8  FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
  9  WHERE A.TABLE_NAME = UPPER('&table_name')
 10  AND A.TABLE_NAME = B.TABLE_NAME
 11  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 12  ORDER BY 1;


--テーブル名を入力する

table_nameに値を入力してください: emp2
旧   9: WHERE A.TABLE_NAME = UPPER('&table_name')
新   9: WHERE A.TABLE_NAME = UPPER('emp2')

COLUMN_NAME                    CONSTRAINT_TYPE   CONSTRAINT_NAME
------------------------------ ----------------- ------------------------------
COMN                           CHECK OR NOT NULL EMP2_CK_COMN
DEPTNO                         FOREIGN KEY       EMP2_FK_DEPTNO
EMPNO                          PRIMARY KEY       EMP_PK_EMPNO

SQL>