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
To add additional columns in the set of columns forming the composite key, you can use the alter-add command. And to delete a column from the set of columns combined together to form the composite key, you can use the alter-drop command.
A Composite key is a Candidate key or Primary key that consists of more than one attribute. Sometimes it is possible that no single attribute will have the property to uniquely identify tuples in a table. In such cases, we can use a group of attributes to guarantee uniqueness.
You can also create a Composite Unique Key consisting of two or more fields. To do that we need to apply the Unique Constraint on the table level. In the following example, we create a unique key consisting of FirstName & LastName . The FirstName & LastName themselves can contain duplicate values.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With