Can you have a Foreign Key onto a View of a Linked Server table in SQLServer 2k5?

Question

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 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?

Answer 1

No, foreign keys have to be made against user tables. Have you tried the below?

alter table [baz] with check 
add constraint [fk1_baz_to_foo] 
FOREIGN KEY([foo_id]) 
references 
   LINKEDSERVER.RemoteDatabase.dbo.tbl_bar([id])
go

Answer 2

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.

Answer 3

You can, but you have to use some dynamic SQL trickery to make it happen.

declare @cmd VARCHAR(4000)
SET @cmd = 'Use YourDatabase
ALTER TABLE YourTable
DROP CONSTRAINT YourConstraint'

exec YourServer.master.dbo.sp_executesql @SQL