Maintaining Cross-Database Referential Integrity

It is often convenient to house your master tables in a separate database from application specific databases so that primary keys are copasetic and multiple databases have access to the same lookups.

Common examples would include employees, counties, regions and other lookup tables.

Once these tables are in a separate database however it is no longer possible to simply drag-and-drop a relationship between them to maintain referential integrity.

Here's a Solution
For this example we'll use an Observation table, tied to a master list of Sex, that's right, Sex (we could call it Gender to be all PC, but these are animals, not Pat in the Personnel Department.)

Step 1:
We create a new view called PIC_Sex in our Application Database by querying the table PIC_Sex in our Master Database:

CREATE VIEW dbo.PIC_Sex AS
SELECT SexID, Sex, Rank
FROM Master.dbo.PIC_Sex
ORDER BY Rank

Yes, that's right, Male and Female! Slugs are awesome.

Step 2:
We create a new user-defined function xdf_ValidateSex to validate if a given SexID exists in the view PIC_Sex.

CREATE FUNCTION [dbo].[xdf_ValidateSex] (
@SexID int
)
RETURNS int AS
BEGIN
RETURN
(
SELECT SexID FROM [dbo].[PIC_Sex] WHERE SexID = @SexID
)
END

You could make this function accept table names to make it more reusable.

Step 3:
In our Observation Table we add a constraint on the field SexID which tests against the function xdf_ValidateSex to see if the SexID exists.

Enter the following Constraint Expression:

([dbo].[xdf_ValidateSex]([SexID]) is not null)

Viola! You can now validate Sex programmatically!