虚谷文档 v1.0.1

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


约束 ​

📄字数 9.0K
👁️阅读量 加载中...

一、关于约束 ​

1.1 定义 ​

约束是一种强制执行数据完整性验证的规则，约束赋予用户对于表中数据的控制权，数据库通过约束来确保数据的准确性和可靠性。

1.2 类型 ​

当前版本下，虚谷数据库支持的约束类型，共分为以下几种：

  • 主键约束(PRIMARY KEY)：将非空约束与唯一值约束组合在一个声明中。主键约束禁止多行在同一列或多个列上具有相同的值，并且不允许值为空。
  • 外键约束(FOREIGN KEY)：要求一个表中的值与另一个表中的值相匹配。
  • 唯一值约束(UNIQUE)：禁止在表中的同一列或多个列上具有相同的值，但允许值为空。
  • 值检查约束(CHECK)：要求写入该列的值符合指定的条件。
  • 非空约束(NOT NULL)：禁止在定义非空约束的字段增加或修改非空值为空值。

二、约束的语法 ​

虚谷数据库提供一系列可选子句，以支持在不同类型的命令中，对约束进行各种类型的定义与管理。此处以表中的约束使用为例，介绍约束语法。

2.1 约束定义语法 ​

虚谷数据库中的约束，主要通过CREATE与ALTER命令进行管理，语法请参阅创建表章节与修改表章节。

虚谷数据库提供两种约束的定义语法：内联方式(inline)与外联方式(out_of_line)。

  • 将约束定义视为列或字段属性的一部分，称为内联方式定义规范。
  • 将约束定义视为整个表定义的一部分，称为外联方式定义规范。
  • 约束定义可以发生在创建表命令与修改表命令中。

注意

非空约束必须使用内联方式定义，其他类别的约束都可以使用内联方式或外联方式定义。其中，支持复合键的约束，只能使用外联方式定义复合键。
复合键指将多个字段的组合视为整体，共同作为约束的条件，详情请参阅支持复合键的具体约束章节。

具体的定义语法规范，请参阅具体类别的约束章节。

2.2 约束管理语法 ​

虚谷数据库中的约束，主要通过ALTER命令进行管理，语法请参阅修改表章节。

2.2.1 追加约束 ​

为表已存在的字段添加新的约束：

  • 示例一
sql
-- 创建表
SQL> CREATE TABLE tab_user (
    user_id INTEGER,
    user_name CHAR(20)
);

-- 追加唯一性约束，使 user_id 唯一
SQL> ALTER TABLE tab_user ADD CONSTRAINT uq_user_id UNIQUE(user_id);

-- 插入数据
SQL> INSERT INTO tab_user VALUES (1, 'Alice');
SQL> INSERT INTO tab_user VALUES (2, 'Bob');

-- 插入重复 user_id 将失败
SQL> INSERT INTO tab_user VALUES (1, 'Charlie');
Error: [E13001] 违反唯一约束
  • 示例二
sql
-- 为已有表添加一个新字段 email，并要求非空
SQL> ALTER TABLE tab_user ADD COLUMN email VARCHAR(100) NOT NULL;

-- 插入一条记录未指定 email，触发非空约束
SQL> INSERT INTO tab_user (user_id, user_name) VALUES (3, 'David');
Error: [E16005] 字段 email 不能取空值

-- 插入正确记录
SQL> INSERT INTO tab_user (user_id, user_name, email) VALUES (3, 'David', 'david@example.com');

2.2.2 更改约束 ​

对已存在的约束进行修改、删除等操作：

  • 示例一
sql
-- 禁用唯一性约束 uq_user_id
SQL> ALTER TABLE tab_user DISABLE CONSTRAINT uq_user_id;

-- 查询约束信息
SQL> SELECT uc.cons_name,uc.cons_type,uc.match_type,uc.define,uc.enable,uc.valid FROM SYS_CONSTRAINTS uc JOIN SYS_TABLES ut ON uc.table_id=ut.table_id WHERE ut.table_name='tab_user';

