添加約束以檢查值是否存在於非唯一複合鍵列中

我有下表，它有一個複合主鍵：

CREATE TABLE dbo.Seqs(
   SequenceKey nvarchar(10) NOT NULL,
   UsageSequence smallint NOT NULL,
   col1 bit NOT NULL,
   col2 nvarchar(30) NULL,
   CONSTRAINT PK_Seqs PRIMARY KEY CLUSTERED 
   (
       SequenceKey ASC,
       UsageSequence ASC
   )
)

我正在尋找創建下表，其中的“SequenceKey”列必須只接受“Seqs”表中存在的值：

CREATE TABLE dbo.Sometbl (
   SomeID int IDENTITY(1,1) NOT NULL,
   Col1 int NOT NULL,
   Col2 nvarchar(100) NOT NULL,
   Col3 nvarchar(30) NULL,
   SequenceKey nvarchar(10) NOT NULL,
   CONSTRAINT PK_Sometbl PRIMARY KEY CLUSTERED (
       SomeID ASC
       )
   )

鑑於“Seqs”表中的“SequenceKey”不是唯一的（主鍵唯一性依賴於“UsageSequence”列），確保不能在“Sometbl”中添加或更新數據的最佳方法是“ ‘Seqs’ 表中不存在的 SequenceKey’ 值？

作為對我自己的問題的可能答案，這可以通過添加帶有以下函式的 Check 約束來實現：

CREATE FUNCTION dbo.CheckSequenceKey (@SequenceKey nvarchar(10))
RETURNS bit
AS 
BEGIN
 DECLARE @retval bit

 IF EXISTS (SELECT 1 FROM dbo.Seqs S WHERE S.SequenceKey = @SequenceKey)
     SET @retval = 1
 ELSE 
     SET @retval = 0

 RETURN @retval
END;
GO

ALTER TABLE dbo.Sometbl
 ADD CONSTRAINT chkSequenceKey
 CHECK (dbo.CheckSequenceKey(SequenceKey) = 1);

編輯：

Check 約束防止插入和更新到 ‘Seqs’ 表中未找到 ‘SequenceKey’ 的新值的 ‘Sometbl’ 表，這是我對使用觸發器的偏好。

但是，檢查約束不限制對“Seqs”表中“SequenceKey”列中的值的更新或從該表中刪除整行，這可能導致“Sometbl”表中的孤立記錄。因此，除了檢查約束和功能之外，我還從 EzLo 的答案中提取了以下內容，以獲得更完整的解決方案：

• 然後，如果正在引用，dbo.Seqs您可以回滾嘗試更新或刪除的操作：SequenceKey

CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnDelete ON dbo.Seqs
   AFTER DELETE
AS
BEGIN

   IF EXISTS (
       SELECT
           'deleted records have a SequenceKey being referenced'
       FROM
           deleted AS D
       WHERE
           EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
   BEGIN
       RAISERROR('Can''t delete SequenceKey being referenced in dbo.Sometbl', 16, 1)
       ROLLBACK
   END

END

-------------------------------------------------------------------

CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnUpdate ON dbo.Seqs
   AFTER UPDATE
AS
BEGIN

   IF UPDATE(SequenceKey) AND EXISTS (
       SELECT
           'updated records have a SequenceKey being referenced'
       FROM
           deleted AS D
       WHERE
           EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
   BEGIN
       RAISERROR('Can''t update SequenceKey being referenced in dbo.Sometbl', 16, 1)
       ROLLBACK
   END

END

一種解決方案是使用觸發器來檢查完整性。請記住，您必須檢查兩個表上的操作，因為可以從dbo.Seqs.

• dbo.Sometbl如果在 上不存在插入或更新的值，則第一個觸發器將回滾dbo.Seqs：

CREATE TRIGGER dbo.utrValidateSequenceKey ON dbo.Sometbl
   AFTER INSERT, UPDATE
AS
BEGIN

   IF EXISTS (
       SELECT
           'new or updated records have an invalid SequenceKey'
       FROM
           inserted AS I
       WHERE
           NOT EXISTS (SELECT 'the sequence does not exist' FROM dbo.Seqs AS S WHERE I.SequenceKey = S.SequenceKey))
   BEGIN
       RAISERROR('Invalid SequenceKey detected on dbo.Sometbl', 16, 1)
       ROLLBACK
   END

END

• 然後，如果正在引用，dbo.Seqs您可以回滾嘗試更新或刪除的操作：SequenceKey

CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnDelete ON dbo.Seqs
   AFTER DELETE
AS
BEGIN

   IF EXISTS (
       SELECT
           'deleted records have a SequenceKey being referenced'
       FROM
           deleted AS D
       WHERE
           EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
   BEGIN
       RAISERROR('Can''t delete SequenceKey being referenced in dbo.Sometbl', 16, 1)
       ROLLBACK
   END

END

-------------------------------------------------------------------

CREATE TRIGGER dbo.utrCheckSequenceKeyReferencesOnUpdate ON dbo.Seqs
   AFTER UPDATE
AS
BEGIN

   IF UPDATE(SequenceKey) AND EXISTS (
       SELECT
           'updated records have a SequenceKey being referenced'
       FROM
           deleted AS D
       WHERE
           EXISTS (SELECT 'the sequence is being referenced' FROM dbo.Sometbl AS S WHERE S.SequenceKey = D.SequenceKey))
   BEGIN
       RAISERROR('Can''t update SequenceKey being referenced in dbo.Sometbl', 16, 1)
       ROLLBACK
   END

END

引用自：https://dba.stackexchange.com/questions/232518