#
第七章 SQL表之间的关系 要在表之间强制执行引用完整性,可以定义外键。修改包含外键约束的表时,将检查外键约束。 # 定义外键 有几种方法可以在InterSystems SQL中定义外键: - 可以定义两个类之间的关系。定义关系会自动将外键约束投影到SQL。 - 可以在类定义中添加显式外键定义(对于关系未涵盖的情况)。 - 可以使用`CREATE TABLE`或`ALTER TABLE`命令添加外键。可以使用`ALTER TABLE`命令删除外键。 用作外键引用的`RowID`字段必须是公共的。引用隐藏的`RowID`?有关如何使用公用(或专用)`RowID`字段定义表的信息。 **一个表(类)的外键最大数目为400。** ## 外键引用完整性检查 外键约束可以指定更新或删除时的引用操作。 在`CREATE TABLE reference action`子句中描述了使用DDL定义这个引用操作。 在类定义引用的`OnDelete`和`OnUpdate`外键关键字中定义了一个持久化类来定义这个引用操作,该类投射到一个表。 在创建分片表时,这些引用操作必须设置为无操作。 默认情况下,InterSystemsIRIS®数据平台对`INSERT`,`UPDATE`和`DELETE`操作执行外键引用完整性检查。如果该操作将违反参照完整性,则不会执行;该操作将发出`SQLCODE -121,-122,-123或-124`错误。参照完整性检查失败会生成如下错误: ```java 错误#5540:SQLCODE:-124消息:表'HealthLanguage.FKey2'中至少存在1行,该行引用键NewIndex1-外键约束'NewForeignKey1'(字段'Pointer1')的NO ACTION引用操作失败[Execute + 5 ^ IRISSql16:USER] ``` 可以使用`$SYSTEM.SQL.SetFilerRefIntegrity()`方法在系统范围内禁止此检查。若要确定当前设置,请调用`$SYSTEM.SQL.CurrentSettings()`。 默认情况下,当删除带有外键的行时,InterSystems IRIS将在相应的被引用表的行上获取长期(直到事务结束)共享锁。这样可以防止在引用行上的`DELETE`事务完成之前对引用行进行更新或删除。这样可以防止删除引用行,然后回退删除引用行的情况。如果发生这种情况,外键将引用不存在的行。如果使用`NoCheck`定义外键,或者使用`%NOCHECK`或`%NOLOCK`指定引用行的`DELETE`,则不会获取此锁定。 使用持久性类定义定义表时,可以使用`NoCheck`关键字定义外键,以禁止将来对该外键进行检查。` CREATE TABLE`不提供此关键字选项。 可以使用`%NOCHECK`关键字选项禁止检查特定操作。 默认情况下,InterSystems IRIS还对以下操作执行外键引用完整性检查。如果指定的操作违反了引用完整性,则不执行该命令: - `ALTER TABLE DROP COLUMN`。 - `ALTER TABLE DROP CONSTRAINT`删除约束 问题`-317 SQLCODE`。 可以使用`SET`选项`COMPILEMODE=NOCHECK`来抑制外键完整性检查。 - 删除表。问题`-320 SQLCODE`。可以使用`SET`选项`COMPILEMODE = NOCHECK`来抑制外键插入检查。 - 触发器事件,包括事件之前。 例如,如果删除操作因违反外键引用完整性而不能执行,则不会执行`BEFORE DELETE`触发器。 在父/子关系中,没有定义子元素的顺序。 应用程序代码不能依赖于任何特定的顺序。 # 父表和子表 ## 定义父表和子表 在定义投射到表的持久类时,可以使用`relationship`属性指定两个表之间的父/子关系。 下面的例子定义了父表: ```java Class Sample.Invoice Extends %Persistent { Property Buyer As %String(MAXLEN=50) [Required]; Property InvoiceDate As %TimeStamp; Relationship Pchildren AS Sample.LineItem [ Cardinality = children, Inverse = Cparent ]; } ``` 下面的例子定义了一个子表: ```java Class Sample.LineItem Extends %Persistent { Property ProductSKU As %String; Property UnitPrice As %Numeric; Relationship Cparent AS Sample.Invoice [ Cardinality = parent, Inverse = Pchildren ]; } ``` 注意这两句话: - `Relationship Pchildren AS Sample.LineItem [ Cardinality = children, Inverse = Cparent ];` - `Relationship Cparent AS Sample.Invoice [ Cardinality = parent, Inverse = Pchildren ];` 在Management Portal SQL interface Catalog Details选项卡中,表信息提供了子表和/或父表的名称。 如果是子表,则提供对父表的引用,如:`parent->Sample.Invoice`。 子表本身可以是子表的父表。 (子表的子表被称为“孙”表。) 在本例中,表`Info`提供了父表和子表的名称。 ## 向父表和子表插入数据 在将相应的记录插入子表之前,必须将每个记录插入父表。 例如: ```java INSERT INTO Sample.Invoice (Buyer,InvoiceDate) VALUES ('yaoxin',CURRENT_TIMESTAMP) ``` ```java INSERT INTO Sample.LineItem (Cparent,ProductSKU,UnitPrice) VALUES (1,'45-A7',99.95) INSERT INTO Sample.LineItem (Cparent,ProductSKU,UnitPrice) VALUES (1,'22-A1',0.75) ``` ![image](/sites/default/files/inline/images/1_24.png) 尝试插入没有对应父记录ID的子记录时,会使用`%msg`子表`'Sample生成SQLCODE -104错误。 LineItem'`引用父表中不存在的行。 在子表上的插入操作期间,在父表的相应行上获得共享锁。 在插入子表行时,该行被锁定。 然后,锁被释放(直到事务结束时才被持有)。 这确保了在插入操作期间引用的父行不会被更改。 ## 标识父表和子表 在嵌入式SQL中,可以使用主机变量数组来标识父表和子表。 在子表中,主机变量数组的下标0被设置为父引用(`Cparent`),格式为parentref,下标1被设置为子记录ID,格式为`parentref|| childf`。 在父表中,没有定义下标0。 如下面的例子所示: ```java /// d ##class(PHA.TEST.SQL).FatherChildTable() ClassMethod FatherChildTable() { KILL tflds,SQLCODE,C1 &sql(DECLARE C1 CURSOR FOR SELECT *,%TABLENAME INTO :tflds(),:tname FROM Sample.Invoice) &sql(OPEN C1) IF SQLCODE<0 { WRITE "严重的SQL错误:",SQLCODE," ",%msg QUIT } &sql(FETCH C1) IF SQLCODE=100 { WRITE "The ",tname," 表中不包含数据",! QUIT } WHILE $DATA(tflds(0)) { WRITE tname," 是一个子表",!,"parent ref: ",tflds(0)," %ID: ",tflds(1),! &sql(FETCH C1) IF SQLCODE=100 {QUIT} } IF $DATA(tflds(0))=0 { WRITE tname," 是父表",! } &sql(CLOSE C1) IF SQLCODE<0 { WRITE "错误关闭游标:",SQLCODE," ",%msg QUIT } } ``` ```java DHC-APP> d ##class(PHA.TEST.SQL).FatherChildTable() Sample.Invoice 是父表 ``` ```java /// d ##class(PHA.TEST.SQL).FatherChildTable1() ClassMethod FatherChildTable1() { KILL tflds,SQLCODE,C2 &sql(DECLARE C2 CURSOR FOR SELECT *,%TABLENAME INTO :tflds(),:tname FROM Sample.LineItem) &sql(OPEN C2) IF SQLCODE<0 { WRITE "严重的SQL错误::",SQLCODE," ",%msg QUIT } &sql(FETCH C2) IF SQLCODE=100 { WRITE "The ",tname," 表中不包含数据",! QUIT } WHILE $DATA(tflds(0)) { WRITE tname," 是一个子表",!,"parent ref: ",tflds(0)," %ID: ",tflds(1),! &sql(FETCH C2) IF SQLCODE=100 {QUIT} } IF $DATA(tflds(0))=0 { WRITE tname," 是父表",! } &sql(CLOSE C2) IF SQLCODE<0 { WRITE "错误关闭游标::",SQLCODE," ",%msg QUIT } } ``` 对于子表,`tflds(0)`和`tflds(1)`返回如下值: ```java DHC-APP>d ##class(PHA.TEST.SQL).FatherChildTable1() Sample.LineItem 是一个子表 parent ref: 1 %ID: 1||1 Sample.LineItem 是一个子表 parent ref: 1 %ID: 1||2 ``` 对于“孙”表(即子表的子表),`tflds(0)`和`tflds(1)`返回如下值: ```java parent ref: 1||1 %ID: 1||1||1 parent ref: 1||1 %ID: 1||1||7 parent ref: 1||1 %ID: 1||1||8 parent ref: 1||2 %ID: 1||2||2 parent ref: 1||2 %ID: 1||2||3 parent ref: 1||2 %ID: 1||2||4 parent ref: 1||2 %ID: 1||2||5 parent ref: 1||2 %ID: 1||2||6 ```