課程名稱： 資料庫系統

8. 關聯性

Properties3
description—
tagsmssql
date2026-06-28

閲覽人數 -- 次

1. 導論：建立穩健資料庫的基石

image

在現代應用程式架構中，資料庫不僅是資料的儲存庫，更是維繫業務邏輯與資料一致性的核心。其中，資料庫關聯性 (Relationship) 的設計，是決定此核心是否穩固的基石。一份良好、清晰的關聯性設計不僅是技術上的要求，更是確保資料完整性、提升系統效能與保障長期可維護性的策略性基礎。一個結構混亂、關聯性定義不清的資料庫，將不可避免地導致資料不一致、查詢效能低下，並在未來的系統擴充中成為沉重的技術債。

image

本文旨在為開發者與資料庫架構師提供一套標準化、可依循的 SQL Server 關聯性設計規範與最佳實務。我們將從最核心的概念出發，深入探討各類關聯模式的應用場景，解析參考完整性動作的策略，並提供完整的生命週期管理指引。

總而言之，精準的關聯性設計是資料庫架構的靈魂，它將直接決定系統的健壯與韌性。接下來，讓我們從關聯性的核心概念開始解析。

2. 關聯性核心概念解析

本章節的目標是清晰定義關聯性設計中的基本詞彙與核心機制。透徹理解這些基礎概念，對於後續探討複雜的設計模式與最佳實務至關重要，它能確保我們在討論時擁有共同的語言與認知基礎。

在關聯式資料庫理論中，關聯性 (Relationship) 指的是兩個或多個資料表之間的邏輯連結。它將原本獨立的資料實體（如客戶、訂單、產品）串聯起來，形成一個能夠反映真實世界業務規則的有機整體。

image

在 SQL Server 技術實作中，此邏輯連結是透過 外來鍵 (Foreign Key) 約束來建立和強制執行的。外來鍵是子資料表中的一個（或一組）欄位，其值必須對應到父資料表的主鍵 (Primary Key)。此機制的唯一目的，就是強制執行參考完整性 (Referential Integrity)，確保任何指向父表的子表記錄都必須是有效的，從而根除「孤兒記錄」(Orphan Records) 的產生。

在理解了關聯性的基本定義後，接下來我們將深入探討其在資料庫設計中最主要的三種類型。

3. 關聯性類型與設計模式分析

選擇正確的關聯性類型，是資料庫塑模 (Data Modeling) 過程中至關重要的步驟。不同的業務場景需要不同的資料結構來精準對應。本章節將不僅展示三種主要關聯類型的實作方式，更會深入分析其各自適用的業務場景與設計上的關鍵考量。

3.1 一對一關聯 (One-to-One)

image

「一對一關聯」是指一個資料表中的一筆記錄，最多僅能對應到另一個資料表中的一筆記錄。雖然不如一對多關聯常見，但在特定場景下極具價值。

典型應用情境分析

• 效能優化：將一個包含大量欄位的「寬」資料表進行垂直分割。不常被查詢的欄位可以移至另一個資料表，從而減少主表的查詢負擔。
• 安全性隔離：將敏感性資料（如個人身分證號、薪資資訊）存放在一個獨立的資料表中，並對該表設定更嚴格的存取權限。
• 處理選擇性欄位：當某些欄位僅適用於一小部分記錄時（例如，只有正職員工才有退休金帳戶資訊），將這些稀疏的欄位獨立成表，可以避免在主表中產生大量的 NULL 值。

設計範例

以下範例展示了員工基本資料 Employees 與員工詳細資料 EmployeeDetails 之間的一對一關聯。

-- 員工基本資料表
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(50)
);
 
-- 員工詳細資料表 (一對一)
CREATE TABLE EmployeeDetails (
    EmployeeID INT PRIMARY KEY,
    Address NVARCHAR(200),
    PhoneNumber NVARCHAR(20),
    CONSTRAINT FK_EmployeeDetails_Employees
    FOREIGN KEY (EmployeeID)
    REFERENCES Employees(EmployeeID)
);

3.2 一對多關聯 (One-to-Many)

image

