定义列的约束类型

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

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

约束类型

下面以 ware 表和 cust 表为例，介绍 OceanBase 数据库的约束类型。

obclient>CREATE TABLE ware (w_id int, 
w_ytd decimal(12,2), 
w_tax decimal(4,4), 
w_name varchar(10), 
w_street_1 varchar(20), 
w_street_2 varchar(20), 
w_city varchar(20), 
w_state char(2), 
w_zip char(9), 
unique(w_name, w_city), 
primary key(w_id)
);
Query OK, 0 rows affected (0.09 sec)

obclient>CREATE TABLE cust (c_w_id int NOT NULL, 
c_d_id int NOT NULL, 
c_id int NOT NULL, 
c_discount decimal(4, 4), 
c_credit char(2), 
c_last varchar(16), 
c_first varchar(16), 
c_middle char(2), 
c_balance decimal(12, 2), 
c_ytd_payment decimal(12, 2), 
c_payment_cnt int, 
c_credit_lim decimal(12, 2), 
c_street_1 varchar(20), 
c_street_2 varchar(20), 
c_city varchar(20), 
c_state char(2), 
c_zip char(9), 
c_phone char(16), 
c_since date, 
c_delivery_cnt int, 
c_data varchar(500), 
index icust(c_last, c_d_id, c_w_id, c_first, c_id), 
FOREIGN KEY (c_w_id) REFERENCES ware(w_id), 
primary key (c_w_id, c_d_id, c_id)
);
Query OK, 0 rows affected (0.10 sec)

• 非空约束（NOT NULL）：不允许约束包含的列的值为 NULL。

  例如，ware 表的 w_name 列类型后面有 NOT NULL 约束，表示业务约束每个仓库必须有个名称。

  有非空约束的列，在 INSERT 语句中必须指明该列的值，除非该列还定义了默认值。例如，cust 表的列 c_discount 定义了默认值 0.99，即业务上每个人默认折扣是 0.99。

• 唯一约束（UNIQUE）：不允许约束包含的列的值有重复值，但是可以有多个 NULL 值。

  例如，ware 表的 (w_name, w_city) 列上有个唯一约束，表示每个城市里仓库的名称必须是不重复的。

• 主键约束（PRIMARY KEY）： NOT NULL 约束和唯一约束的组合。

  例如，ware 表和 cust 表都有个主键 w_id 和 c_id，这两列不允许为 NULL 并且必须是不重复的。

  示例如下：

  obclient>CREATE TABLE cust (c_w_id int NOT NULL,
  c_d_id int NOT NULL, 
  c_id int NOT NULL,
  c_discount decimal(4, 4),
  c_credit char(2),
  c_last varchar(16),
  c_first varchar(16),
  c_middle char(2),
  c_balance decimal(12, 2),
  c_ytd_payment decimal(12, 2),
  c_payment_cnt int,
  c_credit_lim decimal(12, 2),
  c_street_1 varchar(20),
  c_street_2 varchar(20),
  c_city varchar(20),
  c_state char(2),
  c_zip char(9),
  c_phone char(16),
  c_since date,
  c_delivery_cnt int,
  c_data varchar(500),
  index icust(c_last, c_d_id, c_w_id, c_first, c_id),
  FOREIGN KEY (c_w_id) REFERENCES ware(w_id),
  primary key (c_w_id, c_d_id, c_id)
  );
  
  Query OK, 0 rows affected (0.10 sec)
  

• 外键约束（FOREIGN KEY）：要求约束的列的值取自于另外一个表的主键列。

  例如，cust 表的 c_w_id上有个外键约束引用了 ware 表的 w_id 列，表示业务上顾客归属的仓库必须是属于仓库表里的仓库。

  OceanBase 数据库默认开启了外键约束检查，外键约束检查开关由租户变量 foreign_key_checks 来控制。更多 foreign_key_checks 变量的说明，请参见《参考指南（Oracle 模式）》。

• CHECK 约束：要求数据库中某列的值符合指定的条件。

默认情况下，约束创建后为启用状态。

管理约束

建表成功后，还支持以下操作：

• 支持对外键约束和 CHECK 约束执行启用和禁用操作。

  具体语法如下：

  obclient> ALTER TABLE table_name ENABLE | DISABLE CONSTRAINT constrain_name ;
  

• 支持对表的列添加 CHECK 约束。

  示例如下：

  obclient> ALTER TABLE ware ADD CONSTRAINT cst CHECK(w_city='hz');
  

• 支持修改表的列属性，对表的列添加非空约束（NOT NULL）。

  语法如下：

  obclient> ALTER TABLE table_name MODIFY c1 NOT NULL;
  

• 支持删除表的 NOT NULL 约束。

  语法如下：

  obclient> ALTER TABLE table_name DROP CONSTRAINT constraint_name;
  

  或者，您也可以使用以下语法：

  obclient> ALTER TABLE table_name MODIFY c1 NULL;
  

关于时间列的默认时间设置

当列上有 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