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

テーブル内のデータがビジネスルールに準拠するように、列に制約を定義できます。

制約は列に定義され、列に格納される値を制限します。制約定義に違反する値をその列に書き込もうとしたり、更新しようとしたりすると、エラーが発生してその操作がロールバックされます。また、既存のテーブルの列に、既存のデータと矛盾する制約を追加しようとすると、同様にエラーが発生してその操作がロールバックされます。

制約の種類

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

NOT NULL制約

NOT NULL制約は、制約された列の値がNULLであることを許可しません。

NOT NULL制約がある列については、その列にNULLではないデフォルト値が定義されている場合を除き、INSERTステートメントでその列の値を指定する必要があります。

テーブル tbl1 を作成し、列 col1 にNOT NULL制約を設定する例を以下に示します：

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

UNIQUE制約

UNIQUE制約は、制約された列の値に重複があることを許可しませんが、複数のNULL値は存在可能です。

テーブル tbl6 を作成し、列 col1 にUNIQUE制約を設定する例を以下に示します：

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

PRIMARY KEY制約

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

テーブル tbl2 を作成し、列 col1 を主キーに設定する例を以下に示します：

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

この例では、テーブル tbl2 に主キー列 col1 があり、この列はNULLを許容せず、かつ一意でなければなりません。

FOREIGN KEY制約 (FOREIGN KEY)

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

関連ドキュメント

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