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

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

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

制約の種類

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

NULL値を許容しない制約（NOT NULL）

NULL値を許容しない制約は、制約対象の列の値がNULLであることを許可しません。

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

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

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

一意性制約（UNIQUE）

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

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

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

主キー制約（PRIMARY KEY）

主キー制約（PRIMARY KEY）：NOT NULL 制約と一意性制約の組み合わせです。

テーブル 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

関連ドキュメント

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