Referential integrity across linked servers 1

[1DMF]

Hi,

I'm trying to implement an FK constraint to a linked server table.

I have tried using the dot prefix notation, but I keep getting...

Foreign key 'FK_Is_Novated' references invalid table 'SERVER.DATABASE.dbo.TABLE'

So I created a View of the linked table, but it doesn't like that either whether I try to use the GUI to create the FK or DDL.

Is it possible?

The only thing I can think of now to implement the referential integrity is by adding a check constraint that does a lookup against the linked server / table for a valid record.

Is this the correct way of adding referential integrity across linked servers?

Your input is appreciated.

1DMF.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[SQLBill]

My understanding is a FK can only reference a table within the same database. That's why the syntax is Foreign Key References schema_name.referenced_table_name. There's no option for database name.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

Yes, it does seem that way, and to make matters worse it seems you can't use correlated sub queries for a check constraint in MS SQL.

Well, unless my other thread responses tell me different!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music