定义列的约束类型 | OceanBase 文档中心

定义列的约束类型

更新时间：2023-10-18 14:40:34

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

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

约束类型

下面以 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 和唯一约束 UNIQUE 的组合，不允许为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 变量的说明，请参见 foreign_key_checks。

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

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

当列上有 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 (0.07 sec)

obclient> INSERT INTO t1(id) VALUES(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

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 (0.00 sec)