如何基于唯一非聚集索引创建外键关联？现有表架构问题

问题背景 #

现有表Table1的StockNumber列因合理原因既非主键也非唯一键，且无法修改该列属性。Table1存在一个包含StockNumber、Location、CreatedDateTime三列的UNIQUE NONCLUSTERED索引。创建新表Table2时，希望将其StockNumber列作为外键关联Table1的StockNumber列，但操作出现以下错误：

• 错误1：Foreign key 'FK_dbo_Table2_StockNumber' references invalid column 'AK_dbo_Table1_StockNumber_Location_CreatedDateTime' in referenced table 'Table1'.
• 尝试关联StockNumber和CreatedDateTime列时，出现错误：There are no primary or candidate keys in the referenced table 'dbo.Table1' that match the referencing column list in the foreign key 'FK_dbo_Table2_StockNumber_CreatedDateTime'.

待解决问题 #

1. 操作中存在哪些错误？
2. 在现有Table1架构下能否实现需求？若不能，有哪些可选方案？

问题解答 #

1. 操作中的错误点 #

• 错误1的原因：外键定义时错误引用了索引名称（AK_dbo_Table1_StockNumber_Location_CreatedDateTime）而非表中的列名。外键必须直接指向被引用表的列，不能指向索引名称。
• 关联StockNumber+CreatedDateTime时的错误原因：外键关联的列组合必须匹配被引用表中**主键或唯一约束（包括唯一索引对应的约束）**的列顺序和列类型。Table1的唯一索引是StockNumber+Location+CreatedDateTime三列组合，仅关联前两列（或非完整组合）不符合唯一约束的列要求，数据库无法找到匹配的候选键。

2. 现有架构下的可行性及替代方案 #

可行性判定

现有Table1架构下无法直接实现仅用StockNumber作为外键关联的需求，因为外键要求被引用的列（或列组合）必须是被引用表的主键或唯一约束/唯一索引的完整列组合，而StockNumber本身不是唯一键，单独作为外键无法保证引用的行唯一性。

可选替代方案

• 方案1：让Table2的外键关联Table1唯一索引的完整列组合
  在Table2中添加Location和CreatedDateTime列，然后创建包含StockNumber+Location+CreatedDateTime三列的外键，匹配Table1的唯一索引列组合。示例代码：

  -- 先修改Table2添加对应列
  ALTER TABLE dbo.Table2 
  ADD Location VARCHAR(50), CreatedDateTime DATETIME; -- 列类型需与Table1对应列一致
  
  -- 创建外键
  ALTER TABLE dbo.Table2
  ADD CONSTRAINT FK_Table2_Table1
  FOREIGN KEY (StockNumber, Location, CreatedDateTime)
  REFERENCES dbo.Table1(StockNumber, Location, CreatedDateTime);
  

• 方案2：在Table1新增唯一约束（基于业务允许的唯一列组合）
  如果业务上存在其他可作为唯一标识的列（或列组合），且能修改Table1添加唯一约束（注意不是修改StockNumber属性），可以基于该组合创建唯一约束，再让Table2关联这个组合。
• 方案3：使用触发器替代外键约束
  若无法修改Table2添加额外列，也无法修改Table1，可以在Table2上创建插入/更新触发器，校验插入的StockNumber在Table1中存在（注意：这种方式无法保证引用完整性的强一致性，仅作为临时替代方案）。示例逻辑：

  CREATE TRIGGER TR_Table2_ValidateStockNumber
  ON dbo.Table2
  AFTER INSERT, UPDATE
  AS
  BEGIN
      IF EXISTS (
          SELECT 1 
          FROM inserted i
          LEFT JOIN dbo.Table1 t ON i.StockNumber = t.StockNumber
          WHERE t.StockNumber IS NULL
      )
      BEGIN
          RAISERROR('StockNumber不存在于Table1中', 16, 1);
          ROLLBACK TRANSACTION;
      END
  END
  

内容的提问来源于stack exchange，提问作者GThree