[SQL] Foreign Key - 多欄位

Foreign Key 關聯欄位是允許 null，但在多欄位情況下會更凸顯欄位允許 null 的問題

官方文章 -  Create Foreign Key Relationships 內容有提到該點

When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column. Otherwise, a foreign key violation error message is returned. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.

已經說明多欄位情況下，FK 要有限制資料唯一性功能，要每個欄位都有值喔

簡易範例來記錄

USE [AdventureWorks2019]
GO

---------- 建立 Table
-- 建立 tblMaster Table 並以 Col1 和 Col2 為 PK

DROP TABLE IF EXISTS tblDetail
DROP TABLE IF EXISTS tblMaster

CREATE TABLE [dbo].[tblMaster](
	[Col1] [char](5) NOT NULL,
	[Col2] [char](10) NOT NULL,
 CONSTRAINT [PK_tblMaster] PRIMARY KEY CLUSTERED 
(
	[Col1] ASC,
	[Col2] 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

-- 建立 tblDetail Table 並建立 Col1 和 Col2 FK

CREATE TABLE [dbo].[tblDetail](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Col1] [char](5) NULL,  -- 允許 null
	[Col2] [char](10) NULL, -- 允許 null
 CONSTRAINT [PK_tblDetail] PRIMARY KEY CLUSTERED 
(
	[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

ALTER TABLE [dbo].[tblDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblDetail_tblDetail] FOREIGN KEY([Col1], [Col2])
REFERENCES [dbo].[tblMaster] ([Col1], [Col2])
GO

ALTER TABLE [dbo].[tblDetail] CHECK CONSTRAINT [FK_tblDetail_tblDetail]

---------- insert 資料並觀察 FK 是否有作用

-- tblMaster
INSERT INTO tblMaster (Col1 , Col2) VALUES('12345' , '1234567890')

-- tblDetail
INSERT INTO tblDetail (Col1 , Col2) VALUES(NULL , NULL)
INSERT INTO tblDetail (Col1 , Col2) VALUES('12345' , NULL)
INSERT INTO tblDetail (Col1 , Col2) VALUES(NULL , '1234567890')

-- 下述兩個 insert 插入不存在 tblmaster 資料 
INSERT INTO tblDetail (Col1 , Col2) VALUES('00000' , NULL)
INSERT INTO tblDetail (Col1 , Col2) VALUES(NULL , '0000000000')

-- Col1 和 Col2 都有值，會引發 FK
INSERT INTO tblDetail (Col1 , Col2) VALUES('00000' , '0000000000')

• 參考資料
• Constraints在資料含有NULL時的隱憂