CODEWITHSUNDEEP
sqldatabasesql-server-2008foreign-keyscross-database
TheRealKernel
TheRealKernel

Reputation: 351

SQL Foreign Key in Another Database

I am currently creating a database at work that is essentially a child of another database. When our clients get our software they get a database, lets call it MasterBase. This database contains schema of two different smaller databases put together, lets call them MasterC and MasterF.

My job is to take all the schema that pertain to MasterF and put them in their own database. The problem is there are a lot of references to tables and views in MasterC. To work around this I will add a property that will pair MasterF with MasterC so that in my .NET solution I should be able to do things like:

Table tableName.... (table being created in MasterF) References .... MasterC.dbo.tableC (table in MasterC)

However, in SQL this raises the error: MSG 1763 Cross-Database foreign key references are not supported.

I have read the most common work around is to add a trigger, but seeings how this is not that safe and there are MANY instances of this I was wondering if there was another way, perhaps a stored procedure or something of the sort.

If a trigger is really the best/only way and I MUST add a trigger to every table that has this problem, how would I go about writing this trigger, I know a little SQL but am hardly proficient.

Please Help!

Upvotes: 2

Views: 4364

Answers (1)

Chains
Chains

Reputation: 13157

  • Triggers (as you mention)...
  • Database Partitioning...
  • Duplicate the tables from MasterC (are they transactional? How often are they updated? Does MasterC need the updates? If so, can you allow for the copies to be updated every 24-hours, etc. via a "Job"?)

Per your last comment about individual builds, I guess I'd say that the threshold for when a client "needs" both is when they...well, "need" both. Sounds almost like you need a 3rd schema, for tables in MasterC that do NOT reference MasterF, for those clients you're categorizing as not needing both.

BTW -- this is all about referential integrity, yes? There are other strategies you could employ toward that goal, besides 'relationships'.

EDIT

CREATE TRIGGER myTrigger ON myTable
AFTER INSERT
AS
IF NOT EXISTS (SELECT * FROM OtherDatabase.otherschema.othertable F
           JOIN inserted AS i 
           ON F.KeyYouAreLookingFor = i.KeyYouHave)
BEGIN
RAISERROR ('Lookup Value Not Found -- Insert Failed', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

Upvotes: 2

Related Questions