在 oracle 中創建表的外鍵關系有兩種方式：創建表時定義或創建后添加。1. 創建表時定義外鍵語法為 create table 子表名 (列定義, constraint 約束名 foreign key (外鍵列) references 父表名 (父表列) [on delete cascade] [on update cascade]); 2. 表創建后添加外鍵使用 alter table 子表名 add constraint 約束名 foreign key (外鍵列) references 父表名 (父表列) [on delete cascade]; 外鍵約束的 on delete 和 on update 可指定級聯行為，包括 cascade、set NULL、set default、restrict 和 no action，選擇依據是業務需求和數據完整性策略；若遇到 ora-02291 錯誤，應檢查父表是否存在對應記錄、列數據類型是否一致、主鍵是否有效、觸發器是否干擾，或臨時禁用外鍵（不推薦）；外鍵會影響插入、更新、刪除性能，優化方法包括建立索引、避免不必要的級聯刪除、使用批量操作、采用分區表、避免循環引用及定期維護數據庫。
在 oracle 中創建表的外鍵關系，簡單來說，就是定義一個表中的列（外鍵）引用另一個表中的列（主鍵或唯一鍵），以確保數據的一致性和完整性。這就像兩個表之間建立了一座橋梁，通過這座橋梁，可以保證數據的有效性。
解決方案
創建外鍵關系，通常有兩種方式：在創建表時定義，或者在表創建之后添加。
1. 創建表時定義外鍵：
CREATE TABLE 子表名 ( 列1 數據類型, 列2 數據類型, ... 外鍵列 數據類型, CONSTRAINT 外鍵約束名 FOREIGN KEY (外鍵列) REFERENCES 父表名 (父表主鍵列) ON DELETE CASCADE -- 可選：定義刪除行為 ON UPDATE CASCADE -- 可選：定義更新行為 );
- 子表名: 需要創建外鍵關系的表名。
- 外鍵列: 子表中作為外鍵的列名。
- 父表名: 被引用的表名。
- 父表主鍵列: 父表中被外鍵引用的列名 (通常是主鍵，也可以是唯一鍵)。
- 外鍵約束名: 外鍵約束的名稱，建議取一個有意義的名字。
- ON DELETE CASCADE: 可選，定義刪除父表記錄時的行為。CASCADE 表示刪除父表記錄時，自動刪除子表中對應的記錄。還有 SET NULL (將子表外鍵列設為 NULL), SET DEFAULT (將子表外鍵列設為默認值), RESTRICT (阻止刪除父表記錄) 和 NO ACTION (與 RESTRICT 類似，但檢查時機不同) 等選項。
- ON UPDATE CASCADE: 可選，定義更新父表記錄時的行為。類似于 ON DELETE CASCADE，但應用于父表主鍵列的更新。
舉例：
假設我們有兩個表：customers (客戶) 和 orders (訂單)。 orders 表的外鍵 customer_id 引用 customers 表的主鍵 customer_id。
CREATE TABLE customers ( customer_id number PRIMARY KEY, customer_name VARCHAR2(100) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE CASCADE );
2. 表創建后添加外鍵：
ALTER TABLE 子表名 ADD CONSTRAINT 外鍵約束名 FOREIGN KEY (外鍵列) REFERENCES 父表名 (父表主鍵列) ON DELETE CASCADE; -- 可選
舉例：
如果我們已經創建了 customers 和 orders 表，但沒有定義外鍵，可以使用以下語句添加：
ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE CASCADE;
副標題1： 外鍵約束的 ON DELETE 和 ON UPDATE 選項有什么區別？如何選擇？
ON DELETE 和 ON UPDATE 選項定義了在父表（被引用表）中的記錄被刪除或更新時，子表（引用表）中相應記錄的行為。選擇哪種選項取決于你的業務需求和數據完整性策略。
-
CASCADE: 父表記錄被刪除/更新時，子表中對應的記錄也會被自動刪除/更新。 這是一種級聯操作，可以保持數據一致性，但需要謹慎使用，避免誤刪數據。
-
SET NULL: 父表記錄被刪除/更新時，子表中對應的外鍵列會被設置為 NULL。 這意味著子表記錄仍然存在，但不再與父表記錄關聯。 使用此選項的前提是外鍵列允許為 NULL。
-
SET DEFAULT: 父表記錄被刪除/更新時，子表中對應的外鍵列會被設置為該列的默認值。 使用此選項的前提是外鍵列定義了默認值。
-
RESTRICT 或 NO ACTION: 父表記錄不能被刪除/更新，除非子表中沒有引用該記錄。 這是一種最嚴格的約束，可以防止數據不一致，但可能會限制父表的操作。RESTRICT 和 NO ACTION 的區別在于檢查的時機，RESTRICT 在語句執行前檢查，NO ACTION 在語句執行后檢查。在 Oracle 中，兩者行為基本相同。
如何選擇？
- 如果刪除/更新父表記錄后，子表記錄也應該被刪除，選擇 CASCADE。
- 如果刪除/更新父表記錄后，子表記錄應該保留，但不與父表記錄關聯，選擇 SET NULL 或 SET DEFAULT。
- 如果為了保證數據完整性，絕對不允許刪除/更新父表記錄，除非子表中沒有引用該記錄，選擇 RESTRICT 或 NO ACTION。
需要注意的是，選擇 CASCADE 要非常小心，因為它可能會導致意外的數據丟失。 在生產環境中，建議仔細評估各種選項的風險和收益，并進行充分的測試。
副標題2： 創建外鍵時遇到 “ORA-02291: integrity constraint violated – parent key not found” 錯誤怎么辦？
這個錯誤 “ORA-02291: integrity constraint violated – parent key not found” 意味著你試圖在子表中插入或更新一個外鍵值，但在父表中找不到對應的記錄。 簡單來說，就是你想在訂單表里關聯一個不存在的客戶ID。
解決方法：
-
檢查數據： 這是最常見的原因。仔細檢查你試圖插入或更新的子表記錄的外鍵值，確保它在父表中存在。 可以使用以下 sql 查詢來驗證：
SELECT * FROM 父表名 WHERE 父表主鍵列 = '你試圖插入的外鍵值';
如果查詢結果為空，說明父表中不存在該值。
-
檢查列的數據類型： 確保子表的外鍵列和父表的主鍵列的數據類型完全一致。 例如，如果父表的主鍵列是 NUMBER 類型，子表的外鍵列也必須是 NUMBER 類型，而不是 VARCHAR2 類型。
-
檢查父表主鍵是否被禁用或刪除： 極少數情況下，父表的主鍵約束可能被禁用或刪除。如果是這種情況，需要重新啟用或創建主鍵約束。
-
檢查觸發器： 如果父表或子表上定義了觸發器，可能會導致外鍵約束檢查失敗。 仔細檢查觸發器的邏輯，確保它不會阻止外鍵關系的建立。
-
臨時禁用外鍵約束（不推薦）： 在某些特殊情況下，你可能需要臨時禁用外鍵約束，例如，在批量導入數據時。 可以使用以下 SQL 語句禁用外鍵約束：
ALTER TABLE 子表名 DISABLE CONSTRAINT 外鍵約束名;
完成數據導入后，一定要重新啟用外鍵約束：
ALTER TABLE 子表名 ENABLE CONSTRAINT 外鍵約束名;
警告： 臨時禁用外鍵約束會降低數據完整性，因此應該謹慎使用，并在操作完成后立即重新啟用。
副標題3： 外鍵約束的性能影響是什么？如何優化？
外鍵約束可以保證數據的一致性和完整性，但也會帶來一定的性能開銷。每次插入、更新或刪除子表記錄時，數據庫都需要檢查外鍵約束，以確保數據的有效性。
性能影響：
- 插入操作： 在插入子表記錄時，數據庫需要查詢父表，以驗證外鍵值是否存在。
- 更新操作： 在更新子表外鍵列時，數據庫需要查詢父表，以驗證新的外鍵值是否存在。
- 刪除操作： 在刪除父表記錄時，如果子表定義了 ON DELETE CASCADE 選項，數據庫需要級聯刪除子表中對應的記錄。
優化方法：
-
索引： 在外鍵列和父表的主鍵列上創建索引可以顯著提高查詢性能。 索引可以幫助數據庫快速找到匹配的記錄，而無需掃描整個表。
CREATE INDEX idx_外鍵列 ON 子表名 (外鍵列); CREATE INDEX idx_父表主鍵列 ON 父表名 (父表主鍵列);
-
避免不必要的級聯刪除： ON DELETE CASCADE 選項可能會導致大量的級聯刪除操作，從而影響性能。 如果不需要級聯刪除，可以考慮使用 SET NULL 或 RESTRICT 選項。
-
批量操作： 盡量使用批量操作（例如，使用 INSERT ALL 語句）來插入或更新數據，而不是逐條操作。 批量操作可以減少數據庫的開銷，提高性能。
-
分區表： 如果表非常大，可以考慮使用分區表來提高查詢性能。 分區表可以將數據分成多個小的分區，數據庫可以只掃描相關的分區，而無需掃描整個表。
-
避免循環引用： 循環引用是指兩個或多個表相互引用，形成一個環。 循環引用會增加外鍵約束的復雜性，并可能導致性能問題。 盡量避免循環引用，或者使用觸發器來維護數據一致性。
-
定期維護： 定期維護數據庫，例如，重建索引、更新統計信息，可以提高查詢性能。
總的來說，外鍵約束的性能影響取決于數據量、查詢頻率和數據庫配置。 通過合理的索引、避免不必要的級聯刪除和使用批量操作等方法，可以有效地優化外鍵約束的性能。 在生產環境中，建議進行性能測試，以確定最佳的優化策略。
