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

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

约束类型

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

  1. obclient>CREATE TABLE ware (w_id int
  2. , w_ytd decimal(12,2)
  3. , w_tax decimal(4,4)
  4. , w_name varchar(10)
  5. , w_street_1 varchar(20)
  6. , w_street_2 varchar(20)
  7. , w_city varchar(20)
  8. , w_state char(2)
  9. , w_zip char(9)
  10. , unique(w_name, w_city)
  11. , primary key(w_id)
  12. );
  14. Query OK, 0 rows affected (0.09 sec)
  16. obclient>CREATE TABLE cust (c_w_id int NOT NULL
  17. , c_d_id int NOT NULL
  18. , c_id int NOT NULL
  19. , c_discount decimal(4, 4)
  20. , c_credit char(2)
  21. , c_last varchar(16)
  22. , c_first varchar(16)
  23. , c_middle char(2)
  24. , c_balance decimal(12, 2)
  25. , c_ytd_payment decimal(12, 2)
  26. , c_payment_cnt int
  27. , c_credit_lim decimal(12, 2)
  28. , c_street_1 varchar(20)
  29. , c_street_2 varchar(20)
  30. , c_city varchar(20)
  31. , c_state char(2)
  32. , c_zip char(9)
  33. , c_phone char(16)
  34. , c_since date
  35. , c_delivery_cnt int
  36. , c_data varchar(500)
  37. , index icust(c_last, c_d_id, c_w_id, c_first, c_id)
  38. , FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id)
  39. , primary key (c_w_id, c_d_id, c_id)
  40. );
  42. 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 并且必须是不重复的。

    1. obclient>CREATE TABLE cust (c_w_id int NOT NULL,
    2. c_d_id int NOT NULL, c_id int NOT NULL,
    3. c_discount decimal(4, 4),
    4. c_credit char(2),
    5. c_last varchar(16),
    6. c_first varchar(16),
    7. c_middle char(2),
    8. c_balance decimal(12, 2),
    9. c_ytd_payment decimal(12, 2),
    10. c_payment_cnt int,
    11. c_credit_lim decimal(12, 2),
    12. c_street_1 varchar(20),
    13. c_street_2 varchar(20),
    14. c_city varchar(20),
    15. c_state char(2),
    16. c_zip char(9),
    17. c_phone char(16),
    18. c_since date,
    19. c_delivery_cnt int,
    20. c_data varchar(500),
    21. index icust(c_last, c_d_id, c_w_id, c_first, c_id),
    22. FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id),
    23. primary key (c_w_id, c_d_id, c_id)
    24. );
    26. Query OK, 0 rows affected (0.10 sec)

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

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

    OceanBase 数据库默认开启了外键约束检查，外键约束检查开关由租户变量 foreign_key_checks 来控制。

  • CHECK 约束：要求数据库中某列的值符合指定的条件，仅 Oracle 模式支持。

此外，Oracle 模式下，支持对外键约束和 CHECK 约束执行启用或禁用操作。具体语法如下：

  1. obclient> ALTER TABLE table_name MODIFY CONSTRAINT constrain_name ENABLE | DISABLE;

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

说明

目前 OceanBase 数据库不支持通过 ALTER TABLE 语句增加或修改约束，因此您需要在创建表时即确定好表的约束。

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

当列上有 NOT NULL 约束时，通常建议设置为默认值。当列类型是日期或时间类型时，可以设置默认值为数据库当前时间。

  • 示例：为表的时间列设置默认值，可以使用 sysdate 或 systimestamp 函数。
  1. obclient> CREATE TABLE t1(
  2.     id bigint not null primary KEY
  3.     , gmt_create datetime not null default current_timestamp 
  4.     , gmt_modified datetime not null default current_timestamp 
  5. );
  6. Query OK, 0 rows affected (0.07 sec)
  8. obclient> INSERT INTO t1(id) VALUES(1),(2),(3);
  9. Query OK, 3 rows affected (0.01 sec)
  10. Records: 3  Duplicates: 0  Warnings: 0
  12. obclient> SELECT * FROM t1;
  13. +----+---------------------+---------------------+
  14. | id | gmt_create            | gmt_modified          |
  15. +----+---------------------+---------------------+
  16. |  1 | 2020-02-27 17:09:23 | 2020-02-27 17:09:23 |
  17. |  2 | 2020-02-27 17:09:23 | 2020-02-27 17:09:23 |
  18. |  3 | 2020-02-27 17:09:23 | 2020-02-27 17:09:23 |
  19. +----+---------------------+---------------------+
  20. 3 rows in set (0.00 sec)