列の制約タイプを定義する

テーブル内のデータが業務ルールに従うように、列に制約を定義できます。

制約は列に定義され、列に格納される値を制限します。その列に制約定義に違反する値を書き込もうとしたり更新しようとした場合、エラーがトリガーされ、この操作がロールバックされます。また、既存のテーブルの列に既存データと競合する制約を追加しようとした場合も、エラーがトリガーされ、この操作がロールバックされます。

制約タイプ

OceanBaseデータベースのOracleモードは、NOT NULL制約（NOT NULL）、一意性制約（UNIQUE）、主キー制約（PRIMARY KEY）、外部キー制約（FOREIGN KEY）、およびCHECK制約などの制約タイプをサポートしています。

NOT NULL制約（NOT NULL）

NOT NULL制約は、制約対象の列の値がNULLであることを許可しません。NOT NULL制約が設定されている列では、その列にデフォルト値が定義されていない限り、INSERTステートメントでその列の値を指定する必要があります。

たとえば、テーブルtbl1を作成し、列col1にNOT NULL制約を設定する例は以下のとおりです：

obclient> CREATE TABLE tbl1(col1 INT NOT NULL,col2 INT);

一意性制約（UNIQUE）

一意性制約は、制約対象の列の値に重複する値を持つことを許可しませんが、複数のNULL値を持つことは可能です。

たとえば、テーブルtbl2を作成し、col1列の値を一意にする例は以下のとおりです：

obclient> CREATE TABLE tbl2(col1 INT UNIQUE,col2 INT);

主キー制約（PRIMARY KEY）

主キー制約は、NOT NULL 制約と一意性制約の組み合わせです。

たとえば、テーブルtbl3を作成し、col1列を主キー列に指定する例は以下のとおりです：

obclient> CREATE TABLE tbl3(col1 INT PRIMARY KEY,col2 INT);

この例では、col1列の値はNULLにすることができず、また重複していてはなりません。

外部キー制約（FOREIGN KEY）

外部キー制約は、制約対象の列の値が別のテーブル（親テーブル）の主キー列から取得されることを要求します。

外部キー制約を作成する際には、親テーブルの外部キー参照列にDELETE操作が実行された場合に、子テーブルの対応する行の外部キー列に対するカスケード操作を指定することもできます：

• CASCADE：親テーブルの外部キー参照列が削除された場合、子テーブルの対応する行の外部キー列を自動的に削除することを意味します。

• SET NULL：親テーブルの外部キー参照列が削除された場合、子テーブルの対応する行の外部キー列の値をNULLに設定することを意味します。

  外部キー制約のカスケード操作をSET NULLに指定する場合、外部キー列は生成列であってはなりません。

外部キーを作成する際にON DELETE句を指定しない場合、親テーブルの外部キー参照列が削除されると、外部キー制約違反により操作が拒否されます。

たとえば、テーブルtbl4を作成し、col2列をテーブルtbl3の主キー列col1に関連付ける例は以下のとおりです。

obclient> CREATE TABLE tbl4(col1 INT,col2 INT,FOREIGN KEY(col2) REFERENCES tbl3(col1) ON DELETE SET NULL);

この例では、親テーブルtbl3の列col1に対する削除操作を実行すると、システムは子テーブルの対応する行の外部キー列の値をNULLに設定します。

CHECK制約

CHECK制約は、データベース内の特定の列の値が指定された条件に適合することを要求します。

制約の確認

制約を作成した後、ビュー ALL_CONS_COLUMNS、DBA_CONS_COLUMNS、 USER_CONS_COLUMNS を使用してテーブル内の制約を照会できます。

例：

obclient> SELECT * FROM USER_CONS_COLUMNS WHERE table_name='TBL3';
+-------+----------------------------+------------+-------------+----------+
| OWNER | CONSTRAINT_NAME            | TABLE_NAME | COLUMN_NAME | POSITION |
+-------+----------------------------+------------+-------------+----------+
| SYS   | TBL4_OBPK_1649992639477914 | TBL3       | COL1        |        1 |
+-------+----------------------------+------------+-------------+----------+
1 row in set

マネジメント制約

主キー制約の管理

テーブル作成後、テーブルに主キーを追加したり、既存の主キーを変更したりできます。

• 既存のテーブルに主キーを追加します。

  SQL構文は次のとおりです：

  obclient> ALTER TABLE table_name ADD PRIMARY KEY(column_name);
  

  テーブル作成時に主キーを追加することを推奨します。

• テーブル内の主キー列を変更します。

  SQL構文は次のとおりです：

  obclient> ALTER TABLE table_name MODIFY PRIMARY KEY(column_name);
  

• テーブル内の主キーを削除します。

  SQL構文は次のとおりです：

  obclient> ALTER TABLE table_name DROP PRIMARY KEY;
  

例：

1. テーブル tbl4 を作成します。

   obclient> CREATE TABLE tbl4(col1 INT,col2 INT);
   

2. テーブルに主キーを追加します。

   obclient> ALTER TABLE tbl4 ADD PRIMARY KEY(col1);
   

