Foreign Key across Databases?
-
There must be a way to do a Foreign key reference across databases in one DB server besides triggers. No?
DatabaseA.Table.Field references DatabaseB.Table.Field
-
If you need to reference data in 2 tables by FK that means those tables are parts of the same database.
Separation of them was (is) a mistake and must be fixed.
_____________
Code for TallyGenerator -
Nope... other than moving the table as Serqiy suggested (and, is actually the correct way to do it!), the only other way is by trigger...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Bah, Sybase allow it just fine. In the references clause just add DB qualifier. MS does not allow it that hardly means it is wrong. You are thinking invoices and orders types of scenarios.
But... if many databases share info in one database, then if that one goes down, then they all go down right? Well yes, but what if you have one database and that goes down.
There are ways or using databases to express ideas that are not exactly customers, invoices and orders. In those cases sharing data with triggers and such you would have to do in SQl Server is not such a good idea. I totally agree. But if the DBMS allows it directly in the FK references clause why not? MS does not grant that feature that hardly makes it a bad idea. A bad idea in SQl server I agree, but a bad idea as a blanket statment, I disagree.
Sybase allows it, SQL Server does not. Wonder about Orcle? Microsoft need to protect you from your self and you regurgitate thier lack of features it like a benifit. MS dows allow it with DPV btw. Ehhh pointless to argue it. Ok your right and I am wrong.
-
Eric, actually Invoices and Orders must be IN THE SAME TABLE, not to mention database.
Separating it is breaking of relational model, just a little bit.
Placing it into different databases - logical mistake.
_____________
Code for TallyGenerator -
How about on 2 different servers??
-
Eric,
PPl are trying to help and if you don't want to know how its done in SQL Server then don't post it here. I hope u understand this site is called SQLSERVERCENTRAL not ORACLECENTRAL OR SYBASECENTRAL.
Thanks
Sreejith
-
That what I'm dealing with. Bloody 3rd part application.
And what causes my deepest frustration.
It's funny, but in order to work with it people load the whole database across servers line-by-line. And they wonder why both servers and network are so busy.
_____________
Code for TallyGenerator -
Dare I ask???
How big are the dbs (size and rows)?
-
Fortunately it's not big. Yet.
Under 1GB all together.
But even with this amount of data it takes up to 40 seconds to update web-page. Because server is too busy with "more important" activity caused by "genius" design.
_____________
Code for TallyGenerator -
Sounds to me like it's 41 seconds too long!!!
I think you should put your foot down on this one
. -
Heh, heh.... I LOVE "genius" designs... life would be so boring if everyone did things right
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
And it actually gave me this job...
When they hit the wall they started to look for a professional.
But not when they started the project.
Now I'm watching the same situation in another project. Gonna be a good reason to discuss a pay rise...
_____________
Code for TallyGenerator -
Heh... I know what you mean... my Co hired me because they were an Oracle shop suddenly thrust into the world of SQL Server
... all they wrote was RBAR (I love that "Modenism" ) with lots and lots of useless very long winded multi-level transactions ... they even had (still have) an Oracle-like sequence table because they didn't know what an IDENTITY column was... and, they had an average 4,000 deadlocks per day (got that down to 12 a week and hit the goose egg one week), several server wide outages per day because of blocking, and dozens of 8-12 hour runs on just a couple thousand rows each to go right along with all that garbage.And, like you imply, they keep doing the same thing over and over... implement,
then think... code, then design. Pure genius 
and keeps me plenty busy. I've tried to show them the light and they get "the religion"... right up until someone gives them a ridiculous development schedule and they katow with a mighty "Yes BOSS"! Then, they turn to the users and say "Would you help us design the database"? That's when they really get into deep Kimchie.What's even better is when they outsource an SQL Server user-designed solution
and then wonder why they have performance and scalability problems 
. Gotta love those managers that try to hit a schedule mark instead of doing it right the first time 
... they're good for everyone's wallet except for the company they work for!--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Keep his phone number in your black book... it might be handy for me one day
.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply