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 次檢視

開啟你的 職業生涯

透過完成課程獲得認證

開始學習
廣告