引用和实体完整性

        本文将详细讲解在数据库服务器级具有引用和实体完整性的好处，包括如何在字段中设置默认值、检查约束和引用约束，以及在何时发生约束检查。通过遵循这些指导原则，可以确保数据的一致性和准确性。

1.完整性

  完整性指数据库中数据的准确性或正确性。

• 引用完整性
  引用完整性强制执行表之间的主键和外键关系。例如，客户记录必须存在，才能为该客户下订单。

• 实体完整性
  实体完整性通过创建主键来唯一标识表中的每一行数据。

• 语义完整性
  语义完整性通过使用以下内容来实现：

  — 数据类型：数据类型定义您可以存储在列中的值的类型。例如，数据类型smallint允许您输入从 -32767 到 32767 的值。

  — 默认值：默认值是在未指定显示值时插入列中的值。例如，操作时间字段，可能默认为当前时间。

  — 检查约束：检查约束指定在列中插入或更新的数据的条件。插入表中的每一行都必须符合这些条件。例如，表的 quantity 列可能会检查大于或等于1的数量。检查约束也可用于表中的强制关系。例如，在 order 表中，ship_date必须大于order_date。

  — NOT NULL 约束：NOT NULL 约束确保在插入和更新操作过程中列包含一个值。

1.1 应用级的完整性

1693191456720890×467 12.8 KB

  可以在应用程序中执行完整性检查。例如，要验证状态值，您可以使用表中的state 属性指定有效状态，或者您可以使用SinoDB 4GL 的SELECT 语句来检查表中的值。虽然第二种方法比较灵活，但是可能导致检查不一致。此外，如果约束值发生变化，则可能需要修改所有受影响的应用程序代码。上图的示例中，form1与form2与SinoDB 4GL程序不一致。

1.2 数据库服务级的完整性

1693191786449888×467 12.1 KB

  通过在数据库服务器级别进行完整性约束检查，可以确保所有应用程序的一致性。

2. 完整性约束的类型

  以下列出了在 SinoDB 中实现的引用、实体和语义完整性（数据类型除外）的类型。CHECK、UNIQUE 和 NOT NULL 约束在单个行内应用完整性检查，而引用约束则在行之间应用完整性检查。

• 默认值：自动为 INSERT 语句中忽略的列提供值。
• NOT NULL 约束：在插入（如果没有默认值）或更新期间需要为列提供值。
• 检查约束：所有插入和更新的行都必须满足此约束定义的条件。
• 引用约束：执行主键和外键关系。
• 唯一约束：插入或更新的每一行都必须具有键指定的唯一值。

unique、not null 和 default 约束示例：

CREATE TABLE orders(
 order_num INTEGER UNIQUE ,
 order_date DATE NOT NULL DEFAULT TODAY );

ALTER TABLE orders
MODIFY order_num INTEGER NOT NULL ;

2.1 约束名称

CREATE TABLE orders(
 order_num INTEGER UNIQUE
  CONSTRAINT order_num_uq,
 order_date DATE NOT NULL
  CONSTRAINT order_date_nn
 DEFAULT TODAY);

ALTER TABLE orders
 MODIFY order_num INTEGER NOT NULL
  CONSTRAINT order_num_nn;

  约束由其名称标识。您可以为其命名或者使用数据库服务器分配的默认名称。但是这些名称在数据库中必须是唯一的。

  系统默认名称是约束 ID 代码，表 ID 和唯一约束 ID的组合。使用命名约定命名约束，而不是使用系统默认值。这样更容易识别约束。

  需要为所有的约束分配名称：primary-key、foreign-key、unique、check和NOT NULL。

  约束名称存储在sysconstraints系统目录表中。 如果要更改特定约束的模式（例如，启用，禁用或过滤），就需要知道名称：

----禁用约束pk_items, fk1_items
SET CONSTRAINTS pk_items, fk1_items TO DISABLED

  也可以为整个表设置约束，而不用知道该约束名称。例如：

SET CONSTRAINTS FOR TABLE orders TO DISABLED;

  要删除约束而不以任何其他方式更改表，请使用 ALTER TABLE 命令的 DROP 子句：

ALTER TABLE items DROP CONSTRAINT pk_items;

2.2 CHECK约束

• 添加表级约束

ALTER TABLE items ADD CONSTRAINT
 CHECK (quantity >= 1 AND quantity <= 10)
 CONSTRAINT ck_items_qty;

• 在列级别添加等效约束

ALTER TABLE items MODIFY quantity SMALLINT
 CHECK (quantity >= 1 AND quantity <= 10)
 CONSTRAINT ck_items_qty;

• 如果对列添加约束，那么不能引用其他列。

ALTER TABLE orders MODIFY paid_date DATE
CHECK (paid_date > ship_date) CONSTRAINT ck_paid_date;

#676: Invalid check constraint column.

• 在表级别添加引用多个列的约束，这些列必须来自同一个表。

ALTER TABLE orders ADD CONSTRAINT
CHECK (paid_date > ship_date) CONSTRAINT ck_paid_date;

  注：当修改列时，请修改关于该列的所有内容，这就是 MODIFY 子句必须包含数据类型的原因。 如果不使用 MODIFY 子句列出所有约束，那么会删除未列出的任何约束。