「一對多關聯」是關聯式資料庫中最普遍、最基礎的關聯類型。它描述了一個資料表中的一筆記錄可以對應到另一個資料表中的多筆記錄。

業務場景分析

這種關聯有效地組織了具有父子層級結構的資料，
例如一位客戶（父）可以擁有多筆訂單（子），
一個產品分類（父）可以包含多種產品（子）。

設計範例

Customers 資料表（一端）與 Orders 資料表（多端）的關聯是典型的例子。

-- 客戶資料表 (一)
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    Email NVARCHAR(100)
);
 
-- 訂單資料表 (多)
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(10, 2),
    CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)
);

在此結構中，Orders 資料表中的 CustomerID 欄位作為外來鍵，將多筆訂單記錄精準地連結至 Customers 資料表中的單一客戶。

3.3 多對多關聯 (Many-to-Many)

image

「多對多關聯」描述了兩個資料表的記錄可以互相對應多筆的複雜關係，例如一位學生可以選修多門課程，同時一門課程也可以被多位學生選修。此種關聯無法由兩個資料表直接建立，而必須引入第三方資料表來實現。

中介表 (Junction Table) 在此扮演了關鍵角色。它將一個複雜的「多對多」關係，巧妙地分解為兩個易於管理的「一對多」關聯。

設計範例

Students(學生主檔) 與 Courses(課程主檔) 之間的關係，
透過 StudentCourses(學生課程檔) 這個中介表來實現。

-- 學生資料表
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName NVARCHAR(100)
);
 
-- 課程資料表
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName NVARCHAR(100)
);
 
-- 選課中介表 (實作多對多)
CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATETIME,
    PRIMARY KEY (StudentID, CourseID),
    CONSTRAINT FK_StudentCourses_Students
    FOREIGN KEY (StudentID)
    REFERENCES Students(StudentID),
    CONSTRAINT FK_StudentCourses_Courses
    FOREIGN KEY (CourseID)
    REFERENCES Courses(CourseID)
);

結構分析

StudentCourses 表的結構極具代表性。
它將 Students 的主鍵 StudentID 和 Courses 的主鍵 CourseID 同時作為外來鍵，
並將這兩者組合起來形成一個複合主鍵 (Composite Primary Key)。
此設計利用了主鍵的唯一性約束，從根本上杜絕了同一位學生重複選修同一門課程的髒數據可能性，精準地強制執行了業務規則。

在定義了關聯的靜態結構後，管理這些關聯在資料變動時的動態行為同樣至關重要。這將是下一章節的探討重點。

4. 參考完整性動作之策略性應用

參考完整性動作 (Referential Integrity Actions) 是定義資料庫在父表資料發生變更（更新或刪除）時，如何自主處理子表關聯資料的關鍵規則。正確地設定 ON DELETE 和 ON UPDATE 的行為，可以讓資料庫自動維護資料一致性，避免產生「孤兒記錄」。本章節旨在分析各選項的行為，並提供選擇這些選項時的策略性考量，以避免非預期的資料行為。

image

4.1 ON DELETE 動作選項分析

以下表格詳細評估了 ON DELETE 的四個主要選項及其適用場景。

image

選項
(Option)		行為描述
(Behavior Description)		適用情境分析
(Use Case
Analysis)		潛在風險
(Potential Risks)
CASCADE刪除父表記錄時，
自動刪除所有關聯
的子表記錄。
例如：「刪除客戶
時，自動刪除該客
戶的所有訂單」。		適用於具有強烈生命
週期依賴的父子關係
例如，訂單主檔與訂
單明細，刪除主檔
時明細也一併移除。		可能引發無法預期的連鎖
刪除，尤其在複雜的關聯
鏈中，極易造成資料意外
遺失。必須極度謹慎使用。
SET NULL刪除父表記錄時，
將子表對應的外來
鍵欄位值設定為
 NULL。
例如：「刪除客戶
時，將訂單 
CustomerID 
設為 NULL。		適用於選擇性或非必
要的關聯。例如一個
產品可以屬於某個分
類，但若該分類被刪
除，產品可以暫時變
為「未分類」狀態。		使用此選項的前提是，
外來鍵欄位必須被設計
為允許 NULL 值，否則
操作將會失敗。
SET DEFAULT刪除父表記錄時，
將子表對應的外來
鍵欄位值設定為
預先定義的預設值。
例如：「刪除客戶時
，將訂單的
 CustomerID 設為預
