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

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

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

制約タイプ

現在のOceanBaseデータベースのMySQLモードは、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制約 (UNIQUE)

UNIQUE制約は、制約を含む列の値に重複する値を許可しませんが、複数の NULL 値を持つことはできます。

テーブル tbl6 を作成し、列 col1 にUNIQUE制約を設定する例は次のとおりです：

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

主キー制約 (PRIMARY KEY)

主キー制約 (PRIMARY KEY)： NOT NULL 制約とUNIQUE制約の組み合わせです。

テーブル tbl2 を作成し、列 col1 を主キーとして設定する例は次のとおりです：

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

この例では、テーブル tbl2 に主キー列 col1 があり、この列は NULL にすることができず、また重複していてはなりません。

外部キー制約 (FOREIGN KEY)

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

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

• RESTRICT：親テーブルの外部キー参照列が削除または更新された場合、親テーブルへの削除または更新操作を拒否することを意味します。

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

  外部キー列のカスケード操作を CASCADE と指定する場合、以下の条件を満たす必要があります：

  • その外部キーはストレージ生成列に定義できません。

  • その外部キーは生成列のベース列に定義できません。

• NO ACTION：RESTRICT と同じです。親テーブルの外部キー参照列が削除または更新された場合、親テーブルへの削除または更新操作を拒否することを意味します。

  外部キーを作成する際に ON DELETE または ON UPDATE 句を指定しない場合、デフォルトの操作は常に NO ACTION となります。

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

  外部キー列のカスケード操作を SET NULL と指定する場合、以下の条件を満たす必要があります：

  • その外部キー列は NOT NULL として定義できません。

  • その外部キーはストレージ生成列に定義できません。

  • その外部キーは生成列のベース列に定義できません。

例えば、テーブル tbl4 を作成し、列 col2 をテーブル tbl2 の主キー列 col1 に関連付ける場合です。

obclient> CREATE TABLE tbl4(col1 INT,col2 INT,FOREIGN KEY(col2) REFERENCES tbl2(col1) ON UPDATE CASCADE ON DELETE RESTRICT);

この例では、親テーブル tbl2 の列 col1 に対する更新操作が実行されると、子テーブルの対応する行の外部キー列が自動的に更新されます。親テーブル tbl2 の列 col1 に対する削除操作が実行されると、親テーブルの操作は拒否されます。

外部キー制約を作成する際に外部キー名を指定しない場合、システムは自動的に制約名を割り当てます。自動的に割り当てられる制約名は テーブル名_OBFK_作成タイムスタンプ です。例えば、t1_OBFK_1627747200000000 です。

OceanBaseデータベースでは、外部キー制約チェックがデフォルトで有効になっています。外部キー制約チェックのスイッチは、テナント変数 foreign_key_checks によって制御されます。

foreign_key_checks 変数の詳細については、foreign_key_checks を参照してください。

CHECK制約

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

単一の列に対して1つ以上の CHECK 制約を定義し、その列に特定の値のみを許可するようにすることもできますし、テーブルレベルの CHECK 制約を定義して、1つの CHECK 制約を複数の列に適用することもできます。テーブル名を変更しても、CHECK 制約名は変更されません。また、特定のテーブルを削除すると、そのテーブルに適用されている CHECK 制約も同時に削除されます。

テーブル tbl8 を作成し、列 col1 の値が 0 より大きいことを要求する例は以下のとおりです：

obclient> CREATE TABLE tbl8(col1 INT CHECK(col1>0),col2 INT);
Query OK, 0 rows affected

CHECK 制約を作成する際、制約名を指定しない場合、システムは自動的に制約名を割り当てます。自動割り当てられる制約名は テーブル名_obchk_作成タイムスタンプ となります。例えば、t1_OBCHECK_1629350823880271 となります。

制約を作成するための構文や詳細については、CREATE TABLEを参照してください。

制約の確認

制約が正常に作成された後、information_schema.TABLE_CONSTRAINTSビューを使用して特定のテーブルの制約を確認できます。例：

obclient> SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='tbl6';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | test              | col1            | test         | tbl6       | UNIQUE          |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
1 row in set

制約の管理

OceanBaseデータベースでは、制約に対して以下の管理がサポートされています。

主キー制約の管理

OceanBaseデータベースは、既存のテーブルに主キー制約を追加したり、主キー制約を変更したりすることをサポートしています。

主キー制約を追加するSQL構文は以下のとおりです：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

主キーを削除するSQL構文は以下のとおりです：

ALTER TABLE table_name DROP PRIMARY KEY;

例：

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

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

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

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