+------------+-----------+------------+-----------+--------+-------+
| CONS_NAME  | CONS_TYPE | MATCH_TYPE |  DEFINE   | ENABLE | VALID |
+------------+-----------+------------+-----------+--------+-------+
| UQ_USER_ID | U         | <NULL>     | "USER_ID" | F      | T     |
+------------+-----------+------------+-----------+--------+-------+

-- 此时可以插入重复的 user_id
SQL> INSERT INTO tab_user VALUES (1, 'Echo', 'echo@example.com');

-- 再次启用约束
SQL> ALTER TABLE tab_user ENABLE CONSTRAINT uq_user_id;

-- 启用后插入重复记录将再次失败
SQL> INSERT INTO tab_user VALUES (1, 'Frank', 'frank@example.com');
Error: [E13001] 违反唯一约束
  • 示例二
sql
-- 删除名为 uq_user_id 的唯一约束，并保留索引
SQL> ALTER TABLE tab_user DROP CONSTRAINT uq_user_id KEEP INDEX;

-- 或同时删除索引
SQL> ALTER TABLE tab_user DROP CONSTRAINT uq_user_id DROP INDEX;

-- 删除后可自由插入重复值
SQL> INSERT INTO tab_user VALUES (1, 'Grace', 'grace@example.com');

注意

  • 用户必须拥有对相关表的操作权限，才能添加或修改约束。
  • 设置约束命令(SET CONSTRAINT)暂未支持。
  • 查询约束信息详细说明请参见 sys_constraints 章节。

三、约束使用示例 ​

3.1 主键约束 ​

3.1.1 定义 ​

主键约束用于将表中的一个字段或多个字段组合定义为主键，用以唯一标识表中的每条记录。以内联方式定义主键时，只需为字段添加 PRIMARY KEY 关键字；使用外联方式时，需在表级别指定一个或多个字段组成主键。

3.1.2 语法格式 ​

  • 子句与参数说明
子句参数/关键字说明
primary_constraint_stmt_inline
  • PRIMARY KEY
  • 主键定义关键字。
primary_constraint_stmt_out_of_line
  • (column_1[,...n])
  • 单列或多列组合定义主键。

3.1.3 使用示例 ​

  • 示例一

创建一个表 tab_test，并在 id 和 tel 列上定义一个复合主键test_pk。复合主键确保 id 和 tel 列的组合值是唯一的。

sql
-- 创建表
SQL> CREATE TABLE tab_test(id INTEGER ,name CHAR(10), tel INTEGER, CONSTRAINT test_pk PRIMARY KEY (id, tel));

-- 插入值
SQL> INSERT INTO tab_test (id, name, tel) VALUES (1, 'He', 1234567890);

-- 查询表信息
SQL> SELECT * FROM tab_test;
+----+------+------------+
| id | name |    tel     |
+----+------+------------+
| 1  | He   | 1234567890 |
+----+------+------------+

-- 插入一条违反主键约束的记录，返回错误
SQL> INSERT INTO tab_test (id, name, tel) VALUES (1, 'Zhang', 1234567890);

Error: [E13001] 违反唯一值约束

-- 查询该约束的名称，假设有系统库下的DBA权限
SQL> SELECT CONS_NAME FROM sys_constraints WHERE table_id = (SELECT table_id FROM sys_tables WHERE table_name = 'tab_test');
+-----------+
| CONS_NAME |
+-----------+
| test_pk   |
+-----------+
  • 示例二

使用内联方式进行主键约束：

sql
-- 创建表test，对表中的id字段添加主键约束
SQL> CREATE TABLE tab_test(id INT PRIMARY KEY);

-- 向表test中插入一条记录
SQL> INSERT INTO tab_test VALUES(1);

-- 插入相同的记录会返回错误
SQL> INSERT INTO tab_test VALUES(1);

Error: [E13001] 违反唯一值约束

-- 插入空值会返回错误
SQL> INSERT INTO tab_test VALUES(NULL);

Error: [E16005] 字段id不能取空值
  • 示例三

若用户不指定约束名可采用以下语法进行表创建，系统默认为缺省的约束创建一个约束名称。

sql
-- 创建不带约束名的表
SQL> CREATE TABLE tab_test(id INTEGER, name CHAR(10), tel INTEGER, PRIMARY KEY(id, tel));