設值」。		適用於需要將孤立記
錄重新指派給一個「
預設」或「未分類」
父記錄的場景。例如
將已刪除業務員的客
戶，自動歸屬給「公
共客戶池」。		必須確保該預設值對應的
父表記錄確實存在，否則
操作會失敗。
NO ACTION如果父表中有任何記
錄正被子表記錄參考，禁止刪除該父表記錄。例如：「禁止刪除有訂單的客
戶」。		這是最安全、最推薦
的預設選項。它強制應用程式必
須先處理所有子記錄（例如轉移或刪除），才能刪除父記錄，有效防止意外操作。		它將刪除權責明確地交由
應用程式層處理，強制開
發者必須設計有意識的、
符合業務流程的子記錄處
理邏輯，從而換來了最高
的資料安全性。

4.2 ON UPDATE 動作選項分析

image

在穩健的資料庫設計中，主鍵值一經確立便不應變更，這是基本原則。然而，在資料遷移或系統整併等極少數的例外情境下，ON UPDATE 動作提供了維護資料一致性的最後一道防線。

ON UPDATE CASCADE 是最常用的選項。

其行為如下

「更新客戶ID時，自動更新訂單中的CustomerID」

價值分析

ON UPDATE CASCADE 的主要價值在於自動維護資料的一致性。
如果父表的主鍵值因故必須更新，此設定可以確保所有子表中對應的外來鍵值也同步更新，避免關聯中斷。

在理解了關聯性的定義與行為後，接著將探討如何在 SQL Server 中具體地建立、修改與查詢這些關聯。

5. 關聯性生命週期管理

本章節將涵蓋關聯性從建立、修改到移除的完整生命週期操作。同時，我們也會提供查詢現有關聯性狀態的標準方法。掌握這些實務操作，是資料庫日常維護與結構演進的基礎技能。

image

5.1 關聯性的建立與移除

管理關聯性約束主要有以下三種標準方法：

管理關聯性約束的三種標準方法

• 方法 1：建立資料表時定義  這是在資料庫設計階段定義關聯性的最標準、最清晰的作法。在 CREATE TABLE 語句中直接宣告外來鍵約束。
• 方法 2：修改現有資料表  此方法適用於對現有資料庫結構進行調整或補強，例如為兩個已存在的資料表補上關聯性。
• 方法 3：移除關聯性  當業務邏輯變更或需要重構資料表時，可以使用此方法來解除資料表之間的約束。

5.2 查詢現有關聯性資訊

image

查詢系統目錄檢視 (System Catalog Views) 是審核和理解資料庫結構的關鍵技能。透過查詢 sys.foreign_keys 及相關檢視，可以獲取資料庫中所有外來鍵的詳細資訊。

以下 T-SQL 指令碼能清楚地列出資料庫中所有的外來鍵關聯及其屬性：

列出資料庫中所有的外來鍵關聯及其屬性的 T-SQL 指令碼

-- 查詢資料庫中所有外來鍵關聯
SELECT
    FK.name AS ForeignKeyName,
    OBJECT_NAME(FK.parent_object_id) AS TableName,
    COL.name AS ColumnName,
    OBJECT_NAME(FK.referenced_object_id) AS ReferencedTable,
    RCOL.name AS ReferencedColumn,
    FK.delete_referential_action_desc AS DeleteAction,
    FK.update_referential_action_desc AS UpdateAction
FROM sys.foreign_keys FK
INNER JOIN sys.foreign_key_columns FKC
    ON FK.object_id = FKC.constraint_object_id
INNER JOIN sys.columns COL
    ON FKC.parent_object_id = COL.object_id
    AND FKC.parent_column_id = COL.column_id
INNER JOIN sys.columns RCOL
    ON FKC.referenced_object_id = RCOL.object_id
    AND FKC.referenced_column_id = RCOL.column_id;

查詢結果欄位解讀