3. テーブル tbl2 の主キーを削除します。

   obclient> ALTER TABLE tbl2 DROP PRIMARY KEY;
   

外部キー制約の管理

OceanBaseデータベースは、既存のテーブルに外部キー制約を追加したり、外部キー制約を削除したりすることをサポートしています。

例：

• まずテーブル tbl5 を作成し、次にテーブル tbl5 の列 col2 に外部キーを追加します。関連する主テーブルは tbl2 です。

  obclient> CREATE TABLE tbl5(col1 INT,col2 INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl5 ADD CONSTRAINT tbl5_fk1 FOREIGN KEY(col2) REFERENCES tbl2(col1);
  Query OK, 0 rows affected
  

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

  obclient> SELECT TABLE_SCHEMA データベース名,TABLE_NAME テーブル名,COLUMN_NAME 列名,CONSTRAINT_NAME 制約名 FROM information_schema(KEY_COLUMN_USAGE;
  +--------------+--------+--------+----------------------------+
  | データベース名     | テーブル名   | 列名   | 制約名                     |
  +--------------+--------+--------+----------------------------+
  | test         | tbl2   | col1   | PRIMARY                    |
  | test         | tbl3   | col1   | PRIMARY                    |
  | test         | tbl3   | col2   | PRIMARY                    |
  | test         | tbl4   | col2   | tbl4_OBFK_1645173651329873 |
  | test         | tbl5   | col1   | tbl5_OBFK_1645174931376110 |
  | test         | tbl5   | col2   | tbl5_fk1                   |
  +--------------+--------+--------+----------------------------+
  6 rows in set
  
  obclient> ALTER TABLE tbl5 DROP FOREIGN KEY tbl5_OBFK_1645174931376110;
  Query OK, 0 rows affected
  

唯一制約の管理

OceanBaseデータベースは、既存のテーブルに唯一制約を追加することをサポートしています。

まずテーブル tbl7 を作成し、次にテーブル tbl7 の列 col2 に唯一制約を追加する例は以下のとおりです：

obclient> CREATE TABLE tbl7(col1 INT,col2 INT);
Query OK, 0 rows affected

obclient> ALTER TABLE tbl7 ADD UNIQUE(col2);
Query OK, 0 rows affected

NOT NULL制約の管理

OceanBaseデータベースでは、テーブル内の列のプロパティをNOT NULLに変更できます。

まずテーブルtbl9を作成し、次にテーブルの列プロパティをNOT NULLに変更する例は以下のとおりです：

obclient> CREATE TABLE tbl9(c1 int);
Query OK, 0 rows affected

obclient> ALTER TABLE tbl9 MODIFY c1 int NOT NULL;
Query OK, 0 rows affected

CHECK制約の管理

OceanBaseデータベースでは、既存のテーブルにCHECK制約を追加したり、テーブルのCHECK制約を変更または削除したりできます。

例：

• テーブルtbl8の列col2にCHECK制約を追加します。

  obclient> ALTER TABLE tbl8 ADD CONSTRAINT tbl8_c1 CHECK(col2>0);
  

• テーブルのCHECK制約を変更します。

  デフォルトでは、CHECK制約は作成後に有効な状態になります。この制約を無効にしたい場合は、その制約に対して有効化または無効化の操作を実行できます。

  • CHECK制約を無効にする：

    obclient> ALTER TABLE tbl8 ALTER CHECK tbl8_c1 NOT ENFORCED;
    

  • CHECK制約を有効にする：

    obclient> ALTER TABLE tbl8 ALTER CHECK tbl8_c1 ENFORCED;
    

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

  obclient> ALTER TABLE tbl8 DROP CHECK tbl8_c1;
  

制約を作成するための構文と詳細については、ALTER TABLEを参照してください。

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

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

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

obclient> CREATE TABLE t1(
    id bigint NOT NULL PRIMARY KEY
    , gmt_create datetime NOT NULL default current_timestamp
    , gmt_modified datetime NOT NULL default current_timestamp
);
Query OK, 0 rows affected

obclient> INSERT INTO t1(id) VALUES(1),(2),(3);
Query OK, 3 rows affected

obclient> SELECT * FROM t1;
+----+---------------------+---------------------+
| id | gmt_create            | gmt_modified          |
+----+---------------------+---------------------+
|  1 | 2020-02-27 17:09:23 | 2020-02-27 17:09:23 |
|  2 | 2020-02-27 17:09:23 | 2020-02-27 17:09:23 |
|  3 | 2020-02-27 17:09:23 | 2020-02-27 17:09:23 |
+----+---------------------+---------------------+
3 rows in set

関連ドキュメント

外部キー制約の詳細については、外部キー制約を参照してください。