Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 623
barrypicker Avatar asked Feb 24 '18 22:02

barrypicker


People also ask

How do you create a composite key in a table?

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.

Can a candidate key be composite?

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.

How do I create a composite unique key in SQL Server?

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.


1 Answers

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);
like image 65
Dan Guzman Avatar answered Oct 06 '22 01:10

Dan Guzman