April 18, 2008 at 11:16 pm
Hi,
We're having a few linked servers in our company. some tables in one of the linked servers include columns (better saying, foreign keys) of tables in another server. I wonder if there's a way to create a foreign key referencing one columns in another server. That is, suppose there's Column A in table A in Server A which references Column B in table B in server B, is there a way to create column A as foreign key referencing column B?
Any idea is appreciated.
April 18, 2008 at 11:28 pm
you could do this with triggers. however it would bring the perf of your system to a halt.
---------------------------------------
elsasoft.org
April 18, 2008 at 11:36 pm
Thanks for the reply Jazemine
However, Could you plz explain more? Maybe I do it for a short period of time to keep the performance high
April 19, 2008 at 12:16 am
you could have a look at this:
---------------------------------------
elsasoft.org
April 19, 2008 at 9:38 am
When implementing "remote foreign keys" keep in mind that if the other instance/db is down (or in maintenance), your instance will suffer as well !
Maybe you'll be better off replicating the strictly needed tables to a db on your own instance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2008 at 10:48 pm
Thanks for the comments Jazemine and ALZDBA,
I'll consider the situation to make a better choice
April 21, 2008 at 1:37 am
Hi again Jazemine
I was just trying the remote key and thought you may be interested in result:
though it sounds a nice tool at the beginning, since it's in its beta version, it gives too many bugs to let try its main features.
By the way, do you know any similar tools?
April 21, 2008 at 7:54 am
no, I don't. I've never used remote-key either. I just heard of it somewhere.
sorry it didn't work for you, but perhaps it's for the best, because enforcing RI across remote servers is really not a good idea.
---------------------------------------
elsasoft.org
April 21, 2008 at 10:31 pm
Well... I agree with you but I'm just employed in this company and have to do what they ask (of course not always 😉 )
By the way, there's also something else that can help me handle this; I was thinking of find a diagramming tools, like Enterprise Architect, ER Win, etc., which can display such foreign keys in diagram this can help me document such relations for future. Unfortunately, Enterprise Architect and ER win don't handle this well
Thanks Anyway 🙂
April 22, 2008 at 9:55 am
well, I wrote a tool called SqlSpec that will document all your FKs as well as every other thing in your database. there's more detail about it at the link in my sig.
price is peanuts compared to ERwin and similar tools. 🙂
---------------------------------------
elsasoft.org
April 22, 2008 at 10:37 pm
Does it really support foreign keys between two different databases??:w00t: Then you're life saver 😛
I'll try the trial version but it takes me time to get my company's permission to download it; would you mind sending me a diagram displaying such relationship between two DBs. If it can draw my boss's attention, we'll be one of your customers in a short time, I promise. 🙂
April 22, 2008 at 11:20 pm
sorry for the confusion - sqlspec does not support documenting FKs between different databases. It doesn't support them because SQL Server itself doesn't support them, which you already know.
I can't very well write code to document an entity that doesn't exist... 🙂
here's an example of what it does generate (view this in IE to see the diagram)
---------------------------------------
elsasoft.org
April 22, 2008 at 11:33 pm
Thanks anyway:(
🙂
April 23, 2008 at 12:09 pm
It isn't clear here what your real needs are; do you just need to look up col A on server A when loaded table b on server b and that happens in batches or somewhat infrequently or do you really, really need an active all the time enforcement? If your load to server b can be done in batches so there is a quick lookup to server a for the fk values and then it's left alone for some time, then there is less need for an active fk enforcement. Especially since there are a lot of issues with doing this across different servers. Think about loading table b on server b outside the enforced fk box and see if there is something else you can do. Can you make a solid load process that can be trusted to get the right values and not have to have the rdbms play watchdog over the values?
April 25, 2008 at 10:33 pm
Thanks for your comments magarity kerns 🙂
I convinced my boss to not enforce any fk check between different DBs; FKs are created virtually (only DB designers know that col A refers to col B in another database) and we keep it as is. However, I need a diagramming tool like Toad, ERWin, Enterprise Architect, etc. to document such FKs as a Model Diagram to be able to refer to it in future for design maintainace. Unfortunately int the tools I know, such links between two DBs cannot be displayed in diagram well (in Enterprise Architect, we can create each DB as a package and create a dependency link between two packages but it can't be shown well that which column in which table refers to which one in another package so that anyone who takes a brief look at the diagram can recognize it)
Sorry if my question was not clear; I hope I've explained it well this time 🙂
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply