DRI - Multiple Databases
sc...@eguires.net
The answer to this question will determine how I set up my next database and will provide firewpower in the argument for compartmentalizing databases.
Thanks,
Scott Eguires
Joel Askey
Great suggestion - I took the liberty of forwarding it to the SQL Server
team at sql...@microsoft.com.
I would have use for this also, as would many of us. For now, we have to
rely on triggers to support cross-DB referential integrity.
<sc...@eguires.net> wrote in message news:uRUgZdr0CHA.1900@TK2MSFTNGP10...
BP Margolin
Personally I think cross-database constraints is a terrible idea :-(
What happens when someone has a cross-database DRI referential integrity
constraint and someone detaches one of the two databases? Similarly, how
does one restore a database with a cross-database constraint?
Do you want SQL Server to disallow detaching and restoring of the databases
that have cross-database DRI constraints? If so, then one is forcing the two
databases to be "forever linked", and one might as well then just merge the
two databases into one.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Joel Askey" <joel....@shs.com> wrote in message
news:epiq3xs0CHA.1928@TK2MSFTNGP10...
Joe Celko
databases that have cross-database DRI constraints? <<
Worse yet, they do detach, operate independently and then re-connect.
Now it is time to fire off the DRI actions and bring them back into
synch. How do you do that correctly? Is it even possible?
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
BP Margolin
> Is it even possible?
Not unless Microsoft changes the entire concept of detach / attach and
backup / restore.
The whole concept of either detach / attach or backup / restore is that the
database can function on any server, so it must be self contained. (I'm
conveniently overlooking that the security structure of SQL Server does not
actually make a database completely self-contained.)
To allow your scenario, linked server connections have to be established
between the two servers which would require code modifications so that the
data on the remote server is addressable.
BPM
"Joe Celko" <71062...@compuserve.com> wrote in message
news:uEUg0Xv0CHA.2904@TK2MSFTNGP09...
Joel Askey
I'm thinking of this in a specific application, which is secondary data.
Data like reports or financial charges, for instance,
are often completely generated from the results of other tables.
Data like this can be regenerated ( with proper design ) as long as the
primary data is backed up and restored. In this case,
you can isolate locking between the reports/financial database and the
primary data database for greater concurrency.
In this situation, it would be nice to have referential constraints, so that
if a financial account was deleted on the primary database,
then one could cascade deletes to the reporting/financial database, for one
example.
I would fully accept the limitations that a "background" process would run
to catch up or restore parts or all of the reporting database.
"BP Margolin" <bpm...@attglobal.net> wrote in message
news:#O5l2Iv0CHA.2076@TK2MSFTNGP10...
BP Margolin
I understand the context in which you would like to implement cross-database
DRI. The problem is that there are lots of things that would be nice to have
in a restricted context, but it's just to difficult to put into SQL Server
because either (a) SQL Server can't know the context in which you want this,
or (b) some idiot is going to try to use the feature outside of the
"restricted context", or (c) the architectural issues just ain't worth it.
To "flesh" this out a bit ... years back, one of the first requests I made
to Microsoft was to carry over constraints when doing:
select *
into new_table
from old_table
As I'm sure you know, doing the above doesn't carry over primary key
constraints, unique constraints, check constraints, etc.
Roy Harvey very nicely and gently informed me that I was a frigging idiot
(obviously not his words ... remember, I said he phrased it nicely and
gently) by pointing out that the INTO option doesn't have to apply only in
the context of one table to one table, but it could also apply in constructs
such as:
select ...
into new_table
from old_table_1 as n1
join old_table_1 as n2
on (...)
In such situations, it makes no sense to carry over primary key constraints,
unique constraints, check constraints, etc. because SQL Server, most
probably, can't figure out what constraints should be applied.
Yes ... SQL Server could carry over constraints when the INTO option is a
one-to-one table construct, and not carry over constraints when used in a
multiple table construct, but is it really worth it.
I agreed with Roy back then, and I still agree with Roy today, that having
SQL Server do different things in this case is just not a good idea.
I would suggest to you that the inherent architectural problems of
cross-database DRI are just not worth it.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Joel Askey" <joel....@shs.com> wrote in message
news:eadnDOw0CHA.1288@TK2MSFTNGP11...
Scott Eguires
You make some very valid points however it would be nice
for the option to be there. Just because something is
there doesn't mean it has to be used. In answer to the
issue of attaching/detaching you could simply remove the
constraints when attaching/detaching operations arise.
Maybe I am missing something here and by no means am I an
expert but I still feel the benefits largely outweigh the
issues you brought up - at least in my case.
Scott Eguires
>.
>
Uri Dimant
I agree with BP in this case
It is nice to have some things in a restricted context, but it's just to
difficult to put into SQL Server
Suppose we established DRI accross two tables different databases
TableA and TableB
So ,as BP said "some idiot (may not) is going to drop/delete/truncate"
TableB
We will get :
Server: Msg 3726, Level 16, State 1, Line 1
Could not drop object 'TableB' because it is referenced by a FOREIGN KEY
constraint.
It 's ok if this happened in single database and you are fammilar with
database but what will be happen to user that doesnt know about the
structure of the databases or some error we'll get at client application?
Will client app connect to other database in order to check DRI ?
I think it is uncomfortable.
PS.What will be really good reason to establish DRI accross databases?
Scott Eguires <sc...@eguires.net> wrote in message
news:039e01c2d313$733a11a0$a201...@phx.gbl...
Scott Eguires
full control of our database. Also the purpose is so I
can break up the database into logical sections (in effect
compartmentalizing it for ease of maintenance and future
expandability). I understand my request is maybe a little
over the top and likely only applies to a small percentage
of people but I would still like to have that option.
Again just because the option exists does not mean it has
to be implemented.
Thanks for all your feedback
Scott Eguires
>.
>
Joel Askey
request that are definitely problematic in most mainstream applications.
What I think this really implies is a more loosely coupled master-slave type
relationship, in which the secondary database "enforces" linkage to a
primary
database by removing orphans.
However, I certainly acknowledge that there are ( as in most applications )
more ways than one to skin this particular feline. Notification Services on
the master database could be used to signal a process that cleans up the
orphans, also.
"BP Margolin" <bpm...@attglobal.net> wrote in message
news:Ou7fxqw0CHA.1628@TK2MSFTNGP10...
Mike Sherrill
wrote:
>I'm thinking of this in a specific application, which is secondary data.
>Data like reports or financial charges, for instance,
>are often completely generated from the results of other tables.
>
>Data like this can be regenerated ( with proper design ) as long as the
>primary data is backed up and restored.
I'd suggest you not think of this "secondary data" as a database.
Think of it as a big whoppin' report.
--
Mike Sherrill
Information Management Systems