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

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

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

制約の種類

OceanBaseデータベースのOracleモードは、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 値は存在可能です。

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

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

PRIMARY KEY制約 (PRIMARY KEY)

PRIMARY KEY制約は、NOT NULL制約とUNIQUE制約の組み合わせです。

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

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

この例では、col1 列の値は NULL にすることができず、かつ一意である必要があります。

FOREIGN KEY制約 (FOREIGN KEY)

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

FOREIGN KEY制約を作成する際には、親テーブルでFOREIGN KEYの参照列に対して DELETE 操作を実行したときの、子テーブルの対応する行のFOREIGN KEY列のカスケード操作も指定できます：

• CASCADE：親テーブルのFOREIGN KEYの参照列が削除されたとき、子テーブルの対応する行のFOREIGN KEY列を自動的に削除します。

• SET NULL：親テーブルのFOREIGN KEYの参照列が削除されたとき、子テーブルの対応する行のFOREIGN KEY列の値を NULL に設定します。

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

FOREIGN KEYを作成する際に ON DELETE 句を指定しなかった場合、親テーブルでFOREIGN KEYの参照列が削除されると、FOREIGN KEY制約違反により操作は拒否されます。

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

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

この例では、親テーブル tbl3 の列 col1 に対して削除操作を実行すると、システムは子テーブルの対応する行のFOREIGN KEY列の値を 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');
  

NOT 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