• ForeignKeyName: 外來鍵約束的名稱。
• TableName: 包含外來鍵的子資料表名稱。
• ColumnName: 子資料表中的外來鍵欄位名稱。
• ReferencedTable: 外來鍵所參考的父資料表名稱。
• ReferencedColumn: 父資料表中的主鍵欄位名稱。
• DeleteAction: ON DELETE 設定的動作（例如 CASCADE, NO_ACTION）。
• UpdateAction: ON UPDATE 設定的動作。

掌握了技術面的實作後，更重要的是遵循設計的最佳實務，以確保長期的系統健康。

6. 實戰演練：設計電商系統架構

image

image

image

7. 設計與實作之最佳實務

遵循業界認可的最佳實務，是區分專業與業餘資料庫設計的關鍵。它不僅關乎當下的效能，更決定了系統未來的可擴展性與可維護性。本章節將提煉出最重要的設計原則，旨在協助開發者建立高效、穩健且易於維護的資料庫結構。

image

7.1 採用標準化的約束命名慣例

規範： 建議採用 FK_子表名稱_父表名稱 的命名格式。例如，Orders 表參考 Customers 表的外來鍵，應命名為 FK_Orders_Customers。

分析： 統一的命名規則能大幅提升資料庫結構的可讀性。當應用程式拋出違反外來鍵約束的錯誤時，開發人員可以從約束名稱立即判斷出涉及哪兩個資料表，從而快速定位問題根源，極大地簡化了除錯過程。

7.2 審慎評估 CASCADE 動作

規範： 謹慎使用 ON DELETE CASCADE，並將 NO ACTION 作為預設的首選。

分析：  CASCADE 動作雖然方便，但也極具風險。在複雜的關聯鏈中（例如 A -> B -> C），對 A 的一筆記錄執行刪除，可能會觸發對 B 和 C 的大規模連鎖刪除，而這種行為往往難以追蹤且可能是非預期的。建議僅在父子關係非常明確（如訂單與訂單明細）且業務邏輯完全允許的情況下才使用。

7.3 為外來鍵建立索引

規範： 所有外來鍵欄位都應建立非叢集索引 (Non-Clustered Index)。

分析： 這是提升效能最關鍵的實務之一。索引在外來鍵上有兩個主要作用：
▪ 加速 JOIN 查詢： 絕大多數涉及關聯的查詢都會使用 JOIN 操作，索引能讓資料庫快速定位到匹配的記錄，避免全表掃描。
▪ 維護參考完整性： 當對父表進行 DELETE 或 UPDATE 操作時，資料庫需要檢查子表中是否存在參考記錄。如果外來鍵有索引，這個檢查過程會非常快；反之，資料庫可能需要掃描整個子表，造成嚴重的效能瓶頸甚至鎖定問題。

7.4 避免循環參考 (Circular References)

規範： 資料表之間的關聯性不應形成閉環。例如，避免出現表 A 參考表 B，同時表 B 又參考表 A 的情況。

分析： 循環參考會導致資料載入順序和刪除操作變得極為複雜，甚至不可能順利執行。它通常暗示著資料模型設計上的缺陷。遇到此類情況，應重新審視實體關係，考慮引入第三個資料表或調整關聯方向來解除此類死結依賴。

7.5 維持關聯性文件化

規範： 維護一份最新的資料庫關聯圖 (ERD - Entity Relationship Diagram) 或相關設計文件。

分析： 程式碼本身雖然是最終的真相，但一份視覺化的 ERD 文件對於團隊協作至關重要。它不僅能幫助新成員快速理解複雜的資料結構，也是架構師、開發人員和業務分析師之間溝通的共同語言，更是未來系統演進與重構的重要依據。

8. 實作範例

8.1 範例資料庫表格及資料

建立資料庫表格及資料

USE [demodb]
GO
 