2.3 引用约束

  引用约束允许用户指定主键和外键来实现父表与子表（主表与从表）的关系。要定义引用约束，用户必须具有REFERENCES 权限或作为表所有者。

  假定在插入、更新或删除每一行时执行检查引用约束，使用引用约束有以下限制：

• 如果用户删除 了 PRIMARY KEY ，但是存在相应的 FOREIGN KEYS，则删除失败。请使用级联删除来规避此限制。

• 如果用户更新 了 PRIMARY KEY ，但是存在与 PRIMARY KEY 的原始值相对应的 FOREIGN KEYS，则更新失败。

• 没有任何与删除 FOREIGN 键相关的限制。

• 如果用户更新 了 FOREIGN KEY，并且不存在与 FOREIGN KEYS 的新的非NULL值相对应的PRIMARY KEY，则更新失败。

• PRIMARY KEY 中的所有值都必须是唯一的。

• 当用户将一行插入 子表中时，如果所有 FOREIGN KEYS 都为非 NULL，并且不存在相应的 PRIMARY KEY，则插入失败

要完全执行引用完整性，请勿在主键和外键列中允许NULL值。

2.4 引用约束的类型

• 循环引用约束强制执行表之间的父与子关系。
• 自引用约束在表内强制执行父与子关系。
• 多路径约束是指可以有多个外键的主键

2.4.1 循环引用约束

  循环引用约束强制执行表之间 的父表与子表（主表与从表）关系。要强制执行引用约束，您必须在父表中指定主键，并在子表中指定相应的外键。每个表只能有一个主键。REFERENCES 子句指定父表。因为该表中只允许有一个主键，所以您无需在 references子句中列出列名。

循环引用约束：示例

CREATE TABLE customer(
 customer_num SERIAL,
 fname CHAR(20),
PRIMARY KEY (customer_num) CONSTRAINT pk_cnum
);

CREATE TABLE orders(
 order_num SERIAL,
 customer_num INTEGER,
FOREIGN KEY (customer_num)
REFERENCES customer CONSTRAINT fk_cnum
);

INSERT INTO customer VALUES (1, “Smith”);
INSERT INTO orders VALUES (0, 1);
INSERT INTO orders VALUES (0, 2);
#
#691: Missing key in referenced table for
#referential constraint (karen.fk_cnum).
#111:ISAM error: no record found.

DELETE FROM customer WHERE customer_num = 1;
# ^
#692:Key value for constraint (karen.pk_cnum)
#is still being referenced.

  在上述示例中，由于客户编号2不存在于 customer 表中，因此订单不能添加到客户编号2的 orders 表中。

  客户编号1无法从 customer 表中删除，因为订单位于客户编号1的 orders 表中。如果客户记录丢失，您要谁为订单付款？

级联删除

CREATE TABLE customer (customer_num INT, PRIMARY
KEY(customer_num));
CREATE TABLE orders (order_num INT, customer_num INT,
PRIMARY KEY(order_num),
FOREIGN KEY(customer_num) REFERENCES customer
ON DELETE CASCADE);

$ DELETE FROM customer WHERE customer_num = 101;
/* all rows in orders table for customer 101 are
automatically deleted */

  级联删除 可让您定义引用约束，在引用约束中当相应的父行被删除时，数据库服务器会自动删除子行。此功能在简化应用程序代码和逻辑方面非常有用。

  级联删除提供了性能增强，因为通过自动删除数据库服务器中的行而不是要求应用程序先删除子行，处理的 SQL 语句更少。数据库服务器可以更有效地处理删除，因为不会产生 SQL 语句的开销。

  如果由于任何原因，原始 DELETE 语句失败或子行上产生的 DELETE 语句失败，那么会回滚整个 DELETE 语句。

  要调用级联删除，请在子表的 CREATE TABLE 语句中的 REFERENCES 子句之后添加 ON DELETE CASCADE 子句。

  使用级联删除的限制：

• 数据库必须具有日志记录。
• 子表不能涉及父表中涉及DELETE语句的相关子查询。

  无日志数据库可以创建级联删除，但是，级联删除未激活。如果关闭数据库日志，则级联删除将被禁用（您将收到引用完整性错误）。一旦开启数据库日志，级联删除将自动重新激活; 管理员无需采取任何行动。

  在父表的DELETE语句中使用子表的相关子查询不会使用级联删除。相反，您会收到以下错误：

735: Cannot reference table that participates in a cascaded delete.

  添加级联删除
  如果列具有外键约束，并且您要添加级联删除，那么请删除该约束并使用ON DELETE CASCADE子句重新添加：

ALTER TABLE orders DROP CONSTRAINT orders_fk1,
 ADD CONSTRAINT (FOREIGN KEY (customer_num)
  REFERENCES customer
  ON DELETE CASCADE
  CONSTRAINT orders_fk1);

  可以删除该约束并使用相同的ALTER TABLE语句重新添加。当您在同一ALTER TABLE语句中执行这两个操作时，不会删除索引，因此这样操作开销是最小的。

