Existing relationship must have related columns

SQL Server

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)

      alt=“Add table relationship column specification”

  • 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.

    alt=“The existing relationship must have at least one pair of related columns image”

    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.