SQL 查询演示表中参照完整性中的添加异常

简介

为了演示表中参照完整性中的添加异常，我们可以创建一个简单的数据库，其中包含两个表：父表和子表。父表应具有主键列，子表应具有外键列，该列引用父表中的主键列。然后，我们可以向这两个表中插入一些行，并在子表上运行一个 SELECT 语句，该语句根据父表中的列过滤结果。如果我们从父表中删除一行，则 SELECT 语句的结果可能不再准确，因为它将不包括与父表中已删除行关联的子表中的任何行。这是一个添加异常的示例。

定义

演示表中参照完整性中的添加异常的 SQL 查询是一个用于创建具有父表和子表的数据，并以演示添加异常的方式在这些表中插入和删除行。

表中参照完整性中的添加异常发生在父表上的删除操作导致子表上 SELECT 语句的结果不正确时。当子表具有引用父表的外键约束，并且 SELECT 语句包含根据父表中的列过滤子表的 WHERE 子句时，可能会发生这种情况。

示例 1

这是一个演示“orders”表中参照完整性中的添加异常的 SQL 查询示例 -

SQL 查询

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (1, 1, 1, 2);

INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (2, 1, 2, 3);

INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (3, 2, 3, 4);

SELECT SUM(quantity)
FROM orders
WHERE customer_id = 1;

在此示例中，我们有一个名为“orders”的表，用于存储客户下的订单信息。该表在外键列“customer_id”上具有外键约束，该约束引用“customers”表中的“customer_id”列。“orders”表还在外键列“product_id”上具有外键约束，该约束引用“products”表中的“product_id”列。

我们向“orders”表中插入三行，每行代表客户下的一个订单。第一个订单由客户 1 下达，包含 2 个产品 1。第二个订单也由客户 1 下达，包含 3 个产品 2。第三个订单由客户 2 下达，包含 4 个产品 3。

最后，我们运行一个 SELECT 语句以获取客户 1 下达的所有订单数量的总和。查询的结果为 5，这是客户 1 订单产品数量的正确总数。

但是，如果我们删除客户 1 下达的一个订单（例如，3 个产品 2 的订单），则 SELECT 语句的结果将不正确，因为它仅包含客户 1 下达的剩余订单的数量（2 个产品 1）。这是一个添加异常的示例，因为查询的结果没有准确反映客户 1 订单产品的总数。

示例 2

这是一个演示“student”表中参照完整性中的添加异常的 SQL 查询示例 -

SQL 查询

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE grades (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  grade INT NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

INSERT INTO students (student_id, name)
VALUES (1, 'John Smith');

INSERT INTO students (student_id, name)
VALUES (2, 'Jane Doe');

INSERT INTO grades (student_id, course_id, grade)
VALUES (1, 1, 90);

INSERT INTO grades (student_id, course_id, grade)
VALUES (1, 2, 85);

INSERT INTO grades (student_id, course_id, grade)
VALUES (2, 1, 95);

SELECT AVG(grade)
FROM grades
WHERE student_id = 1;

在此示例中，我们有一个“students”表，用于存储学生信息，以及一个“grades”表，用于存储学生在课程中获得的成绩信息。“grades”表在外键列“student_id”上具有外键约束，该约束引用“students”表中的“student_id”列。

我们向“students”表中插入两行，每行代表一个学生。我们还在“grades”表中插入三行，代表这两个学生在不同课程中获得的成绩。

最后，我们运行一个 SELECT 语句以获取学生 1 获得的所有成绩的平均分。查询的结果为 87.5，这是学生 1 获得的成绩的正确平均分。

但是，如果我们从“students”表中删除学生 1，则 SELECT 语句的结果将不正确，因为它仅包含学生 2 在课程 1 中获得的成绩。这是一个添加异常的示例，因为查询的结果没有准确反映学生 1 获得的平均成绩。

结论

表中参照完整性中的添加异常发生在父表上的删除操作导致子表上 SELECT 语句的结果不正确时。

更新于: 2023年1月27日

159 次查看

开启你的 职业生涯

通过完成课程获得认证

开始学习
广告