3. テーブルの主キー列を col2 に変更します。

   obclient> ALTER TABLE tbl4 MODIFY PRIMARY KEY(col2);
   

4. テーブル内の主キーを削除します。

   obclient> ALTER TABLE tbl4 DROP PRIMARY KEY;
   

外部キー制約の管理

テーブル作成後、テーブル内の外部キーに対して以下の操作を実行できます。

• 外部キー制約を有効または無効にします。

  デフォルトでは、制約は作成後に有効な状態になります。テーブル作成時に外部キー制約が追加されている場合、この制約を有効または無効にすることができます。

  SQL構文は次のとおりです：

  obclient> ALTER TABLE table_name ENABLE | DISABLE CONSTRAINT constrain_name ;
  

• 既存のテーブルに外部キー制約を追加します。

  構文は次のとおりです：

  obclient> ALTER TABLE table_name1 ADD CONSTRAINT fk_name FOREIGN KEY (column_name1) REFERENCES table_name2(column_name2);
  

  ここで：

  • table_name1 は制約を追加するテーブル名を表します。table_name2 は関連テーブルの名前を表します。

  • fk_name は追加する外部キー制約の名前を表します。

  • column_name1 は指定する制約の列名を表します。column_name2 は関連テーブルの主キー列の名前を表します。

• テーブル内の外部キーを削除します。

  ステートメントは次のとおりです：

  obclient> ALTER TABLE test DROP CONSTRAINT fk_name;
  

例：

1. テーブル tbl5 と tbl6 を作成します。

   obclient> CREATE TABLE tbl5(col1 INT PRIMARY KEY,col2 INT);
   

   obclient> CREATE TABLE tbl6(col3 INT,col4 INT);
   

2. テーブル tbl6 に外部キー制約を追加します。

   obclient> ALTER TABLE tbl6 ADD CONSTRAINT fk FOREIGN KEY(col4) REFERENCES  tbl5(col1);
   

3. テーブル tbl6 内の外部キーを削除します。

   obclient> ALTER TABLE tbl6 DROP CONSTRAINT fk;
   

CHECK制約の管理

テーブル作成後、テーブル内のCHECK制約に対して以下の操作を実行できます：

• CHECK制約を有効または無効にします。

  テーブル作成時にCHECK制約が追加されている場合、この制約を有効または無効にすることができます。

  SQL構文は次のとおりです：

  obclient> ALTER TABLE table_name ENABLE | DISABLE CONSTRAINT constrain_name ;
  

• 既存のテーブルにCHECK制約を追加します。

  SQL構文は次のとおりです：

  ALTER TABLE table_name ADD CONSTRAINT constrain_name CHECK(expression);
  

  例：

  obclient> ALTER TABLE ware ADD CONSTRAINT cst CHECK(w_city='hz');
  

NULL値禁止制約の管理

テーブル作成後、既存のテーブルにNOT NULL制約を追加または削除できます：

• 既存のテーブルに（NOT NULL）制約を追加します。

  SQL構文は次のとおりです：

  obclient> ALTER TABLE table_name MODIFY c1 NOT NULL;
  

• テーブルのNOT NULL制約を削除します。

  SQL構文は次のとおりです：

  obclient> ALTER TABLE table_name DROP CONSTRAINT constraint_name;
  

  または、次の構文を使用してNOT NULL制約を削除することもできます：

  obclient> ALTER TABLE table_name MODIFY c1 NULL;
  

その他の制約管理の構文および詳細については、ALTER TABLEを参照してください。

時間列のデフォルト時間設定について

列にNOT NULL制約がある場合、通常はデフォルト値を設定することを推奨します。列の型が日付または時間型の場合、デフォルト値をデータベースの現在時刻に設定できます。

例：テーブルの時間列にデフォルト値を設定するには、current_timestamp関数を使用できます。

1. テーブルt1を作成し、列gmt_createとgmt_modifiedのデフォルト値をデータベースの現在時刻に指定します。

   obclient> CREATE TABLE t1(
      id INT NOT NULL PRIMARY KEY,
      gmt_create TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   );
   

2. テーブルにデータを挿入します。

   obclient> INSERT INTO t1(id) VALUES(1),(2),(3);
   

3. データの挿入に成功した後、テーブル内のデータを確認します。

   obclient> SELECT * FROM t1;
   

   クエリ結果は次のとおりです：

   +----+------------------------------+------------------------------+
   | ID | GMT_CREATE                   | GMT_MODIFIED                 |
   +----+------------------------------+------------------------------+
   |  1 | 10-NOV-23 10.12.36.417000 AM | 10-NOV-23 10.12.36.417000 AM |
   |  2 | 10-NOV-23 10.12.36.417000 AM | 10-NOV-23 10.12.36.417000 AM |
   |  3 | 10-NOV-23 10.12.36.417000 AM | 10-NOV-23 10.12.36.417000 AM |
   +----+------------------------------+------------------------------+
   3 rows in set