-- 查询约束定义为id,tel的约束名称
SQL> SELECT CONS_NAME FROM sys_constraints WHERE DEFINE = '"id","tel"';
+-----------------------+
|       CONS_NAME       |
+-----------------------+
| PK_S23960175034057082 |
+-----------------------+

3.2 外键约束 ​

3.2.1 定义 ​

外键约束，用于规定表中指定字段取值只能是依赖其他表的记录中的主键或唯一字段的值，不能为其它值。复合外键将多个字段的组合指定为外键。复合外键只能通过外联方式进行定义。外键约束维护了这两个表之间的引用完整性。

3.2.2 语法格式 ​

  • 子句与参数说明
子句参数/关键字说明
foreign_constraint_stmt_inline
  • FOREIGN KEY
  • ColumnName
  • REFERENCES name_space (opt_column_list)*
  • 关键字，用于定义外键约束。
  • 指定外键的名称。
  • 指定引用的目标表和列。
foreign_constraint_stmt_out_of_line
  • FOREIGN KEY
  • column_list
  • REFERENCES name_space (opt_column_list)*
  • 关键字，用于定义外键约束。
  • 指定引用表的多个列组成复合键。
  • 指定引用的目标表和列。
key_match
  • MATCH FULL
  • MATCH PARTIAL
  • 所有数据必须与引用表列相匹配，除非引用列全为 NULL。
  • 非空数据必须与引用表列相匹配。
key_actions
  • NO ACTION
  • CASCADE
  • SET NULL
  • SET DEFAULT
  • RESTRICT
  • 父表变更使子表不符合约束时，变更操作将被拒绝并回滚。
  • 父表变更或删除时，子表字段值随之变更或删除。
  • 父表变更或删除时，子表字段值置为空值。
  • 父表变更或删除时，子表字段值置为默认值或 NULL。
  • 父表变更时，子表随之变更；父表删除时，子表拒绝删除。
ConstraintAttributeSpec
  • 组合形式
  • 用于声明 ConstraintDeferrabilitySpec 与 ConstraintTimeSpec，支持组合声明，也允许切换次序。
ConstraintDeferrabilitySpec
  • NOT DEFERRABLE
  • DEFERRABLE
  • 约束必须在每条命令执行后立即检查。
  • 约束检查可以推迟到事务结束后进行。
ConstraintTimeSpec
  • INITIALLY IMMEDIATE
  • INITIALLY DEFERRED
  • 每条语句执行后立即进行约束检查。
  • 约束检查推迟到事务结束时执行。

注意

key_match缺省时表示不指定匹配方式。
key_actions缺省时默认为NO ACTION。
ConstraintDeferrabilitySpec缺省时默认为NOT DEFERRABLE。
ConstraintTimeSpec缺省时默认为INITIALLY IMMEDIATE。

3.2.3 使用示例 ​

  • 示例一
sql
-- 创建一张被引用表
SQL> CREATE TABLE test_deferred(ID INT PRIMARY KEY, NAME VARCHAR);

-- 对被引用表test_foreign创建外键约束
SQL> CREATE TABLE test_foreign(ID INT FOREIGN KEY REFERENCES test_deferred(ID));
  • 示例二
sql
-- 创建一张被引用表，使用复合主键
SQL> CREATE TABLE test_deferred(ID INT, NAME VARCHAR, PRIMARY KEY(ID,NAME));

-- 可以通过设置key_action子句来指定反向规则。例如当被引用表字段记录发生更改时，指定引用表中的相关记录置空
SQL> CREATE TABLE test_foreign(ID INT, NAME VARCHAR, CONSTRAINT FOR_1 FOREIGN KEY (ID, NAME) REFERENCES test_deferred(ID,NAME) MATCH FULL ON UPDATE SET NULL);

-- 向被引用表插入数据
SQL> INSERT INTO test_deferred VALUES(1, 'XUGU1');
SQL> INSERT INTO test_deferred VALUES(2, 'XUGU2');
SQL> INSERT INTO test_deferred VALUES(3, 'XUGU3');

-- 此时查询被引用表的记录
SQL> SELECT * FROM test_deferred;
+----+-------+
| ID | NAME  |
+----+-------+
| 1  | XUGU1 |
| 2  | XUGU2 |
| 3  | XUGU3 |
+----+-------+

-- 向引用表中插入记录(1, 'XUGU1')

SQL> INSERT INTO test_foreign VALUES(1, 'XUGU1');

-- 查询被引用表的记录
SQL> SELECT * FROM test_foreign;
+----+-------+
| ID | NAME  |
+----+-------+
| 1  | XUGU1 |
+----+-------+

-- 对被引用表中ID为1的字段进行更新
SQL> UPDATE test_deferred SET ID = 3 WHERE NAME = 'XUGU1';

-- 查询被引用表的记录
SQL> SELECT * FROM test_deferred;
+----+-------+
| ID | NAME  |
+----+-------+
| 3  | XUGU1 |
| 2  | XUGU2 |
| 3  | XUGU3 |
+----+-------+

-- 此时，查询引用表中的相关记录，可以看到已被置空
SQL> SELECT * FROM test_foreign;
+--------+--------+
|   ID   |  NAME  |
+--------+--------+
| <NULL> | <NULL> |
+--------+--------+
  • 示例三

表teacher用于记载学校中所有教师的基本情况，另一张表course用于记载学校所有的课程安排，course中一个字段用于表示某门课程的授课教师。若该库在逻辑上是完整一致的，则course中用于表示授课教师的字段的取值都应在表teacher中出现，否则，说明表course中某记录的表示授课教师的字段的取值错误，或者表teacher并未完全包含所有教师的信息。

为防止此类错误的出现，可以在创建表course时设定外键约束，令表course中的授课教师字段引用表teacher中的教师姓名字段。

sql
-- 创建teacher表，teacher_name作为主键
SQL> CREATE TABLE teacher (
    teacher_name VARCHAR(255) PRIMARY KEY,
    teacher_age INT
);

-- 插入数据
SQL> INSERT INTO teacher (teacher_name, teacher_age) VALUES ('He', 35);
SQL> INSERT INTO teacher (teacher_name, teacher_age) VALUES ('Wang', 40);
SQL> INSERT INTO teacher (teacher_name, teacher_age) VALUES ('Zhang', 38);

-- 创建course表，添加外键约束引用teacher_name字段
SQL> CREATE TABLE course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(255) NOT NULL,
    teacher_name VARCHAR(255),
    CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_name) REFERENCES teacher (teacher_name)
);

--插入数据
SQL> INSERT INTO course (course_id, course_name, teacher_name) VALUES (101, 'Math', 'He');
SQL> INSERT INTO course (course_id, course_name, teacher_name) VALUES (102, 'English', 'Wang');
SQL> INSERT INTO course (course_id, course_name, teacher_name) VALUES (103, 'Chinese', 'Zhang');

-- 插入一条无效的记录，即该teacher_name在teacher表中不存在，返回错误
SQL> INSERT INTO course (course_id, course_name, teacher_name) VALUES (104, 'Art', 'Zhao');
Error: [E13005] 违反外键约束

3.3 值检查约束 ​

3.3.1 定义 ​

值检查约束用于限定一个字段的取值范围，要求这个字段的值必须满足一个布尔表达式。

3.3.2 语法格式 ​

  • 参数说明
参数/关键字说明
  • CHECK
  • expression
  • 关键字，定义检查约束。
  • 布尔表达式，指定列值必须满足的条件。

3.3.3 示例 ​

创建一张名为test_check的表，该表插入数据值要求id>10或者name值为chk，否则数据无法插入。

sql
-- 创建表，使用外联方式对两个字段进行值检查约束
SQL> CREATE TABLE test_check(id INTEGER, name CHAR(10), CHECK(id>10 OR name ='chk'));

-- 同时也可以使用内联方式定义
SQL> CREATE TABLE test_check(id INTEGER CHECK(id>10 OR name ='chk'), name CHAR(10));

-- 插入数据
SQL> INSERT INTO test_check (id, name) VALUES (11, '张三');
SQL> INSERT INTO test_check (id, name) VALUES (5, 'chk');

