title: How do I create a composite candidate key for a table in SQL Server?
body: <p>Please consider the following table definition...</p>
  
  <p><strong>Table Definition</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>Other tables have a foreign key relationship to the primary key column of this table <code>[id]</code>.</p>
  
  <p>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...</p>
  
  <p><strong>Self Referencing Foreign Key Constraint</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>... but I am getting errors...</p>
  
  <p><strong>Errors</strong></p>
  
  <pre><code>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.
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>Unique Index</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>How do I create a superkey/candidate key on <code>[Id]</code> and <code>[UserId]</code> 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.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Per suggestion/Comments from Dan Guzman adding a database constraint did the trick.  Here is the definition of the constraint...</p>
  
  <p><strong>Constraint Definition</strong></p>
  
  <pre><code>ALTER TABLE [dbo].[Folders]
  ADD CONSTRAINT AK_Folders_UserId UNIQUE ([UserId], [Id])
  GO
  </code></pre>