Creating realtionship between tables not in the same DB

Question

Post reply

Creating realtionship between tables not in the same DB

Airolg Icsadjag

SSC Eights!

Points: 961
More actions
August 24, 2005 at 1:18 am

#88776

good morning guys!
i tried to create a one-to-many relationship between two tables not in the same database like this:
CREATE TABLE [TBL1] (...
CONSTRAINT [PK_TBL1] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_TBL1_TBL2] FOREIGN KEY
(
[ID]
) REFERENCES [OtherDB.dbo.TBL2] (
[TBL1_ID]
)
) ON [PRIMARY]
GO
but SQL Server can't create this constratint.
can you help me to solve my problem?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

SQL server does not support this. You can implement the constarint by triggers.

(Sysbase has this feature)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Peter is correct. No relationships across dbs.

If you want to use a trigger, I'd suggest using a view in case you do something else with the other db. Then you only have to change the view to fix the issues. Otherwise you might be making changes in lots of places.

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 3

Hello guys! Thanks for yours answers!

You wrote about views. Can I define a constraint between a table and a view or I would have to create a view according to TBL2 and a trigger for TBL1 using TBL2_view?

Best regards,

Gloria

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Just for general information ...

[Sybase DBA mode "ON"]

You can do it in Sybase, but then the databases are 'joined at the hip' for all restore/DR operations (so you had better back them up that way !!! ). Also, if they are ever separated, there's a boatload of system table edits to remove things so the databases is functions. This is a case of a 'good' idea gone very, very 'bad' !!! Oh, one other thing is that even Sybase support has trouble with this type of 'database divorce !!!

[Sybase DBA mode "ON"]

This is one case where Microsoft was right in not including a 'feature' in thier code base.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

peterhe SSChampion Points: 11469 More actions · Answer 5

It's true. Every technology or solution has its pros and cons.

If you use this feature in Sybase, it means you need to manage related dbs as a group.

A gun can be used to kill enemy, or kill yourself if your are not careful.

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 6

Thanks for all, guys!

Then I'm going to create a trigger. This is the simplier solution, isn't it?

All the best,

Gloria