Cross database integrity

Is it possible to create foreign key between to distinct SQL databases ?
Database A contains table [customers] (primary key : customer_id)
Database B contains table [invoices] (each invoice should be linked to
a customer. Foreign key : customer_id)
I would like to create an integrity constraint between
[invoices].[customer_id] and [customers].[customer_id], despite the fact
that [invoices] et [customers] tables are in SEPARATE databases.
Is it possible to do that ?
Thanks in advance
Tom
The short answer is no. The more complex answer is: Yes, you would have to
enforce cross database constraint integrity using a Trigger. There are
security and other issues that will also have to be addressed.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tom McLeesh" <tom.mcleesh@.gmail.com> wrote in message
news:u7%23a7148GHA.4572@.TK2MSFTNGP02.phx.gbl...
> Is it possible to create foreign key between to distinct SQL databases ?
> Database A contains table [customers] (primary key : customer_id)
> Database B contains table [invoices] (each invoice should be linked to a
> customer. Foreign key : customer_id)
> I would like to create an integrity constraint between
> [invoices].[customer_id] and [customers].[customer_id], despite the fact
> that [invoices] et [customers] tables are in SEPARATE databases.
> Is it possible to do that ?
> Thanks in advance
> Tom
|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tom McLeesh" <tom.mcleesh@.gmail.com> wrote in message
news:u7%23a7148GHA.4572@.TK2MSFTNGP02.phx.gbl...
> Is it possible to create foreign key between to distinct SQL databases ?
> Database A contains table [customers] (primary key : customer_id)
> Database B contains table [invoices] (each invoice should be linked to a
> customer. Foreign key : customer_id)
> I would like to create an integrity constraint between
> [invoices].[customer_id] and [customers].[customer_id], despite the fact
> that [invoices] et [customers] tables are in SEPARATE databases.
> Is it possible to do that ?
> Thanks in advance
> Tom

Cross database integrity

Is it possible to create foreign key between to distinct SQL databases ?
Database A contains table [customers] (primary key : customer_id)
Database B contains table [invoices] (each invoice should be linked to
a customer. Foreign key : customer_id)
I would like to create an integrity constraint between
[invoices].[customer_id] and [customers].[customer_id], despite the fact
that [invoices] et [customers] tables are in SEPARATE databases.
Is it possible to do that ?
Thanks in advance
Tom
Tom
> Is it possible to create foreign key between to distinct SQL databases ?
No
"Tom McLeesh" <tom.mcleesh@.gmail.com> wrote in message
news:u2njv248GHA.4572@.TK2MSFTNGP02.phx.gbl...
> Is it possible to create foreign key between to distinct SQL databases ?
> Database A contains table [customers] (primary key : customer_id)
> Database B contains table [invoices] (each invoice should be linked to a
> customer. Foreign key : customer_id)
> I would like to create an integrity constraint between
> [invoices].[customer_id] and [customers].[customer_id], despite the fact
> that [invoices] et [customers] tables are in SEPARATE databases.
> Is it possible to do that ?
> Thanks in advance
> Tom
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eh1pb548GHA.4288@.TK2MSFTNGP02.phx.gbl...
> Tom
> No
>
And a desire to implement referential integrity between databases usually
indicates that you shouldn't be using different databases.
Perhaps multiple schemas in a single database?
David
|||On Thu, 19 Oct 2006 16:54:00 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:

>Tom
>No
But it would be possible to write triggers on the tables in both
databases to enforce the relationship.
Roy Harvey
Beacon Falls, CT
|||>> Is it possible to create foreign key between to distinct SQL databases ?
No. Triggers are the usually suggested workaround.
Anith

Cross database integrity

Is it possible to create foreign key between to distinct SQL databases ?
Database A contains table [customers] (primary key : customer_id)
Database B contains table [invoices] (each invoice should be linked to
a customer. Foreign key : customer_id)
I would like to create an integrity constraint between
[invoices].[customer_id] and [customers].[customer_id], desp
ite the fact
that [invoices] et [customers] tables are in SEPARATE databases.
Is it possible to do that ?
Thanks in advance
TomTom
> Is it possible to create foreign key between to distinct SQL databases ?
No
"Tom McLeesh" <tom.mcleesh@.gmail.com> wrote in message
news:u2njv248GHA.4572@.TK2MSFTNGP02.phx.gbl...
> Is it possible to create foreign key between to distinct SQL databases ?
> Database A contains table [customers] (primary key : customer_id)
> Database B contains table [invoices] (each invoice should be linked t
o a
> customer. Foreign key : customer_id)
> I would like to create an integrity constraint between
> [invoices].[customer_id] and [customers].[customer_id], de
spite the fact
> that [invoices] et [customers] tables are in SEPARATE databases.
> Is it possible to do that ?
> Thanks in advance
> Tom|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eh1pb548GHA.4288@.TK2MSFTNGP02.phx.gbl...
> Tom
> No
>
And a desire to implement referential integrity between databases usually
indicates that you shouldn't be using different databases.
Perhaps multiple schemas in a single database?
David|||On Thu, 19 Oct 2006 16:54:00 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:

>Tom
>No
But it would be possible to write triggers on the tables in both
databases to enforce the relationship.
Roy Harvey
Beacon Falls, CT|||>> Is it possible to create foreign key between to distinct SQL databases ?
No. Triggers are the usually suggested workaround.
Anith

Cross database integrity

Is it possible to create foreign key between to distinct SQL databases ?
Database A contains table [customers] (primary key : customer_id)
Database B contains table [invoices] (each invoice should be linked to
a customer. Foreign key : customer_id)
I would like to create an integrity constraint between
[invoices].[customer_id] and [customers].[customer_id], despite the fact
that [invoices] et [customers] tables are in SEPARATE databases.
Is it possible to do that ?
Thanks in advance
TomTom
> Is it possible to create foreign key between to distinct SQL databases ?
No
"Tom McLeesh" <tom.mcleesh@.gmail.com> wrote in message
news:u2njv248GHA.4572@.TK2MSFTNGP02.phx.gbl...
> Is it possible to create foreign key between to distinct SQL databases ?
> Database A contains table [customers] (primary key : customer_id)
> Database B contains table [invoices] (each invoice should be linked to a
> customer. Foreign key : customer_id)
> I would like to create an integrity constraint between
> [invoices].[customer_id] and [customers].[customer_id], despite the fact
> that [invoices] et [customers] tables are in SEPARATE databases.
> Is it possible to do that ?
> Thanks in advance
> Tom|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eh1pb548GHA.4288@.TK2MSFTNGP02.phx.gbl...
> Tom
>> Is it possible to create foreign key between to distinct SQL databases ?
> No
>
And a desire to implement referential integrity between databases usually
indicates that you shouldn't be using different databases.
Perhaps multiple schemas in a single database?
David|||On Thu, 19 Oct 2006 16:54:00 +0200, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Tom
>> Is it possible to create foreign key between to distinct SQL databases ?
>No
But it would be possible to write triggers on the tables in both
databases to enforce the relationship.
Roy Harvey
Beacon Falls, CT|||>> Is it possible to create foreign key between to distinct SQL databases ?
No. Triggers are the usually suggested workaround.
--
Anith

Cross Database Foreign Keys

Does somebody knows if it is possible to have a Foreign key constraint where the primary key resides in a table in an other database? If this is possible how do I define it through the EM or a sql syntax is fine to? I am happy to with a reference in BOL (
book online).
e.g TABLE INVOICE in database B has a foreign column PARENT_ID with his primary key ID in table PARENT in database C.
Database B Database C
TABLE INVOICE - [PARENT_ID] --> [ID] - TABLE PARENT
You cannot have FK's crossing databases. If you want to enforce the rule, you can use triggers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"jacques" <anonymous@.discussions.microsoft.com> wrote in message
news:40FD755B-169D-493F-B66E-9CBCF9A9D57D@.microsoft.com...
> Does somebody knows if it is possible to have a Foreign key constraint where the primary key resides in a
table in an other database? If this is possible how do I define it through the EM or a sql syntax is fine to?
I am happy to with a reference in BOL (book online).
> e.g TABLE INVOICE in database B has a foreign column PARENT_ID with his primary key ID in table PARENT in
database C.
> Database B Database C
> TABLE INVOICE - [PARENT_ID] --> [ID] - TABLE PARENT
>
>
|||I believe Tibor has answered your question but may I ask why you store
closely related data in different databases? There are some situations
where this may be desirable but it is better to keep related data in the
same physical database if possible. This allows you to enforce integrity
via DRI and easily provide consistent backups.
Hope this helps.
Dan Guzman
SQL Server MVP
"jacques" <anonymous@.discussions.microsoft.com> wrote in message
news:40FD755B-169D-493F-B66E-9CBCF9A9D57D@.microsoft.com...
> Does somebody knows if it is possible to have a Foreign key constraint
where the primary key resides in a table in an other database? If this is
possible how do I define it through the EM or a sql syntax is fine to? I am
happy to with a reference in BOL (book online).
> e.g TABLE INVOICE in database B has a foreign column PARENT_ID with his
primary key ID in table PARENT in database C.
> Database B Database C
> TABLE INVOICE - [PARENT_ID] --> [ID] - TABLE PARENT
>
>

