你可以有一个外键到SQLServer 2k5的链接服务器表的视图？ [英] Can you have a Foreign Key onto a View of a Linked Server table in SQLServer 2k5?

问题描述

我有一个连接服务器到其他地方的另一个数据库的SQLServer。我创建了一个链接的服务器上的视图

  create view vw_foo as 
 select 
 [id]， 
 [name] 
 from LINKEDSERVER.RemoteDatabase.dbo.tbl_bar 
  

d喜欢以下内容：

pre code $ alter table [baz]
add foo_id int not null
go $
$ b alter table [baz] with check
add constraint [fk1_baz_to_foo]
foreign key（[foo_id]）
references [dbo]。[vw_foo]（[id] ）
go

但是会产生错误：外键'fk1_baz_to_foo'引用对象' dbo.vw_foo，这不是一个用户表。

如果我尝试把外键直接放在表上，使用下面的

  alter table [baz] with check 
 add constraint [fk1_baz_to_bar] 
外键（[foo_id]）
引用LINKEDSERVER。 RemoteDatabase.dbo.tbl_bar（[id]）
  

然后我得到以下错误：

对象名称LINKEDSERVER.RemoteDatabase.dbo.tbl_bar包含超过最大数量的前缀。

有什么办法可以达到同样的效果吗？

其他解决方案：

1. 将数据从源（视图的位置）复制到与您要添加密钥的表相同的服务器。您可以每小时，每天或其他方式执行此操作，具体取决于源数据更改的频率。


2. 在源表上添加一个触发器，将任何更改推送到您的本地副本。这基本上与＃1是一样的，但是直接进行了一些变化


3. 在表中添加一个INSTEAD OF触发器，通过从链接服务器中选择手动检查外键约束，比较你试图插入/更新的值，如果不匹配，你可以拒绝更改。
   

I have a SQLServer with a linked server onto another database somewhere else. I have created a view on that linked server

create view vw_foo as
select
[id],
[name]
from LINKEDSERVER.RemoteDatabase.dbo.tbl_bar

I'd like to to the following

alter table [baz] 
add foo_id int not null
go

alter table [baz] with check 
add constraint [fk1_baz_to_foo] 
  foreign key([foo_id]) 
  references [dbo].[vw_foo] ([id])
go

But that generates the error: "Foreign key 'fk1_baz_to_foo' references object 'dbo.vw_foo' which is not a user table."

If I try and put the foreign key directly onto the table using the following

alter table [baz] with check 
add constraint [fk1_baz_to_bar] 
  foreign key([foo_id]) 
  references LINKEDSERVER.RemoteDatabase.dbo.tbl_bar ([id])

Then I get the following error:

The object name 'LINKEDSERVER.RemoteDatabase.dbo.tbl_bar' contains more than the maximum number of prefixes. The maximum is 2.

Is there any way I can achieve the same effect?

解决方案

Foreign keys can't be connected to non-local objects - they have to reference local tables. You get the "maximum number of prefixes" error because you're referencing the table with a 4-part name (LinkedServer.Database.Schema.Object), and a local object would only have a 3-part name.

Other solutions :

1. Replicate the data from the source (the location of the view) to the same server as the table you're trying to add the key on. You can do this hourly, daily, or whatever, depending on how often the source data changes.
2. Add a trigger on the source table to push any changes to your local copy. This would essentially be the same as #1, but with immediate population of changes
3. Add an INSTEAD OF" trigger to your table that manually checks the foreign key constraint by selecting from the linked server and comparing the value you're trying to INSERT/UPDATE. If it doesn't match, you can reject the change.

这篇关于你可以有一个外键到SQLServer 2k5的链接服务器表的视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！