Wednesday, March 28, 2012

Relationships

Is it possible to have 2 different databases and create relationships inbetween the two? I am thinking about way down the road and the size of the databases and I believe that I need to split my data into multiple databases to keep from running into the size limitations.

Davids Learning

By "relationship", do you mean "foreign key"? If so, the answer is "No, you cannot create a foriegn key relationship across databases." What you can do though to implement the same functionality is to create a triggers on both tables that enforce the "relationship".

CREATE TRIGGER MyTrigger ON dbo.ChildTable AFTER INSERT, UPDATE

AS

IF EXISTS (

SELECT * FROM OtherDatabase.dbo.ParentTable p WHERE p.PrimaryKey NOT IN ( SELECT ChildFk FROM inserted )

)

BEGIN

RAISERROR ('You are attempting to insert/update like a heathen. Stop at once!', 16, 1)

ROLLBACK TRAN

END

GO

No comments:

Post a Comment