请参考以下表定义...
表定义
CREATE TABLE [dbo].[Folders](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[ParentFolderId] [int] NULL
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
其他表与该表的主键列 [id]
存在外键关系。
我希望添加一个自引用的外键约束,其中父文件夹 ID 引用同一 Folders 表中另一条记录的 Id 字段,但 UserId 也必须匹配...
自引用外键约束
ALTER TABLE [dbo].[Folders] WITH CHECK ADD CONSTRAINT [FK_Folders_ParentFolder] FOREIGN KEY([UserId], [ParentFolderId])
REFERENCES [dbo].[Folders] ([UserId], [Id])
GO
ALTER TABLE [dbo].[Folders] CHECK CONSTRAINT [FK_Folders_ParentFolder]
GO
...但是我遇到了错误...
错误信息
Msg 1776, Level 16, State 0, Line 64
There are no primary or candidate keys in the referenced table 'dbo.Folders' that match the referencing column list in the foreign key 'FK_Folders_ParentFolder'.
Msg 1750, Level 16, State 0, Line 64
Could not create constraint or index. See previous errors.
Msg 4917, Level 16, State 0, Line 67
Constraint 'FK_Folders_ParentFolder' does not exist.
Msg 4916, Level 16, State 0, Line 67
Could not enable or disable the constraint. See previous errors.
我尝试给这个表添加一个复合唯一索引作为候选键,但是没有成功,我仍然得到相同的错误。
唯一索引。
CREATE UNIQUE NONCLUSTERED INDEX [IX_Folders_UserParentFolder] ON [dbo].[Folders]
(
[UserId] ASC,
[ParentFolderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
如何在
[Id]
和 [UserId]
上创建超键/候选键,以便我可以添加自引用外键?请注意,将主键从单个整数转换为组合键将破坏其他引用文件夹表的表上的外键。这些外键不需要知道 UserId。编辑:根据 Dan Guzman 的建议/评论,添加数据库约束就做到了。以下是约束定义...
ALTER TABLE [dbo].[Folders]
ADD CONSTRAINT AK_Folders_UserId UNIQUE ([UserId], [Id])
GO