DROP TABLE IF EXISTS [dbo].[Customers]
DROP TABLE IF EXISTS [dbo].[Employees]
DROP TABLE IF EXISTS [dbo].[Departments]
DROP TABLE IF EXISTS [dbo].[Titles]
DROP TABLE IF EXISTS [dbo].[OrderMaster]
DROP TABLE IF EXISTS [dbo].[OrderDetails]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CustNo] [nvarchar](50) NULL,
	[CustName] [nvarchar](50) NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[Employees](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[IsValid] [bit] NULL,
	[EmpNo] [nvarchar](50) NULL,
	[EmpName] [nvarchar](50) NULL,
	[GenderCode] [nvarchar](50) NULL,
	[DeptNo] [nvarchar](50) NULL,
	[TitleNo] [nvarchar](50) NULL,
	[Birthday] [date] NULL,
	[OnboardDate] [date] NULL,
	[LeaveDate] [date] NULL,
	[ContactEmail] [nvarchar](50) NULL,
	[ContactTel] [nvarchar](50) NULL,
	[ContactAddress] [nvarchar](250) NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[Departments](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DeptNo] [nvarchar](50) NULL,
	[DeptName] [nvarchar](50) NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_Departments] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[Titles](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TitleNo] [nvarchar](50) NULL,
	[TitleName] [nvarchar](50) NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_Titles] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[OrderMaster](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderType] [nvarchar](50) NULL,
	[OrderNo] [nvarchar](50) NULL,
	[OrderDate] [Date] NULL,
	[CustNo] [nvarchar](50) NULL,
	[CustName] [nvarchar](50) NULL,
	[SalesNo] [nvarchar](50) NULL,
	[ShipAddress] [nvarchar](250) NULL,
	[Amounts] int NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_OrderMaster] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
CREATE TABLE [dbo].[OrderDetails](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderId] [int] NULL,
	[ProdNo] [nvarchar](50) NULL,
	[OrderQty] [int] NULL,
	[OrderPrice] [int] NULL,
	[Amounts] int NULL,
	[Remark] [nvarchar](250) NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