2.4.2 自引用约束

  自引用约束强制执行表中的父表与子表（主表与从表）关系。

CREATE TABLE emp(
 enum SERIAL,
 mnum INTEGER,
 PRIMARY KEY (enum) CONSTRAINT pk_enum,
 FOREIGN KEY (mnum) REFERENCES emp
 CONSTRAINT fk_enum);

INSERT INTO emp VALUES (1, 1);
INSERT INTO emp VALUES (2, 1);
INSERT INTO emp VALUES (3, 10);

#691: Missing key in referenced table for
#referential constraint (karen.fk_enum).
#111: ISAM error: no record found

  此示例假定使用员工表来跟踪所有员工及其分配的管理员。使用自引用约束来确保分配给每个员工的管理员存在于员工表中。也就是说，不能有一个不是员工的管理员。eum（员工编号）是存储在mnum列（管理员编号）中的一组值必须存在的主键。在该示例中，emp表要求在eum（员工编号）列中输入的值必须存在，然后才能添加到mnum（管理员编号）列。允许管理员编号为1，但管理员编号为10会失败。

2.4.3 多路引用约束

  多路径引用约束是指可以有多个外键的主键。

CREATE TABLE stock
 (stock_num SMALLINT, manu_code CHAR(3),…,
PRIMARY KEY (stock_num, manu_code) CONSTRAINT pk_stock);

CREATE TABLE items (
 item_num SMALLINT,
 stock_num SMALLINT,
 manu_code CHAR(3),…,
 FOREIGN KEY (stock_num, manu_code)
 REFERENCES stock
 CONSTRAINT fk1_stock);

CREATE TABLE catalog(
 catalog_num SERIAL,
 stock_num SMALLINT,
 manu_code CHAR(3),…,
 FOREIGN KEY (stock_num,manu_code)
 REFERENCES stock
 CONSTRAINT fk2_stock);

  以上示例显示一个父表 stock 有两个子表（items 表和 catalog 表）。为了清楚起见，其他列被排除在示例之外。

2.5 创建主键约束

创建具有主键约束的表的两种方法：

• 在列的列表末尾

CREATE TABLE customer(
  customer_num SERIAL,
  fname CHAR(20),
PRIMARY KEY(customer_num)
 CONSTRAINT pk_cnum);

• 在列定义的末尾。

CREATE TABLE customer(
  customer_num SERIAL
PRIMARY KEY CONSTRAINT pk_cnum,
 fname CHAR(20));

  可以通过两种方式在CREATE TABLE语句中添加引用约束。上述方法完成了同样的事情。但是，只能使用第一种方法来创建复合主键。

2.6 创建外键约束

创建具有外键约束的表的两种方法：

• 在列的列表末尾

CREATE TABLE orders(
  order_num SERIAL,
  customer_num INTEGER,
FOREIGN KEY(customer_num)
 REFERENCES customer CONSTRAINT fk_cnum);

• 在列定义的末尾

CREATE TABLE orders(
  order_num SERIAL,
  customer_num INTEGER
   REFERENCES customer CONSTRAINT fk_cnum);

  可以通过两种方式在CREATE TABLE语句中添加引用约束。上述方法完成了同样的事情。但是，只能使用第一种方法来创建复合外键。

2.7 添加主键约束

将主键约束添加到现有表的两种方法:

• 向表添加约束

ALTER TABLE customer ADD CONSTRAINT
 PRIMARY KEY(customer_num)
 CONSTRAINT pk_cnum;

• 修改列定义

ALTER TABLE customer
 MODIFY customer_num SERIAL
 PRIMARY KEY CONSTRAINT pk_cnum;

  可以通过两种方式在ALTER TABLE语句中添加引用约束。上述方法完成了同样的事情。但是，只有第一种方法可以创建复合主键（多个列）。此外，第二种方法修改了有关列的所有内容，所以必须小心包含所有约束，而该列所有未列出的约束都将被删除。

2.8 添加外键约束

将外键约束添加到现有表的两种方法:

• 向表添加约束

ALTER TABLE orders
 ADD CONSTRAINT
 FOREIGN KEY(customer_num)
  REFERENCES customer CONSTRAINT fk_cnum;

• 修改列定义

ALTER TABLE orders
 MODIFY customer_num INTEGER
  REFERENCES customer CONSTRAINT fk_cnum;

  在ALTER TABLE语句中添加引用约束有两种方法。上述方法完成了同样的事情。但是，只能使用第一种方法来创建复合外键（多个列）。此外，第二种方法修改了列的所有内容，所以您必须小心包含所有约束，而该列所有未列出的约束都将被删除。

3. 系统目录表

Q_O(8CRXD52LNG383UYTYH886×210 27.6 KB

以下系统目录表可用于强制执行引用和实体完整性：

syschecks	包含check约束的文本。
sysdefaults	跟踪每个具有用户指定的默认值的列。
sysreferences	列出对数据库中的列设置的referential约束。
sysconstraints	存储primary、check和referential 约束以及unique约束
syscoldepend	跟踪每个check约束中指定的表列。

更多信息内容请移步星瑞格官方社区，期待大家加入
Sinoregal Tech Forum