SQL Server Management Studio 不允许我为多个主键创建多个外键 [英] SQL Server Management Studio does NOT let me create multiple foreign keys to multiple primary keys

问题描述

我有一个表 ApplicationRegion 与 Translation 一对多关系.

I have a table ApplicationRegion in a one-to-many relationship to Translation.

我想将 FK 从 Translation.appname/isocode 设置为 ApplicationRegion.appname/isocode

I want to set FK's from Translation.appname/isocode to ApplicationRegion.appname/isocode

但是正如您从屏幕截图中看到的那样，这不起作用.

But that did not work as you can see from the screenshot.

当 FK 配置窗口打开时，左侧/右侧显示 3 列

When the FK configure window opens 3 columns are shown on the left/right side

appname
isocode
resourcekey

然后我选择作为父表 ApplicationRegion 并从 FK 设置中删除资源键列.

Then I chose as parent table ApplicationRegion and removed the resource key column from the FK setting.

然后单击确定"，但随后出现了您在屏幕截图中看到的错误.

And clicked OK but then I got the error you see on the screenshot.

最后我用那个变通办法让它工作了，我想知道为什么我必须使用这个变通办法?

Finally I made it work with that workaround and I would like to know why I had to use this workaround?

1. 从 Translation.ResourceKey 列中删除 PK
2. 打开 FK 配置窗口
3. 现在只有 2 列显示为外键
4. 我点击确定
5. 现在我再次将 PK 添加到 Translation.ResouceKey 列
6. 我向所有 3 个表添加了测试数据，一切正常.

为什么要采用这种解决方法?

WHY this workaround?

更新

哈哈……

推荐答案

我想您一定在 SSMS 中遇到了一个奇怪的故障.我能够使用 SSMS 2014 创建您的架构而没有任何错误.添加新 FK 时，它确实预先填充了三个复合主键列.在我开始在 FK 中添加两列之前，我很小心地确保它们都被清空了.也许 SSMS 认为其中一个空白行中仍有数据.

I think you must have hit a weird glitch in SSMS. I was able to create your schema using SSMS 2014 without any errors. It did pre-fill the three composite primary key columns when adding the new FK. I was careful to make sure they were all blanked out before I started to add the two columns in the FK. Maybe SSMS thought one of the blank rows still had data in it.

再想一想，SSMS 可以缓存编辑表时所做的任何更改.例如，如果您要修改两个表并同时打开两个编辑窗口.然后您在一个窗口中更改 PK，然后尝试在第二个窗口中引用它，它会出错，因为它在第一次打开窗口时缓存了第一个表的架构.

Just had one more thought, SSMS is know for caching any changes that are made when editing a table. For example, if you go to modify two tables and have both edit windows open. Then you change the PK in one window and then try to reference it in the second window, it will error because it has cached what the schema was for the first table when the window was first opened.

这是我生成的 DDL:

Here is my generated DDL:

CREATE TABLE [dbo].[AppRegion](
    [appname] [nvarchar](50) NOT NULL,
    [isocode] [char](5) NOT NULL,
 CONSTRAINT [PK_AppRegion] PRIMARY KEY CLUSTERED 
(
    [appname] ASC,
    [isocode] ASC
)
) ON [PRIMARY]

CREATE TABLE [dbo].[Translation](
    [ResourceKey] [nvarchar](128) NOT NULL,
    [appname] [nvarchar](50) NOT NULL,
    [isocode] [char](5) NOT NULL,
    [text] [nvarchar](400) NULL,
 CONSTRAINT [PK_Translation] PRIMARY KEY CLUSTERED 
(
    [ResourceKey] ASC,
    [appname] ASC,
    [isocode] ASC
)
) ON [PRIMARY]

ALTER TABLE [dbo].[Translation]   ADD  CONSTRAINT [FK_Translation_AppRegion] FOREIGN KEY([appname], [isocode])
REFERENCES [dbo].[AppRegion] ([appname], [isocode])

这篇关于SQL Server Management Studio 不允许我为多个主键创建多个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！