SQL Server复合候选键创建及自引用外键报错求助

Hey，我来帮你解决这个问题。你要实现的是子文件夹的父ID必须指向同用户下的父文件夹，这个需求其实需要结合复合唯一约束和复合外键来实现，咱们一步步来：

一、正确实现自引用外键的步骤 #

首先，SQL Server的外键约束要求引用的目标必须是唯一键（主键或者唯一约束）。你要同时验证ParentFolderId和UserId匹配，所以不能只关联Id，得先给(Id, UserId)建一个复合唯一约束，让它成为外键能引用的候选键。

1. 先创建复合唯一约束（候选键） #

虽然Id本身是主键已经唯一，但(Id, UserId)的组合天然也是唯一的，但SQL Server还是要求你显式声明这个组合是唯一约束，才能被外键引用。执行这条语句：

ALTER TABLE Folders
ADD CONSTRAINT UQ_Folders_Id_UserId UNIQUE (Id, UserId);

2. 再添加自引用复合外键 #

现在就可以创建自引用外键，让ParentFolderId和当前记录的UserId同时关联父记录的Id和UserId，这样就保证了父文件夹一定属于同一个用户：

ALTER TABLE Folders
ADD CONSTRAINT FK_Folders_ParentFolder
FOREIGN KEY (ParentFolderId, UserId)
REFERENCES Folders (Id, UserId);

二、你之前报错的大概率原因 #

1. 没先建复合唯一约束就直接加外键：如果直接把(ParentFolderId, UserId)关联到单独的Id，SQL Server会报错，因为外键的复合字段必须对应目标表的复合唯一键，不能只对应单个主键字段。
2. 现有数据不符合约束：如果表已经有数据，比如某条记录的ParentFolderId存在，但对应的父记录UserId和当前记录不一样，或者ParentFolderId根本不存在，这时候加约束肯定报错。你可以先查一下问题数据：

-- 找出不符合要求的记录
SELECT f.*
FROM Folders f
LEFT JOIN Folders parent ON f.ParentFolderId = parent.Id
WHERE f.ParentFolderId IS NOT NULL 
  AND (parent.Id IS NULL OR parent.UserId != f.UserId);

查到后先修正这些数据（比如改ParentFolderId、调整UserId或者删掉违规记录），再重新加约束。

三、SQL Server里创建复合候选键的两种方法 #

复合候选键其实就是复合唯一约束（候选键的定义就是唯一且非空的键，主键是特殊的候选键），有两种常用方式：

1. 创建表的时候直接定义 #

比如新建Folders表时就加上：

CREATE TABLE Folders (
    Id INT PRIMARY KEY IDENTITY(1,1),
    UserId INT NOT NULL,
    FolderName NVARCHAR(100) NOT NULL,
    ParentFolderId INT NULL,
    -- 这里定义复合候选键，比如保证同一用户下文件夹名不重复
    CONSTRAINT UQ_Folders_User_FolderName UNIQUE (UserId, FolderName),
    -- 顺便把自引用外键也加上
    CONSTRAINT FK_Folders_Parent FOREIGN KEY (ParentFolderId, UserId) REFERENCES Folders(Id, UserId)
);

2. 给已存在的表添加 #

用ALTER TABLE语句就行，比如给现有表加一个(UserId, FolderName)的复合候选键：

ALTER TABLE Folders
ADD CONSTRAINT UQ_Folders_Composite UNIQUE (UserId, FolderName);

另外也可以用创建唯一索引的方式，效果差不多（唯一约束会自动生成唯一索引，而唯一索引可以设置更多索引参数，比如是否非聚集）：

CREATE UNIQUE NONCLUSTERED INDEX IX_Folders_User_Folder
ON Folders (UserId, FolderName);

如果是要作为外键的引用目标，更推荐用UNIQUE CONSTRAINT，因为它更明确地表示这是一个候选键，可读性更好。

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