How do I create a composite candidate key for a table in SQL Server?

Question

Please consider the following table definition...

Table Definition

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

Other tables have a foreign key relationship to the primary key column of this table [id].

I wish to add a self referencing foreign key constraint where the parent folder id refers to another record's Id field in the same Folders table but the UserId also must match...

Self Referencing Foreign Key Constraint

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

... but I am getting errors...

Errors

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.

I have attempted to added a composite unique index to the the table to provide a candidate key but this did not work, I still get the same error.

Unique Index

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

How do I create a superkey/candidate key on [Id] and [UserId] such that I can add the self referencing foreign key? Please keep in mind converting the primary key from a single integer to composite key will disrupt the foreign keys on other tables referencing the Folders table. These foreign keys do not need to be aware of UserId.

EDIT:

Per suggestion/Comments from Dan Guzman adding a database constraint did the trick. Here is the definition of the constraint...

Constraint Definition

ALTER TABLE [dbo].[Folders]
ADD CONSTRAINT AK_Folders_UserId UNIQUE ([UserId], [Id])
GO

Depilate

Create a UNIQUE constraint (or index) on the referenced columns, UserId and Id. – Tackling

Thanks Dan, add an answer and I will award points... – Depilate

Answer 1

The columns referenced by the foreign key columns must be the primary key, a unique constraint, or a unique index. I suggest a unique constraint here:

ALTER TABLE dbo.Folders
ADD CONSTRAINT UQ_Folders_UserId_Id UNIQUE(UserId, Id);

Tackling