Cross Database Foreign Keys

Does somebody knows if it is possible to have a Foreign key constraint where
the primary key resides in a table in an other database? If this is possibl
e how do I define it through the EM or a sql syntax is fine to? I am happy t
o with a reference in BOL (
book online).
e.g TABLE INVOICE in database B has a foreign column PARENT_ID with his pri
mary key ID in table PARENT in database C.
Database B Database C
TABLE INVOICE - [PARENT_ID] --> [ID] - TABLE PARENTYou cannot have FK's crossing databases. If you want to enforce the rule, yo
u can use triggers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"jacques" <anonymous@.discussions.microsoft.com> wrote in message
news:40FD755B-169D-493F-B66E-9CBCF9A9D57D@.microsoft.com...
> Does somebody knows if it is possible to have a Foreign key constraint where the p
rimary key resides in a
table in an other database? If this is possible how do I define it through t
he EM or a sql syntax is fine to?
I am happy to with a reference in BOL (book online).
> e.g TABLE INVOICE in database B has a foreign column PARENT_ID with his primary k
ey ID in table PARENT in
database C.
> Database B Database C
> TABLE INVOICE - [PARENT_ID] --> [ID] - TABLE PARENT
>
>|||I believe Tibor has answered your question but may I ask why you store
closely related data in different databases? There are some situations
where this may be desirable but it is better to keep related data in the
same physical database if possible. This allows you to enforce integrity
via DRI and easily provide consistent backups.
Hope this helps.
Dan Guzman
SQL Server MVP
"jacques" <anonymous@.discussions.microsoft.com> wrote in message
news:40FD755B-169D-493F-B66E-9CBCF9A9D57D@.microsoft.com...
> Does somebody knows if it is possible to have a Foreign key constraint
where the primary key resides in a table in an other database? If this is
possible how do I define it through the EM or a sql syntax is fine to? I am
happy to with a reference in BOL (book online).
> e.g TABLE INVOICE in database B has a foreign column PARENT_ID with his
primary key ID in table PARENT in database C.
> Database B Database C
> TABLE INVOICE - [PARENT_ID] --> [ID] - TABLE PARENT
>
>

Cross Database Foreign Keys

Does somebody knows if it is possible to have a Foreign key constraint where the primary key resides in a table in an other database? If this is possible how do I define it through the EM or a sql syntax is fine to? I am happy to with a reference in BOL (book online)
e.g TABLE INVOICE in database B has a foreign column PARENT_ID with his primary key ID in table PARENT in database C
Database B Database
TABLE INVOICE - [PARENT_ID] --> [ID] - TABLE PARENYou cannot have FK's crossing databases. If you want to enforce the rule, you can use triggers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"jacques" <anonymous@.discussions.microsoft.com> wrote in message
news:40FD755B-169D-493F-B66E-9CBCF9A9D57D@.microsoft.com...
> Does somebody knows if it is possible to have a Foreign key constraint where the primary key resides in a
table in an other database? If this is possible how do I define it through the EM or a sql syntax is fine to?
I am happy to with a reference in BOL (book online).
> e.g TABLE INVOICE in database B has a foreign column PARENT_ID with his primary key ID in table PARENT in
database C.
> Database B Database C
> TABLE INVOICE - [PARENT_ID] --> [ID] - TABLE PARENT
>
>|||I believe Tibor has answered your question but may I ask why you store
closely related data in different databases? There are some situations
where this may be desirable but it is better to keep related data in the
same physical database if possible. This allows you to enforce integrity
via DRI and easily provide consistent backups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"jacques" <anonymous@.discussions.microsoft.com> wrote in message
news:40FD755B-169D-493F-B66E-9CBCF9A9D57D@.microsoft.com...
> Does somebody knows if it is possible to have a Foreign key constraint
where the primary key resides in a table in an other database? If this is
possible how do I define it through the EM or a sql syntax is fine to? I am
happy to with a reference in BOL (book online).
> e.g TABLE INVOICE in database B has a foreign column PARENT_ID with his
primary key ID in table PARENT in database C.
> Database B Database C
> TABLE INVOICE - [PARENT_ID] --> [ID] - TABLE PARENT
>
>