定义列的约束类型

为了确保表里的数据符合业务规则，您可以在列上定义约束。

约束定义在列上，可以限制列里存储的值。当尝试在该列上写入或更新为违反约束定义的值时，会触发一个错误并回滚这个操作；当尝试在已有的表的列上加上一个跟现有数据相冲突的约束时，也会触发一个错误并回滚这个操作。

约束类型

OceanBase 数据库的 Oracle 模式支持支持非空约束（NOT NULL）、唯一约束（UNIQUE）、主键约束（PRIMARY KEY）、外键约束 （FOREIGN KEY）以及 CHECK 约束等约束类型。

非空约束（NOT NULL）

非空约束不允许约束包含的列的值为 NULL。有非空约束的列，在 INSERT 语句中必须指明该列的值，除非该列还定义了默认值。

例如，创建表 tbl1，并设置列 col1 为非空约束的示例如下：

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');
  

管理非空约束

创建表后，支持对已创建的表添加或删除 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