-- 查询表
SQL> SELECT * FROM test_check;
+----+------+
| ID | NAME |
+----+------+
| 11 | 张三 |
| 5  | chk  |
+----+------+

-- 插入无效数据，返回错误
SQL> INSERT INTO test_check (id, name) VALUES (5, 'cba');
Error: [E13008] 违反值检查约束

-- 当创建一个值检查表达式恒为假时，数据库不会报错
SQL> CREATE TABLE test_check(id INTEGER CHECK(id>10 and id < 9), name CHAR(10));

3.4 非空约束 ​

3.4.1 定义 ​

非空约束禁止表中的某字段包含空值(NULL)。这意味着在向表中插入新记录，或更新现有记录的场景下，必须为定义了非空约束的字段提供一个非空值，否则该操作会被终止，命令将被回滚并返回一个错误。非空约束只有内联定义方式。

3.4.2 语法格式 ​

  • 参数说明
参数/关键字说明
  • NOT NULL
  • 关键字，用于定义非空约束。

3.4.3 使用示例 ​

  • 使用非空约束时，可以创建一个带有非空约束字段的表：
sql
-- 创建表test_notnull并对name字段添加非空约束，约束名为默认值
SQL> CREATE TABLE test_notnull(
         id INTEGER IDENTITY(1,1),
         name CHAR(10) NOT NULL
     );

-- 创建表test_notnull并对id、name字段添加非空约束，约束名为默认值
SQL> CREATE TABLE test_notnull2(
         id INTEGER IDENTITY(1,1) NOT NULL,
         name CHAR(10) NOT NULL
     );

-- 向表中插入一条新记录，name对应字段为NULL
SQL> INSERT INTO test_notnull values(1, NULL);
Error: [E16005] 字段name不能取空值

-- 向表中插入一条新记录，name对应字段为'xugu'
SQL> INSERT INTO test_notnull values(1, 'xugu');

-- 该记录能够进行查询
SQL> SELECT * FROM test_notnull;
+----+------+
| id | name |
+----+------+
| 1  | xugu |
+----+------+

注意

非空约束从本质上看，更接近一个字段的属性而非约束，关于非空约束更多的内容，请参阅修改表章节。

3.5 唯一值约束 ​

3.5.1 定义 ​

唯一值约束（UNIQUE）将表中的字段指定为唯一键。复合唯一键将表中的多个字段组合为唯一键。当以内联方式定义唯一键时，只需指定UNIQUE关键字。外联方式定义唯一键，必须显示引用字段名。复合唯一键只能通过外联方式定义。

3.5.2 语法格式 ​

  • 子句与参数说明
子句参数/关键字说明
unique_constraint_stmt_inline
  • UNIQUE
  • 唯一性定义关键字。
unique_constraint_stmt_out_of_line
  • (column_list)
  • 指定单列或多个列组成唯一性约束。

3.5.3 示例 ​

创建表test_uniq时，创建名为test_uk的唯一值约束，约束键为id与name。

sql
-- 创建表
SQL> CREATE TABLE test_uniq(id INTEGER,name CHAR(10),CONSTRAINT test_uk UNIQUE (id,name));

-- 插入数据
SQL> INSERT INTO test_uniq (id, name) VALUES (1, 'He');
SQL> INSERT INTO test_uniq (id, name) VALUES (2, 'Zhang');
SQL> INSERT INTO test_uniq (id, name) VALUES (1, 'Wang');

-- 查询表
SQL> SELECT * FROM test_uniq;
+----+-------+
| ID | NAME  |
+----+-------+
| 1  | He    |
| 2  | Zhang |
| 1  | Wang  |
+----+-------+

-- 插入一条违反唯一性约束的记录，返回错误
SQL> INSERT INTO test_uniq (id, name) VALUES (1, 'He');
Error: [E13001] 违反唯一值约束

注意

唯一值约束与主键约束相比少了字段非空约束，此时若约束键值某个字段或多个字段值存在NULL，依然允许插入NULL值，因为NULL无法与NULL做比较。所以唯一值约束键需根据实际情况指定为非空。