本文介绍如何使用 OceanBase 数据库中 MySQL 模式中的约束类型。
前提条件
当前用户需要对表具有 CREATE 和 ALTER 权限。
约束类型
为了确保表里的数据符合业务规则，可以在列上定义约束。
约束定义在列上，限制了列里存储的值。当尝试在该列上写入或更新为违反约束定义的值时，会触发一个错误并回滚这个操作；当尝试在已有的表的列上加上一个跟现有数据相冲突的约束时，也会触发一个错误并回滚这个操作。
约束的类型有：
非空约束（NOT NULL），不允许约束包含的列的值包含 NULL。
唯一约束（UNIQUE），不允许约束包含的列的值有重复值，但是可以有多个 NULL 值。
主键约束（PRIMARY KEY），是 NOT NULL 约束和唯一约束的组合。
外键约束（FOREIGN KEY），要求约束的列的值取自于另外一个表的主键列。
CHECK 约束：要求数据库中某列的值符合指定的条件。您可以对单个列定义一个或多个 CHECK 约束，使该列只允许特定的值，也可以定义表级的 CHECK 约束，将一个 CHECK 约束应用于多个列。修改表名时，CHECK 约束名不会被修改。删除某个表时，同时也会删除应用于该表的 CHECK 约束。
默认值约束（DEFAULT）：DEFAULT 约束用于向列中插入默认值；如果没有规定其他的值，那么会将默认值添加到所有的新记录中。
说明
- OceanBase 数据库默认是开启外键约束的，通过租户变量
foreign_key_checks控制。- OceanBase 数据库支持
DEFAULT NULL修改为DEFAULT 'var' NOT NULL。
示例 1
ware表的w_name列类型后面有 not null 约束，表示业务约束每个仓库必须有个名称。obclient> CREATE TABLE ware( id int, w_name varchar(256) not null, PRIMARY KEY (id) ) ; Query OK, 0 rows affected obclient> DESC ware; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | w_name | varchar(256) | NO | | NULL | | +--------+--------------+------+-----+---------+-------+ 2 rows in set有非空约束的列，在 INSERT 语句中必须指明该列的值，除非该列还定义了默认值。
obclient> INSERT INTO ware(id) values(1); ERROR 1364 (HY000): Field 'w_name' doesn't have a default value如
cust表的列c_discount定义了默认值 0.99，即业务上每个人默认折扣是 0.99。obclient> CREATE TABLE cust( id int, c_discount varchar(256) default '0.99', PRIMARY KEY (id) ); Query OK, 0 rows affected obclient> DESC cust; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | c_discount | varchar(256) | YES | | 0.99 | | +------------+--------------+------+-----+---------+-------+ 2 rows in setCHECK 约束示例：创建表 tbl1，并设置列 col1 的值需大于 0。
obclient> CREATE TABLE tbl1(col1 INT CHECK(col1>0),col2 INT); Query OK, 0 rows affected
示例 2
ware表的 (w_name,w_city) 列上有个唯一约束，表示每个城市里仓库的名称必须是不重复的。obclient> CREATE TABLE ware( w_id int, w_name varchar(256) unique, w_city varchar(256) unique, PRIMARY KEY (w_id) ) ; Query OK, 0 rows affected obclient> DESC ware; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | w_id | int(11) | NO | PRI | NULL | | | w_name | varchar(256) | YES | UNI | NULL | | | w_city | varchar(256) | YES | UNI | NULL | | +--------+--------------+------+-----+---------+-------+ 3 rows in set如果
ware表的 (w_name,w_city) 列违反唯一性约束会报错。obclient> SELECT * FROM ware; +------+--------+--------+ | w_id | w_name | w_city | +------+--------+--------+ | 1 | ny | LA | +------+--------+--------+ 1 row in set obclient> INSERT INTO ware VALUES (2,'ny','LA'); ERROR 1062 (23000): Duplicate entry 'ny' for key 'w_name'
示例 3
如 ware 表都有个主键 w_id，该列不允许为 NULL 并且必须是不重复的。
主键为空报错。
obclient> INSERT INTO ware(w_name,w_city) VALUES ('ny', 'LA'); ERROR 1364 (HY000): Field 'w_id' doesn't have a default value主键重复报错
obclient> SELECT * FROM ware; +------+--------+--------+ | w_id | w_name | w_city | +------+--------+--------+ | 1 | ny | LA | +------+--------+--------+ 1 row in set obclient> INSERT INTO ware VALUES (1,'zs','EH'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'说明
MySQL 模式下默认主键具有非空约束。
示例 4
cust 表的 c_w_id 上有个外键约束引用了 ware 表的 w_id 列，表示业务上顾客归属的仓库必须是属于仓库表里的仓库。
obclient> ALTER TABLE cust ADD CONSTRAINT ware FOREIGN KEY (c_w_id) REFERENCES ware(w_id);
Query OK, 0 rows affected
关于时间列的默认时间设置
当列上有 NOT NULL 约束时，通常建议设置默认值。当列类型是日期或时间类型时，可以设置默认值为数据库当前时间。
示例
为表的时间列设置默认值，可以使用 current_timestamp 函数。
创建
t1表，并定义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在
t1表中插入数据。obclient> insert into t1(id) values(1),(2),(3); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0查看
t1表。obclient> select * from t1; +----+---------------------+---------------------+ | id | gmt_create | gmt_modified | +----+---------------------+---------------------+ | 1 | 2022-03-16 14:23:10 | 2022-03-16 14:23:10 | | 2 | 2022-03-16 14:23:10 | 2022-03-16 14:23:10 | | 3 | 2022-03-16 14:23:10 | 2022-03-16 14:23:10 | +----+---------------------+---------------------+ 3 rows in set
