Existing relationship must have related columns
Table of Contents
I was working on the Microsoft SQL Server (2008) Management Studio when I renamed some tables. After renaming I decided to edit the relationships and I got stuck with the following error
The existing relationship must have at least one pair of related columns
No matter what I did, I could not discard the screen with this message.
To reproduce the error, use the following steps but take note that you will end up in an endless loop and you will be forced to kill the SQL Server process.
-
Create table let’s call it Roles
CREATE TABLE Role ( Id INT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL );
-
Create another table let’s call this Menu
CREATE TABLE Menu ( TaskId INT NOT NULL PRIMARY KEY, TaskName VARCHAR(50) NOT NULL );
-
Add a third (junction) table and call it RoleMenu
CREATE TABLE RoleMenu ( RoleId INT NOT NULL, TaskId INT NOT NULL, CONSTRAINT pk_RoleMenu PRIMARY KEY (roleid, taskid), CONSTRAINT FK_RoleMenu_Role FOREIGN KEY(RoleID) REFERENCES Role (Id), CONSTRAINT FK_RoleMenu_Menu FOREIGN KEY(TaskId) REFERENCES Menu (TaskId) );
-
Right click on RoleMenu and select Design
-
Right click on Menu table and rename to Task and rename RoleMenu to RoleTask in the designer
-
Edit The Relationships on to reflect the change in names
-
Click on RoleMenu which is opened in design view
-
Select Table Designer, Relationships… from the menu
-
Select first relationship under Selected Relationship:
-
Click on Tables And Columns Specification (make sure to click on the elipsis button)
-
-
You get a dialog Table Menu could not be loaded. Click OK to discard the dialog.
-
Select the remaining relationship and again click on Tables And Columns Specification
-
Click on Close button to close the screen. You are now stuck with the following message
The existing relationship must have at least one pair of related columns.
You can neither Add a relationship or Close the screen. To get out of the loop, you will have to kill the SQL Server process.