INSERT [dbo].[Customers] ([CustNo], [CustName], [Remark]) VALUES (N'A', N'大明科技', N'')
INSERT [dbo].[Customers] ([CustNo], [CustName], [Remark]) VALUES (N'B', N'小華科技', N'')
GO
INSERT [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E001', N'陳大明', N'')
INSERT [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E002', N'李小惠', N'')
INSERT [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E003', N'楊小天', N'')
INSERT [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E004', N'林小國', N'')
INSERT [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E005', N'吳小月', N'')
INSERT [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E006', N'張小賢', N'')
INSERT [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E007', N'劉小金', N'')
GO
 
INSERT [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D01', N'管理部', N'')
INSERT [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D02', N'行政部', N'')
INSERT [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D03', N'市場部', N'')
INSERT [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D04', N'資訊部', N'')
INSERT [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D05', N'品保部', N'')
GO
 
INSERT [dbo].[Titles] ([TitleNo], [TitleName], [Remark]) VALUES (N'T01', N'經理', N'')
INSERT [dbo].[Titles] ([TitleNo], [TitleName], [Remark]) VALUES (N'T02', N'副理', N'')
INSERT [dbo].[Titles] ([TitleNo], [TitleName], [Remark]) VALUES (N'T03', N'課長', N'')
GO
 
INSERT [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101001', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'A',N'大明科技', N'', N'', 0, N'')
INSERT [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101002', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'B', N'小華科技', N'', N'', 0, N'')
INSERT [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101003', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'A', N'大明科技', N'', N'', 0, N'')
INSERT [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101004', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'A', N'大明科技', N'', N'', 0, N'')
INSERT [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101005', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'B', N'小華科技', N'', N'', 0, N'')
GO
 
INSERT [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (1, N'DSTY084', 1, 1, 1, N'')
INSERT [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (1, N'SEJU092', 1, 1, 1, N'')
INSERT [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (2, N'KJDR013', 1, 1, 1, N'')
INSERT [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (3, N'CFED084', 1, 1, 1, N'')
INSERT [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (4, N'DSTY084', 1, 1, 1, N'')
INSERT [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (5, N'DFRK093', 1, 1, 1, N'')
INSERT [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (5, N'DERH036', 1, 1, 1, N'')
GO

重建範例資料

USE [demodb]
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderMaster]') AND type in (N'U'))
ALTER TABLE [dbo].[OrderMaster] DROP CONSTRAINT IF EXISTS [FK_OrderMaster_Customers]
GO
 
TRUNCATE TABLE [dbo].[Customers]
TRUNCATE TABLE [dbo].[Employees]
TRUNCATE TABLE [dbo].[Departments]
TRUNCATE TABLE [dbo].[Titles]
TRUNCATE TABLE [dbo].[OrderMaster]
TRUNCATE TABLE [dbo].[OrderDetails]
GO
 
INSERT INTO [dbo].[Customers] ([CustNo], [CustName], [Remark]) VALUES (N'A', N'大明科技', N'')
INSERT INTO [dbo].[Customers] ([CustNo], [CustName], [Remark]) VALUES (N'B', N'小華科技', N'')
GO
INSERT INTO [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E001', N'陳大明', N'')
INSERT INTO [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E002', N'李小惠', N'')
INSERT INTO [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E003', N'楊小天', N'')
INSERT INTO [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E004', N'林小國', N'')
INSERT INTO [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E005', N'吳小月', N'')
INSERT INTO [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E006', N'張小賢', N'')
INSERT INTO [dbo].[Employees] ([EmpNo], [EmpName], [Remark]) VALUES (N'E007', N'劉小金', N'')
GO
 
INSERT INTO [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D01', N'管理部', N'')
INSERT INTO [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D02', N'行政部', N'')
INSERT INTO [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D03', N'市場部', N'')
INSERT INTO [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D04', N'資訊部', N'')
INSERT INTO [dbo].[Departments] ([DeptNo], [DeptName], [Remark]) VALUES (N'D05', N'品保部', N'')
GO
 
INSERT INTO [dbo].[Titles] ([TitleNo], [TitleName], [Remark]) VALUES (N'T01', N'經理', N'')
INSERT INTO [dbo].[Titles] ([TitleNo], [TitleName], [Remark]) VALUES (N'T02', N'副理', N'')
INSERT INTO [dbo].[Titles] ([TitleNo], [TitleName], [Remark]) VALUES (N'T03', N'課長', N'')
GO
 
INSERT INTO [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101001', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'A', N'大明科技', N'', N'', 0, N'')
INSERT INTO [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101002', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'B', N'小華科技', N'', N'', 0, N'')
INSERT INTO [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101003', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'A', N'大明科技', N'', N'', 0, N'')
INSERT INTO [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101004', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'A', N'大明科技', N'', N'', 0, N'')
INSERT INTO [dbo].[OrderMaster] ([OrderType],[OrderNo], [OrderDate], [CustNo], [CustName],[SalesNo], [ShipAddress], [Amounts], [Remark]) VALUES (N'A',N'20220101005', CAST(N'2022-01-01T00:00:00.000' AS DateTime), N'B', N'小華科技', N'', N'', 0, N'')
GO
 
INSERT INTO [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (1, N'DSTY084', 1, 1, 1, N'')
INSERT INTO [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (1, N'SEJU092', 1, 1, 1, N'')
INSERT INTO [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (2, N'KJDR013', 1, 1, 1, N'')
INSERT INTO [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (3, N'CFED084', 1, 1, 1, N'')
INSERT INTO [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (4, N'DSTY084', 1, 1, 1, N'')
INSERT INTO [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (5, N'DFRK093', 1, 1, 1, N'')
INSERT INTO [dbo].[OrderDetails] ([OrderId], [ProdNo], [OrderQty], [OrderPrice], [Amounts], [Remark]) VALUES (5, N'DERH036', 1, 1, 1, N'')
GO

8.2 建立資料表格關聯的要件

image

要能順利建立資料表間的關聯，則必須滿足以下要個要件：

image

建立資料表格關聯的要件

  • 主檔 的 關聯欄 位必須建立 索引 且設定為 唯一

    image

  • 用 交易檔 去關聯 主檔

    image

操作步驟

image

image

8.3 資料表結構

資料表結構

image

8.4 使用重疊顯示範例

使用重疊顯示範例

image

image

image

8.5 使用設定為 Null 範例

使用設定為 Null 範例

image

image

image

8.6 使用設定為沒有動作範例

使用設定為沒有動作範例

image

image

image

9. 關聯欄位的選擇

9.1 關聯欄位的選擇說明

image

對新手開發者而言，選擇用「業務代號 (No)」還是「系統識別碼 (Id)」來建立資料表間的關聯，似乎只是一個隨機的技術細節；但在資深資料庫架構師眼中，這個決策直接決定了系統的「容錯能力 (Fault Tolerance)」與「資料完整性 (Data Integrity)」。

image

在我們深入技術細節前，請先思考這個核心問題

當資料消失或重複時，你的關聯還能維持正確嗎？

好的關聯設計能在發生人為疏失或業務變動時，像自動化防護網一樣維持數據穩定，而不佳的設計則會讓資料在瞬間淪為「孤兒紀錄」。接下來，我們將透過兩個經典的實務場景，拆解這背後的設計智慧。

image

9.2 案例剖析一(使用 No 來當關聯)：行政部的消失與重生

使用 No 來當關聯的時機最好是用於 交易檔與主檔 之間的關聯。

image

在企業應用中，「員工主檔 (Employees)」與「部門主檔 (Departments)」的關聯是最常見的設計場景。這裡涉及的是「交易/主檔」的關係，我們必須考量當維護資料的人員發生操作失誤時，系統的恢復能力。

衝突情境

假設目前有三名員工隸屬於「DO2 行政部」。
某次操作中，維護人員誤刪了部門主檔中的「DO2 行政部」紀錄。
雖然他隨即發現錯誤並立即補建一筆同樣名為「DO2 行政部」的新資料，但在資料庫底層，這兩次操作已經產生了截然不同的結果。

image

如下圖示所示， 員工主檔(Employees) 中的 部門代號(DeptNo) 及 職稱代號(TitleNo) 分別與 部門主檔(Departments) 及 職稱主檔(Titles) 產生關聯，此時用的是 No 來當關聯，而非用 Id 當關聯。

資料表關聯圖

image

關鍵點在於如用的是 Id 來當關聯鍵，會造成如下的問題
下點為 三名員工 都為 行政部 部門

範例資料

image

結果發生了操作人員 誤刪除 了部門主檔的 DO2行政部 資料，接著發現後又立即再 新增 補上一筆 DO2行政部 , 結果造成三名員工找不到部門的問題

誤刪除作業

image

此時如果用的是 No 來當關聯鍵的話，當操作員補回 DO2行政部 時，此時關聯自然又能連上了。

image

9.3 案例剖析二(使用 Id 來當關聯)：鬧雙胞的訂單編號 (唯一性的陷阱)

使用 Id 來當關聯的時機最好是用於 表單表頭與表單明細檔 之間的關聯。

image

然而，並非所有情境都適合使用「代號」。當我們處理「表單表頭」與「明細檔」的關係時，情況將發生逆轉。

複雜性說明：業務邏輯的重疊

在大型系統中，不同業務部門可能會產生編號重疊的現象。例如：

  • 類別 A（客戶訂單）：由銷售部產生，編號序列可能從 110901 開始。
  • 類別 B（樣品訂單）：由研發部產生，編號序列也可能包含 110901。

image

在業務面上，這兩張單據是完全不同的實體，但「訂單編號 (OrderNo)」卻不再具備全球唯一性。

資料庫危機預警：

如果在表頭與明細的關聯中使用「訂單編號 (No)」，系統將面臨毀滅性的邏輯災難。當訂單編號重複時，系統會無法判定特定明細究竟屬於「客戶訂單」還是「樣品訂單」。這不僅會導致財務核算錯誤，更可能造成出錯貨、報價混亂等現實商業損失。

image

決策邏輯：Id 作為永久錨點

在此情境下，我們必須捨棄業務編號，改用 Id 作為關聯鍵。系統生成的 Id 是唯一的 surrogate key，它是系統層級的永久錨點。不論外部的業務編號如何重疊，Id 都能精準、絕對地鎖定唯一的表頭紀錄，確保每一筆明細都精準掛載在正確的父項下。

如下圖示所示，訂單編號可能不是唯一值 ，不能當成關聯鍵使用，此例
訂單類別 A=客戶訂單 B=樣品訂單 ，
訂單編號 110901 重覆，用為關聯並不適當。

表單使用 Id 關聯

image

9.4 結論

image

關係圖譜