January 27, 2012 at 8:22 am
Hello All,
I have tableA that needs foreign key reference to a synonym(TableB). The actual tableB resides in a seperate server.
So I cannot have foreign key reference to this tableB directly either using Synonym or cross-database reference.
I think only option I have is to create a function and refer that function in foreign key reference (correct me if I am wrong)
So now I am trying to create a function for this but on compiling the function, I get the error
"Parameters were not supplied for the function". If I create the function with parameter, then how do I reference the function in foreign key reference?
Here is my function:
CREATE FUNCTION [dbo].[GetEmployees]
(
)
RETURNS Table
AS
RETURN
with DataList as
(
SELECT Id
FROM someServer.dbo.Employees
)
SELECT * FROM DataList WHERE Id <> 0
GO
SELECT * FROM dbo.GetEmployees
January 27, 2012 at 8:30 am
Foreign keys can only reference tables in the same database. They can't reference functions, views, et al.
You might be able to compensate for that with a trigger on the table you want to reference, but cross-server triggers are almost always a really, really bad idea.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2012 at 8:57 am
Besides triggers, I can also use function. right?
January 27, 2012 at 9:58 am
YOu can't use a function as a FK. You could use a function in a CHECK constraint.
I'd recommend against a cross-server constraint like this. I'd verify the data exists in the other server as part of the application. Doing a check across a linked server is just asking for performance issues. What happens if the linked server is down for some reason?
I'd look to see if you might be better off replicating that table (or tables) instead of goign across the linked server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 30, 2012 at 6:31 am
ramadesai108 (1/27/2012)
Besides triggers, I can also use function. right?
I don't think so. How would you use a function for this?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2012 at 6:49 am
GSquared (1/30/2012)
ramadesai108 (1/27/2012)
Besides triggers, I can also use function. right?I don't think so. How would you use a function for this?
As I said earlier you can't use a function in the creation of a foreign key so I think what the OP means is that they can use a function in a check constraint. This is possible but means that you no longer have a set-based operation because the function will be evaluated once per row and slow down the insert. In my opinion you'd be better off doing the evaluation before you do an insert or update into that table. The other thing you want to keep in mind is that you need to enforce the other side of the foreign key as well, meaning that you need to make sure you don't allow a row in the referenced table to be deleted if there is a related row in the referencing table and that you don't allow the foreign key column to be updated without cascading the update to the referencing table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 30, 2012 at 6:51 am
Jack Corbett (1/30/2012)
GSquared (1/30/2012)
ramadesai108 (1/27/2012)
Besides triggers, I can also use function. right?I don't think so. How would you use a function for this?
As I said earlier you can't use a function in the creation of a foreign key so I think what the OP means is that they can use a function in a check constraint. This is possible but means that you no longer have a set-based operation because the function will be evaluated once per row and slow down the insert. In my opinion you'd be better off doing the evaluation before you do an insert or update into that table. The other thing you want to keep in mind is that you need to enforce the other side of the foreign key as well, meaning that you need to make sure you don't allow a row in the referenced table to be deleted if there is a related row in the referencing table and that you don't allow the foreign key column to be updated without cascading the update to the referencing table.
That's what I was thinking too. But that's only half of ref integrity, even if performance is okay. That's a really bad idea. So I was wondering if he had a better idea that you and I just haven't thought of. Probably not, but worth asking.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 30, 2012 at 6:56 am
As already pointed out in this thread there is not such a thing as cross-database referential integrity therefore you cannot have a real FK pointing to a PK in a different database - no matter if in the same server or not.
What you can do is to programatically manage referential integrity - in this case you have to code everything you need, a function would do it. You can pass the value of the pseudo-PK you want to check on the remote database and get a True or False answer telling you if such a row exists or not based on a select statement on such value.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 31, 2012 at 6:33 am
PaulB-TheOneAndOnly (1/30/2012)
As already pointed out in this thread there is not such a thing as cross-database referential integrity therefore you cannot have a real FK pointing to a PK in a different database - no matter if in the same server or not.What you can do is to programatically manage referential integrity - in this case you have to code everything you need, a function would do it. You can pass the value of the pseudo-PK you want to check on the remote database and get a True or False answer telling you if such a row exists or not based on a select statement on such value.
Jack's and my point is that something like that would give you half of referential integrity. You also need something on the other side that protects rows from deletion if there are dependent rows on the second server. Or cascade-delete them, at the very least.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2012 at 8:07 am
GSquared (1/31/2012)
Jack's and my point is that something like that would give you half of referential integrity. You also need something on the other side that protects rows from deletion if there are dependent rows on the second server. Or cascade-delete them, at the very least.
I couldn't agree more.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply