南大通用GBase8s 常用SQL语句(七十三)
被引用列的缺省值
如果被引用表与引用表不同,则您不需要知道被引用列;缺省列为被引用表的主键列(或列组)。如果被引用表与引用表一样,则必须指定被引用列。
表内的引用关系
可以在同一表的两列之间建立引用关系。在以下示例中, employee 表中的 emp_num 列通过雇员编号唯一地标识了每个雇员。该表中的 mgr_num 列包含管理该雇员的经理的编号。在该示例中,mgr_num 将引用 emp_num 。在 mgr_num 列中出现重复的值,这是因为经理可以管理多个雇员。
CREATE TABLE employee
(
emp_num INTEGER PRIMARY KEY,
mgr_num INTEGER REFERENCES employee (emp_num)
);
其中行之间存在引用关系的表可以具有没有显式外键的 PRIMARY KEY 约束。有关递归查询其中行存在逻辑层次结构的多个级别的表的语法。请参阅 层级查询子句 。
创建引用约束时的锁定问题
当您创建引用约束时,将在被引用的表上放置互斥锁。当 CREATE TABLE 语句完成时,该锁才被释放。如果在支持事务记录的数据库中创建表并使用事务,则直到事务结束时锁才被释放。
使用单列约束格式的示例
这些示例指示了单列约束格式选项来定义缺省启用的外键约束,并声明禁用的引用约束的名称。
缺省启用的引用约束
以下示例使用单列约束格式定义 sub_accounts 和 accounts 表之间的引用关系。(术语外键约束和引用约束是同义词)。sub_accounts 表的 ref_num 列(外键)引用了 accounts 表中的 acc_num 列(外键)。
CREATE TABLE accounts (
acc_num INTEGER PRIMARY KEY,
acc_type INTEGER,
acc_descr CHAR(20));
CREATE TABLE sub_accounts (
sub_acc INTEGER PRIMARY KEY,
ref_num INTEGER REFERENCES accounts (acc_num),
sub_descr CHAR(20));
以上定义 sub_accounts 表的 CREATE TABLE 语句的单列格式约束语法没有显式指定 ref_num 列是外键,但是REFERENCES 关键字指定 ref_num 必须具有与 accounts 表中 acc_num 列的一些行的值相同的值。这意味着在 sub_accounts 是引用表,accounts 是被引用表的引用关系中,ref_num 列是外键。
在单列约束格式中,您不能显式指定 ref_num 列为外键。当在引用表的单列(或引用同一主键的列列表)上放置引用约束时,要包含 FOREIGN KEY 关键字,您必须代替使用多列约束格式语法来定义引用约束。
缺省情况下,sub_accounts 表上的约束不用过滤而启用,因为没有指定显式约束方式。可以使用 DISABLED 或 FILTERING 关键字在此示例中指定。SET Database Object Mode 语句的 SET CONSTRAINTS 选项可以重置现有约束的对象方式。
因为以上的 sub_accounts 示例没有为引用约束声明名称,所以数据库服务器在将此约束注册到 sysconstraints 系统目录表中时隐式生成标识符,并将它的方式( E )注册到 sysobjstate 系统目录表中。
禁用的引用约束
以下 CREATE TABLE 引用创建了 xeno_counts 表,并在它的 xeno_num 列和第一个示例中 accounts 表的 acc_num 列之间定义了引用约束。此单列约束格式语法还包含了约束定义,指定 DISABLED 作为它的约束方式,且声明 xeno_constr 作为外键约束的名称。此处 xeno_accounts 是引用表,accounts 是被引用表。
CREATE TABLE xeno_counts (
xeno_acc INTEGER PRIMARY KEY,
xeno_num INTEGER REFERENCES accounts (acc_num)
CONSTRAINT xeno_constr DISABLED,
xeno_descr CHAR(20));
在 DISABLED 方式,当 DML 操作在 xeno_counts 表中产生违例行时,不会执行 xeno_constr 约束。然而,要实现参照完整性,可以使 SET Database Object Mode 语句的 SET CONSTRAINTS 选项将约束的方式更改为 ENABLED。或者,START VIOLATION 语句将违例表与 xeno_counts 表关联后, SET CONSTRAINTS 可将 xeno_constr 约束重设为 FILTERING 方式。
使用 ON DELETE CASCADE 选项
使用 ON DELETE CASCADE 选项来指定从父表中删除某行时,是否需要从子表中删除相应的行。如果您不指定级联删除,则数据库服务器的缺省行为将阻止您删除表中的数据(如果有其它表引用它)。
如果指定此选项,则稍后当您删除父表中的行时,数据库服务器还删除任何与子表中的行(外键)相关的行。级联删除功能最主要的好处是,需要执行删除操作时它可以减少 SQL 语句的数量。
例如,all_candy 表中包含的 candy_num 列是主键。hard_candy 表将 candy_num 列作为外键引用。以下的 CREATE TABLE 语句将创建 hard_candy 表,其外键上具有级联删除选项:
CREATE TABLE all_candy
(candy_num SERIAL PRIMARY KEY,
candy_maker CHAR(25));
CREATE TABLE hard_candy
(candy_num INT,
candy_flavor CHAR(20),
FOREIGN KEY (candy_num) REFERENCES all_candy
ON DELETE CASCADE);
由于为子表指定了 ON DELETE CASCADE ,因此当从 all_candy 表中删除某行时,hard_candy 表中的相应行也将被删除。有关从具有级联删除的表中删除行时的语法限制和锁定影响的信息,请参阅级联删除表时的注意事项。