定义列的约束类型

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

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

约束类型

当前 OceanBase 数据库的 MySQL 模式支持非空约束（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 值。

创建表 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）

外键是一种用于维护表之间关联关系完整性的约束，它指向其他表（父表）的主键列。

• 在创建表并添加外键约束时，需要确保当前用户具备 CREATE 权限以及对父表的 REFERENCES 权限。
• 在为现有表添加外键约束时，需要确保当前用户具备 ALTER 权限以及对父表的 REFERENCES 权限。

更多有关 OceanBase 数据库权限的详细介绍，请参见 MySQL 模式下的权限分类。

创建外键约束时，还需要指定当父表中外键的引用列被执行 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 约束要求数据库中某列的值符合指定的条件。

您可以对单个列定义一个或多个 CHECK 约束，使该列只允许特定的值，也可以定义表级的 CHECK 约束，将一个 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

相关文档

更多关于外键约束的介绍，请参见 外键约束。