Relationships and constraints across databases

(I wasn't sure which forum to pos this in. Let me know if you think there is a better one.)

I have an application that breaks down into two sets of data, Set A and Set B, for our purposes. Both Sets currently reside in the same database. Set A is a fairly static set of data, changing maybe once a week, if not less often. Set B is empty at first but it has a lot of dependencies to Set A. Items are chosen from Set A to fill Set B but never the otherway around.

Here is the problem. We continue to copy the "master" database whenever a new customer signs up. The problem is, anytime we have to update data on Set A we have to do it across multiple databases. Set B is never copied as its customer owned.

What I would like to do is create a separate database that contains Set A data only. Whenever we have a new customer, I create a new database that contains Set B only. I have done so, but the relationships and constraints cannot be done unless I use triggers and procedures.

Is there anyway to achieve what I am trying to do without using triggers and procedures?

Thanks in advance.

Hi, actually no. Cross database integrity has to be achieved through triggers.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Another alternative in 2005 would be to use schemas instead of different databases. Then all of your data would be in the same database, which would be better for your data security, easier to program etc. You could break the schemas out into filegroups if you wanted for backup purposes too.

Then you could use DRI and not have to resort to triggers (which can be really problematic to manage and keep the data in sync (though not by any means impossible.) The problem being that you need a trigger on the parent AND child table, to protect on inserts/updates of children, then for deletes/updates of parents.

|||

These circumstances are the sole purpose of triggers (but not always sp_'s).

There is only one alternative but has more tradeoff's than it's worth. Dump everything into one table but you've already discovered this.

Adamus