Is cross-database referential integrity possible? Doesn't seem to be with a
standard foreign key constraint but are there "alternative" methods of
maintaining referential integrity across databases? Thanks.
LC
Use Triggers instead.
HTH, Jens Smeyer
http://www.sqlserver2005.de
"LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
> Is cross-database referential integrity possible? Doesn't seem to be with
> a
> standard foreign key constraint but are there "alternative" methods of
> maintaining referential integrity across databases? Thanks.
> LC
|||Thanks Jens, that should work.
"Jens Sü?meyer" wrote:
> Use Triggers instead.
> HTH, Jens Sü?meyer
> --
> http://www.sqlserver2005.de
> --
> "LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
>
>
|||yuck.
I'm no fan of triggers (Although IN the db that is your only option)
You could "consider" doing this in an DB_Accessor layer. (Enforce via Code)
Greg Jackson
PDX, Oregon
Showing posts with label constraint. Show all posts
Showing posts with label constraint. Show all posts
Sunday, February 19, 2012
cross-database referential integrity?
Is cross-database referential integrity possible? Doesn't seem to be with a
standard foreign key constraint but are there "alternative" methods of
maintaining referential integrity across databases? Thanks.
LCUse Triggers instead.
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
> Is cross-database referential integrity possible? Doesn't seem to be with
> a
> standard foreign key constraint but are there "alternative" methods of
> maintaining referential integrity across databases? Thanks.
> LC|||Thanks Jens, that should work.
"Jens Sü?meyer" wrote:
> Use Triggers instead.
> HTH, Jens Sü?meyer
> --
> http://www.sqlserver2005.de
> --
> "LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
>
>|||yuck.
I'm no fan of triggers (Although IN the db that is your only option)
You could "consider" doing this in an DB_Accessor layer. (Enforce via Code)
Greg Jackson
PDX, Oregon
standard foreign key constraint but are there "alternative" methods of
maintaining referential integrity across databases? Thanks.
LCUse Triggers instead.
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
> Is cross-database referential integrity possible? Doesn't seem to be with
> a
> standard foreign key constraint but are there "alternative" methods of
> maintaining referential integrity across databases? Thanks.
> LC|||Thanks Jens, that should work.
"Jens Sü?meyer" wrote:
> Use Triggers instead.
> HTH, Jens Sü?meyer
> --
> http://www.sqlserver2005.de
> --
> "LC" <LC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:73B1B5DB-9477-41EE-9A4A-EEA528871BD3@.microsoft.com...
>
>|||yuck.
I'm no fan of triggers (Although IN the db that is your only option)
You could "consider" doing this in an DB_Accessor layer. (Enforce via Code)
Greg Jackson
PDX, Oregon
Cross-database foreign key references are not supported. Workaround?
When I try to create a cross-database foreign key constraint, I get
"Cross-database foreign key references are not supported"
The cause of the message is, of course, obvious. However, I would still like
to have a constraint based on rows in a table of another database (running
in the same instance).
Is there any way round this, or am I trying to do something unreasonable? If
it's not possible, what would others normally do in this situation?
TIA
Charles
Given that I don't fully understand your scenario, or what set of
requirements is driving the separation of the data in the first place, if
you really want to enforce the relationship, then keep both tables in the
same database, and then create a view in the other database where you think
you need a "copy" of the data.
A
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>
|||>> Is there any way round this, or am I trying to do something unreasonable?[vbcol=seagreen]
You are not trying to do anything unreasonable. In fact, it is a very common
issue. Since DRI between databases are not supported, people usually use
triggers (a way of procedural referential integrity) to get the job done.
Anith
|||Charles,
By definition, constraints only work within a database. The only mechanism
that I have used for cross-database constraints is triggers. (And only
occasionally.)
RLF
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>
|||Hi Aaron
Thanks for the quick response. We have two databases: one containing static
(look-up) data, and one containing transient data that gets updated on a
regular basis. There are tables in the latter database that have columns
requiring a corresponding record in a static table (in the other database).
That is why I have been trying to create a cross-database foreign key
constraint. The databases are separate since the look-up data will change
from customer to customer, and it is more convenient to be able to 'plug in'
a new set of static data if it is in a separate database.
Charles
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epNsk9UUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Given that I don't fully understand your scenario, or what set of
> requirements is driving the separation of the data in the first place, if
> you really want to enforce the relationship, then keep both tables in the
> same database, and then create a view in the other database where you
> think you need a "copy" of the data.
> A
>
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Russell
Thanks for the reply. I see that Anith is suggesting much the same thing as
well, so with two votes I will look at using triggers.
Cheers.
Charles
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23IfhS%23UUIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Charles,
> By definition, constraints only work within a database. The only
> mechanism that I have used for cross-database constraints is triggers.
> (And only occasionally.)
> RLF
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> You are not trying to do anything unreasonable. In fact, it is a very
> common issue. Since DRI between databases are not supported, people
> usually use triggers (a way of procedural referential integrity) to get
> the job done.
> --
> Anith
>
|||On Jan 7, 10:53 am, "Charles Law" <bl...@.nowhere.com> wrote:
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still like
> to have a constraint based on rows in a table of another database (running
> in the same instance).
> Is there any way round this, or am I trying to do something unreasonable? If
> it's not possible, what would others normally do in this situation?
> TIA
> Charles
Charles,
I hope you realize that whatever solution you choose, it might not be
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution. Also be aware that sometimes trigger do not fire.
|||Hi
There is also the option of using a function in the constraint.
John
"Charles Law" wrote:
> Hi Anith
> Thanks for the quick reply. As I replied to Russell, with your and his vote,
> it looks like I will be looking at triggers.
> Cheers.
> Charles
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>
>
|||Hi John
Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?
Charles
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...[vbcol=seagreen]
> Hi
> There is also the option of using a function in the constraint.
> John
> "Charles Law" wrote:
"Cross-database foreign key references are not supported"
The cause of the message is, of course, obvious. However, I would still like
to have a constraint based on rows in a table of another database (running
in the same instance).
Is there any way round this, or am I trying to do something unreasonable? If
it's not possible, what would others normally do in this situation?
TIA
Charles
Given that I don't fully understand your scenario, or what set of
requirements is driving the separation of the data in the first place, if
you really want to enforce the relationship, then keep both tables in the
same database, and then create a view in the other database where you think
you need a "copy" of the data.
A
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>
|||>> Is there any way round this, or am I trying to do something unreasonable?[vbcol=seagreen]
You are not trying to do anything unreasonable. In fact, it is a very common
issue. Since DRI between databases are not supported, people usually use
triggers (a way of procedural referential integrity) to get the job done.
Anith
|||Charles,
By definition, constraints only work within a database. The only mechanism
that I have used for cross-database constraints is triggers. (And only
occasionally.)
RLF
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>
|||Hi Aaron
Thanks for the quick response. We have two databases: one containing static
(look-up) data, and one containing transient data that gets updated on a
regular basis. There are tables in the latter database that have columns
requiring a corresponding record in a static table (in the other database).
That is why I have been trying to create a cross-database foreign key
constraint. The databases are separate since the look-up data will change
from customer to customer, and it is more convenient to be able to 'plug in'
a new set of static data if it is in a separate database.
Charles
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epNsk9UUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Given that I don't fully understand your scenario, or what set of
> requirements is driving the separation of the data in the first place, if
> you really want to enforce the relationship, then keep both tables in the
> same database, and then create a view in the other database where you
> think you need a "copy" of the data.
> A
>
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Russell
Thanks for the reply. I see that Anith is suggesting much the same thing as
well, so with two votes I will look at using triggers.
Cheers.
Charles
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23IfhS%23UUIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Charles,
> By definition, constraints only work within a database. The only
> mechanism that I have used for cross-database constraints is triggers.
> (And only occasionally.)
> RLF
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> You are not trying to do anything unreasonable. In fact, it is a very
> common issue. Since DRI between databases are not supported, people
> usually use triggers (a way of procedural referential integrity) to get
> the job done.
> --
> Anith
>
|||On Jan 7, 10:53 am, "Charles Law" <bl...@.nowhere.com> wrote:
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still like
> to have a constraint based on rows in a table of another database (running
> in the same instance).
> Is there any way round this, or am I trying to do something unreasonable? If
> it's not possible, what would others normally do in this situation?
> TIA
> Charles
Charles,
I hope you realize that whatever solution you choose, it might not be
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution. Also be aware that sometimes trigger do not fire.
|||Hi
There is also the option of using a function in the constraint.
John
"Charles Law" wrote:
> Hi Anith
> Thanks for the quick reply. As I replied to Russell, with your and his vote,
> it looks like I will be looking at triggers.
> Cheers.
> Charles
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>
>
|||Hi John
Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?
Charles
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...[vbcol=seagreen]
> Hi
> There is also the option of using a function in the constraint.
> John
> "Charles Law" wrote:
Cross-database foreign key references are not supported. Workaround?
When I try to create a cross-database foreign key constraint, I get
"Cross-database foreign key references are not supported"
The cause of the message is, of course, obvious. However, I would still like
to have a constraint based on rows in a table of another database (running
in the same instance).
Is there any way round this, or am I trying to do something unreasonable? If
it's not possible, what would others normally do in this situation?
TIA
CharlesGiven that I don't fully understand your scenario, or what set of
requirements is driving the separation of the data in the first place, if
you really want to enforce the relationship, then keep both tables in the
same database, and then create a view in the other database where you think
you need a "copy" of the data.
A
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>|||>> Is there any way round this, or am I trying to do something unreasonable?
>> If it's not possible, what would others normally do in this situation?
You are not trying to do anything unreasonable. In fact, it is a very common
issue. Since DRI between databases are not supported, people usually use
triggers (a way of procedural referential integrity) to get the job done.
--
Anith|||Charles,
By definition, constraints only work within a database. The only mechanism
that I have used for cross-database constraints is triggers. (And only
occasionally.)
RLF
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>|||Hi Aaron
Thanks for the quick response. We have two databases: one containing static
(look-up) data, and one containing transient data that gets updated on a
regular basis. There are tables in the latter database that have columns
requiring a corresponding record in a static table (in the other database).
That is why I have been trying to create a cross-database foreign key
constraint. The databases are separate since the look-up data will change
from customer to customer, and it is more convenient to be able to 'plug in'
a new set of static data if it is in a separate database.
Charles
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epNsk9UUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Given that I don't fully understand your scenario, or what set of
> requirements is driving the separation of the data in the first place, if
> you really want to enforce the relationship, then keep both tables in the
> same database, and then create a view in the other database where you
> think you need a "copy" of the data.
> A
>
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> When I try to create a cross-database foreign key constraint, I get
>> "Cross-database foreign key references are not supported"
>> The cause of the message is, of course, obvious. However, I would still
>> like to have a constraint based on rows in a table of another database
>> (running in the same instance).
>> Is there any way round this, or am I trying to do something unreasonable?
>> If it's not possible, what would others normally do in this situation?
>> TIA
>> Charles
>>
>|||Hi Russell
Thanks for the reply. I see that Anith is suggesting much the same thing as
well, so with two votes I will look at using triggers.
Cheers.
Charles
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23IfhS%23UUIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Charles,
> By definition, constraints only work within a database. The only
> mechanism that I have used for cross-database constraints is triggers.
> (And only occasionally.)
> RLF
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> When I try to create a cross-database foreign key constraint, I get
>> "Cross-database foreign key references are not supported"
>> The cause of the message is, of course, obvious. However, I would still
>> like to have a constraint based on rows in a table of another database
>> (running in the same instance).
>> Is there any way round this, or am I trying to do something unreasonable?
>> If it's not possible, what would others normally do in this situation?
>> TIA
>> Charles
>>
>|||Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>> Is there any way round this, or am I trying to do something
>> unreasonable? If it's not possible, what would others normally do in
>> this situation?
> You are not trying to do anything unreasonable. In fact, it is a very
> common issue. Since DRI between databases are not supported, people
> usually use triggers (a way of procedural referential integrity) to get
> the job done.
> --
> Anith
>|||On Jan 7, 10:53 am, "Charles Law" <bl...@.nowhere.com> wrote:
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still like
> to have a constraint based on rows in a table of another database (running
> in the same instance).
> Is there any way round this, or am I trying to do something unreasonable? If
> it's not possible, what would others normally do in this situation?
> TIA
> Charles
Charles,
I hope you realize that whatever solution you choose, it might not be
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution. Also be aware that sometimes trigger do not fire.|||Hi
There is also the option of using a function in the constraint.
John
"Charles Law" wrote:
> Hi Anith
> Thanks for the quick reply. As I replied to Russell, with your and his vote,
> it looks like I will be looking at triggers.
> Cheers.
> Charles
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> >> Is there any way round this, or am I trying to do something
> >> unreasonable? If it's not possible, what would others normally do in
> >> this situation?
> >
> > You are not trying to do anything unreasonable. In fact, it is a very
> > common issue. Since DRI between databases are not supported, people
> > usually use triggers (a way of procedural referential integrity) to get
> > the job done.
> >
> > --
> > Anith
> >
>
>|||Hi John
Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?
Charles
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
> Hi
> There is also the option of using a function in the constraint.
> John
> "Charles Law" wrote:
>> Hi Anith
>> Thanks for the quick reply. As I replied to Russell, with your and his
>> vote,
>> it looks like I will be looking at triggers.
>> Cheers.
>> Charles
>>
>> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
>> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>> >> Is there any way round this, or am I trying to do something
>> >> unreasonable? If it's not possible, what would others normally do in
>> >> this situation?
>> >
>> > You are not trying to do anything unreasonable. In fact, it is a very
>> > common issue. Since DRI between databases are not supported, people
>> > usually use triggers (a way of procedural referential integrity) to get
>> > the job done.
>> >
>> > --
>> > Anith
>> >
>>|||"Charles Law" <blank@.nowhere.com> wrote in message
news:e9P4QCVUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Aaron
> The databases are separate since the look-up data will change from
> customer to customer, and it is more convenient to be able to 'plug in' a
> new set of static data if it is in a separate database.
>
I think your question demonstrates exactly the opposite. It is much LESS
convenient to put the static data in a separate database precisely because
that data is very likely to be referenced by a foreign key.
--
David Portas|||> I hope you realize that whatever solution you choose, it might not be
> fully watertight. If you ever need to restore one of your databases
> from a backup, that may violate your integrity - I don't know a fully
> safe solution
Good point.
> Also be aware that sometimes trigger do not fire.
That would be worrying if it were random. Do you mean that they sometimes
don't fire for no apparent reason?
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:6d8fa87b-9204-4858-a727-7b8c40d625e0@.1g2000hsl.googlegroups.com...
> On Jan 7, 10:53 am, "Charles Law" <bl...@.nowhere.com> wrote:
>> When I try to create a cross-database foreign key constraint, I get
>> "Cross-database foreign key references are not supported"
>> The cause of the message is, of course, obvious. However, I would still
>> like
>> to have a constraint based on rows in a table of another database
>> (running
>> in the same instance).
>> Is there any way round this, or am I trying to do something unreasonable?
>> If
>> it's not possible, what would others normally do in this situation?
>> TIA
>> Charles
> Charles,
> I hope you realize that whatever solution you choose, it might not be
> fully watertight. If you ever need to restore one of your databases
> from a backup, that may violate your integrity - I don't know a fully
> safe solution. Also be aware that sometimes trigger do not fire.|||Hi David
I can't argue with your logic there.
I'll have to give this some more careful thought. Perhaps I am creating more
problems than I solve by separating the static data from the rest.
Charles
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uXD2f9VUIHA.5360@.TK2MSFTNGP03.phx.gbl...
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e9P4QCVUIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Aaron
>> The databases are separate since the look-up data will change from
>> customer to customer, and it is more convenient to be able to 'plug in' a
>> new set of static data if it is in a separate database.
> I think your question demonstrates exactly the opposite. It is much LESS
> convenient to put the static data in a separate database precisely because
> that data is very likely to be referenced by a foreign key.
> --
> David Portas
>|||On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> > I hope you realize that whatever solution you choose, it might not be
> > fully watertight. If you ever need to restore one of your databases
> > from a backup, that may violate your integrity - I don't know a fully
> > safe solution
> Good point.
> > Also be aware that sometimes trigger do not fire.
> That would be worrying if it were random. Do you mean that they sometimes
> don't fire for no apparent reason?
> Charles
not random. Described here:
http://www.devx.com/dbzone/Article/31985|||On Jan 7, 12:57 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> Hi John
> Any idea how performance compares between use of FK constraint (if it were
> available), trigger and function?
> Charles
> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
> > Hi
> > There is also the option of using a function in the constraint.
> > John
> > "Charles Law" wrote:
> >> Hi Anith
> >> Thanks for the quick reply. As I replied to Russell, with your and his
> >> vote,
> >> it looks like I will be looking at triggers.
> >> Cheers.
> >> Charles
> >> "Anith Sen" <an...@.bizdatasolutions.com> wrote in message
> >>news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> >> >> Is there any way round this, or am I trying to do something
> >> >> unreasonable? If it's not possible, what would others normally do in
> >> >> this situation?
> >> > You are not trying to do anything unreasonable. In fact, it is a very
> >> > common issue. Since DRI between databases are not supported, people
> >> > usually use triggers (a way of procedural referential integrity) to get
> >> > the job done.
> >> > --
> >> > Anith
I haven't repeated my benchmarks on 2005, but on 2000 FK is a clear
winner.|||I guess that's what one would expect, but good to have it confirmed (on
2000).
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:632fe8e1-a5ea-4d23-b90c-9e803f7f5a13@.p69g2000hsa.googlegroups.com...
> On Jan 7, 12:57 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> Hi John
>> Any idea how performance compares between use of FK constraint (if it
>> were
>> available), trigger and function?
>> Charles
>> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
>> news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
>> > Hi
>> > There is also the option of using a function in the constraint.
>> > John
>> > "Charles Law" wrote:
>> >> Hi Anith
>> >> Thanks for the quick reply. As I replied to Russell, with your and his
>> >> vote,
>> >> it looks like I will be looking at triggers.
>> >> Cheers.
>> >> Charles
>> >> "Anith Sen" <an...@.bizdatasolutions.com> wrote in message
>> >>news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>> >> >> Is there any way round this, or am I trying to do something
>> >> >> unreasonable? If it's not possible, what would others normally do
>> >> >> in
>> >> >> this situation?
>> >> > You are not trying to do anything unreasonable. In fact, it is a
>> >> > very
>> >> > common issue. Since DRI between databases are not supported, people
>> >> > usually use triggers (a way of procedural referential integrity) to
>> >> > get
>> >> > the job done.
>> >> > --
>> >> > Anith
> I haven't repeated my benchmarks on 2005, but on 2000 FK is a clear
> winner.|||Hi Charles
There is an example in the link Alex posted.
John
"Charles Law" wrote:
> Hi John
> Any idea how performance compares between use of FK constraint (if it were
> available), trigger and function?
> Charles
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
> > Hi
> >
> > There is also the option of using a function in the constraint.
> >
> > John
> >
> > "Charles Law" wrote:
> >
> >> Hi Anith
> >>
> >> Thanks for the quick reply. As I replied to Russell, with your and his
> >> vote,
> >> it looks like I will be looking at triggers.
> >>
> >> Cheers.
> >>
> >> Charles
> >>
> >>
> >> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> >> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> >> >> Is there any way round this, or am I trying to do something
> >> >> unreasonable? If it's not possible, what would others normally do in
> >> >> this situation?
> >> >
> >> > You are not trying to do anything unreasonable. In fact, it is a very
> >> > common issue. Since DRI between databases are not supported, people
> >> > usually use triggers (a way of procedural referential integrity) to get
> >> > the job done.
> >> >
> >> > --
> >> > Anith
> >> >
> >>
> >>
> >>
>
>|||Thanks. It looks, though, like they do not fire if the settings are
incorrect, or if someone changes them. So long as the rules are followed
then there shouldn't be a problem?
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:53f039b7-b49f-4d75-86bc-5157ce745a97@.e23g2000prf.googlegroups.com...
> On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> > I hope you realize that whatever solution you choose, it might not be
>> > fully watertight. If you ever need to restore one of your databases
>> > from a backup, that may violate your integrity - I don't know a fully
>> > safe solution
>> Good point.
>> > Also be aware that sometimes trigger do not fire.
>> That would be worrying if it were random. Do you mean that they sometimes
>> don't fire for no apparent reason?
>> Charles
> not random. Described here:
> http://www.devx.com/dbzone/Article/31985|||On Jan 8, 5:24 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> Thanks. It looks, though, like they do not fire if the settings are
> incorrect, or if someone changes them. So long as the rules are followed
> then there shouldn't be a problem?
> Charles
> "Alex Kuznetsov" <alk...@.gmail.com> wrote in message
> news:53f039b7-b49f-4d75-86bc-5157ce745a97@.e23g2000prf.googlegroups.com...
> > On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> >> > I hope you realize that whatever solution you choose, it might not be
> >> > fully watertight. If you ever need to restore one of your databases
> >> > from a backup, that may violate your integrity - I don't know a fully
> >> > safe solution
> >> Good point.
> >> > Also be aware that sometimes trigger do not fire.
> >> That would be worrying if it were random. Do you mean that they sometimes
> >> don't fire for no apparent reason?
> >> Charles
> > not random. Described here:
> >http://www.devx.com/dbzone/Article/31985
Also use database permissions to make sure that your users cannot
truncate your parent table. Truncate also does not fire triggers. Bulk
insert also does not fire them either. Also dropping recreating a
parent table is a way around your triggers...|||> Also use database permissions to make sure that your users cannot
> truncate your parent table. Truncate also does not fire triggers. Bulk
> insert also does not fire them either. Also dropping recreating a
> parent table is a way around your triggers...
Ok. Point taken.
Cheers
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:35375a5f-32fd-4389-b3d9-b9160a923061@.i7g2000prf.googlegroups.com...
> On Jan 8, 5:24 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> Thanks. It looks, though, like they do not fire if the settings are
>> incorrect, or if someone changes them. So long as the rules are followed
>> then there shouldn't be a problem?
>> Charles
>> "Alex Kuznetsov" <alk...@.gmail.com> wrote in message
>> news:53f039b7-b49f-4d75-86bc-5157ce745a97@.e23g2000prf.googlegroups.com...
>> > On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> >> > I hope you realize that whatever solution you choose, it might not
>> >> > be
>> >> > fully watertight. If you ever need to restore one of your databases
>> >> > from a backup, that may violate your integrity - I don't know a
>> >> > fully
>> >> > safe solution
>> >> Good point.
>> >> > Also be aware that sometimes trigger do not fire.
>> >> That would be worrying if it were random. Do you mean that they
>> >> sometimes
>> >> don't fire for no apparent reason?
>> >> Charles
>> > not random. Described here:
>> >http://www.devx.com/dbzone/Article/31985
> Also use database permissions to make sure that your users cannot
> truncate your parent table. Truncate also does not fire triggers. Bulk
> insert also does not fire them either. Also dropping recreating a
> parent table is a way around your triggers...
"Cross-database foreign key references are not supported"
The cause of the message is, of course, obvious. However, I would still like
to have a constraint based on rows in a table of another database (running
in the same instance).
Is there any way round this, or am I trying to do something unreasonable? If
it's not possible, what would others normally do in this situation?
TIA
CharlesGiven that I don't fully understand your scenario, or what set of
requirements is driving the separation of the data in the first place, if
you really want to enforce the relationship, then keep both tables in the
same database, and then create a view in the other database where you think
you need a "copy" of the data.
A
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>|||>> Is there any way round this, or am I trying to do something unreasonable?
>> If it's not possible, what would others normally do in this situation?
You are not trying to do anything unreasonable. In fact, it is a very common
issue. Since DRI between databases are not supported, people usually use
triggers (a way of procedural referential integrity) to get the job done.
--
Anith|||Charles,
By definition, constraints only work within a database. The only mechanism
that I have used for cross-database constraints is triggers. (And only
occasionally.)
RLF
"Charles Law" <blank@.nowhere.com> wrote in message
news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still
> like to have a constraint based on rows in a table of another database
> (running in the same instance).
> Is there any way round this, or am I trying to do something unreasonable?
> If it's not possible, what would others normally do in this situation?
> TIA
> Charles
>|||Hi Aaron
Thanks for the quick response. We have two databases: one containing static
(look-up) data, and one containing transient data that gets updated on a
regular basis. There are tables in the latter database that have columns
requiring a corresponding record in a static table (in the other database).
That is why I have been trying to create a cross-database foreign key
constraint. The databases are separate since the look-up data will change
from customer to customer, and it is more convenient to be able to 'plug in'
a new set of static data if it is in a separate database.
Charles
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epNsk9UUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Given that I don't fully understand your scenario, or what set of
> requirements is driving the separation of the data in the first place, if
> you really want to enforce the relationship, then keep both tables in the
> same database, and then create a view in the other database where you
> think you need a "copy" of the data.
> A
>
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> When I try to create a cross-database foreign key constraint, I get
>> "Cross-database foreign key references are not supported"
>> The cause of the message is, of course, obvious. However, I would still
>> like to have a constraint based on rows in a table of another database
>> (running in the same instance).
>> Is there any way round this, or am I trying to do something unreasonable?
>> If it's not possible, what would others normally do in this situation?
>> TIA
>> Charles
>>
>|||Hi Russell
Thanks for the reply. I see that Anith is suggesting much the same thing as
well, so with two votes I will look at using triggers.
Cheers.
Charles
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23IfhS%23UUIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Charles,
> By definition, constraints only work within a database. The only
> mechanism that I have used for cross-database constraints is triggers.
> (And only occasionally.)
> RLF
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e%235ZS3UUIHA.4280@.TK2MSFTNGP06.phx.gbl...
>> When I try to create a cross-database foreign key constraint, I get
>> "Cross-database foreign key references are not supported"
>> The cause of the message is, of course, obvious. However, I would still
>> like to have a constraint based on rows in a table of another database
>> (running in the same instance).
>> Is there any way round this, or am I trying to do something unreasonable?
>> If it's not possible, what would others normally do in this situation?
>> TIA
>> Charles
>>
>|||Hi Anith
Thanks for the quick reply. As I replied to Russell, with your and his vote,
it looks like I will be looking at triggers.
Cheers.
Charles
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>> Is there any way round this, or am I trying to do something
>> unreasonable? If it's not possible, what would others normally do in
>> this situation?
> You are not trying to do anything unreasonable. In fact, it is a very
> common issue. Since DRI between databases are not supported, people
> usually use triggers (a way of procedural referential integrity) to get
> the job done.
> --
> Anith
>|||On Jan 7, 10:53 am, "Charles Law" <bl...@.nowhere.com> wrote:
> When I try to create a cross-database foreign key constraint, I get
> "Cross-database foreign key references are not supported"
> The cause of the message is, of course, obvious. However, I would still like
> to have a constraint based on rows in a table of another database (running
> in the same instance).
> Is there any way round this, or am I trying to do something unreasonable? If
> it's not possible, what would others normally do in this situation?
> TIA
> Charles
Charles,
I hope you realize that whatever solution you choose, it might not be
fully watertight. If you ever need to restore one of your databases
from a backup, that may violate your integrity - I don't know a fully
safe solution. Also be aware that sometimes trigger do not fire.|||Hi
There is also the option of using a function in the constraint.
John
"Charles Law" wrote:
> Hi Anith
> Thanks for the quick reply. As I replied to Russell, with your and his vote,
> it looks like I will be looking at triggers.
> Cheers.
> Charles
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> >> Is there any way round this, or am I trying to do something
> >> unreasonable? If it's not possible, what would others normally do in
> >> this situation?
> >
> > You are not trying to do anything unreasonable. In fact, it is a very
> > common issue. Since DRI between databases are not supported, people
> > usually use triggers (a way of procedural referential integrity) to get
> > the job done.
> >
> > --
> > Anith
> >
>
>|||Hi John
Any idea how performance compares between use of FK constraint (if it were
available), trigger and function?
Charles
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
> Hi
> There is also the option of using a function in the constraint.
> John
> "Charles Law" wrote:
>> Hi Anith
>> Thanks for the quick reply. As I replied to Russell, with your and his
>> vote,
>> it looks like I will be looking at triggers.
>> Cheers.
>> Charles
>>
>> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
>> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>> >> Is there any way round this, or am I trying to do something
>> >> unreasonable? If it's not possible, what would others normally do in
>> >> this situation?
>> >
>> > You are not trying to do anything unreasonable. In fact, it is a very
>> > common issue. Since DRI between databases are not supported, people
>> > usually use triggers (a way of procedural referential integrity) to get
>> > the job done.
>> >
>> > --
>> > Anith
>> >
>>|||"Charles Law" <blank@.nowhere.com> wrote in message
news:e9P4QCVUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Aaron
> The databases are separate since the look-up data will change from
> customer to customer, and it is more convenient to be able to 'plug in' a
> new set of static data if it is in a separate database.
>
I think your question demonstrates exactly the opposite. It is much LESS
convenient to put the static data in a separate database precisely because
that data is very likely to be referenced by a foreign key.
--
David Portas|||> I hope you realize that whatever solution you choose, it might not be
> fully watertight. If you ever need to restore one of your databases
> from a backup, that may violate your integrity - I don't know a fully
> safe solution
Good point.
> Also be aware that sometimes trigger do not fire.
That would be worrying if it were random. Do you mean that they sometimes
don't fire for no apparent reason?
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:6d8fa87b-9204-4858-a727-7b8c40d625e0@.1g2000hsl.googlegroups.com...
> On Jan 7, 10:53 am, "Charles Law" <bl...@.nowhere.com> wrote:
>> When I try to create a cross-database foreign key constraint, I get
>> "Cross-database foreign key references are not supported"
>> The cause of the message is, of course, obvious. However, I would still
>> like
>> to have a constraint based on rows in a table of another database
>> (running
>> in the same instance).
>> Is there any way round this, or am I trying to do something unreasonable?
>> If
>> it's not possible, what would others normally do in this situation?
>> TIA
>> Charles
> Charles,
> I hope you realize that whatever solution you choose, it might not be
> fully watertight. If you ever need to restore one of your databases
> from a backup, that may violate your integrity - I don't know a fully
> safe solution. Also be aware that sometimes trigger do not fire.|||Hi David
I can't argue with your logic there.
I'll have to give this some more careful thought. Perhaps I am creating more
problems than I solve by separating the static data from the rest.
Charles
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uXD2f9VUIHA.5360@.TK2MSFTNGP03.phx.gbl...
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:e9P4QCVUIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Aaron
>> The databases are separate since the look-up data will change from
>> customer to customer, and it is more convenient to be able to 'plug in' a
>> new set of static data if it is in a separate database.
> I think your question demonstrates exactly the opposite. It is much LESS
> convenient to put the static data in a separate database precisely because
> that data is very likely to be referenced by a foreign key.
> --
> David Portas
>|||On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> > I hope you realize that whatever solution you choose, it might not be
> > fully watertight. If you ever need to restore one of your databases
> > from a backup, that may violate your integrity - I don't know a fully
> > safe solution
> Good point.
> > Also be aware that sometimes trigger do not fire.
> That would be worrying if it were random. Do you mean that they sometimes
> don't fire for no apparent reason?
> Charles
not random. Described here:
http://www.devx.com/dbzone/Article/31985|||On Jan 7, 12:57 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> Hi John
> Any idea how performance compares between use of FK constraint (if it were
> available), trigger and function?
> Charles
> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
> > Hi
> > There is also the option of using a function in the constraint.
> > John
> > "Charles Law" wrote:
> >> Hi Anith
> >> Thanks for the quick reply. As I replied to Russell, with your and his
> >> vote,
> >> it looks like I will be looking at triggers.
> >> Cheers.
> >> Charles
> >> "Anith Sen" <an...@.bizdatasolutions.com> wrote in message
> >>news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> >> >> Is there any way round this, or am I trying to do something
> >> >> unreasonable? If it's not possible, what would others normally do in
> >> >> this situation?
> >> > You are not trying to do anything unreasonable. In fact, it is a very
> >> > common issue. Since DRI between databases are not supported, people
> >> > usually use triggers (a way of procedural referential integrity) to get
> >> > the job done.
> >> > --
> >> > Anith
I haven't repeated my benchmarks on 2005, but on 2000 FK is a clear
winner.|||I guess that's what one would expect, but good to have it confirmed (on
2000).
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:632fe8e1-a5ea-4d23-b90c-9e803f7f5a13@.p69g2000hsa.googlegroups.com...
> On Jan 7, 12:57 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> Hi John
>> Any idea how performance compares between use of FK constraint (if it
>> were
>> available), trigger and function?
>> Charles
>> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
>> news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
>> > Hi
>> > There is also the option of using a function in the constraint.
>> > John
>> > "Charles Law" wrote:
>> >> Hi Anith
>> >> Thanks for the quick reply. As I replied to Russell, with your and his
>> >> vote,
>> >> it looks like I will be looking at triggers.
>> >> Cheers.
>> >> Charles
>> >> "Anith Sen" <an...@.bizdatasolutions.com> wrote in message
>> >>news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>> >> >> Is there any way round this, or am I trying to do something
>> >> >> unreasonable? If it's not possible, what would others normally do
>> >> >> in
>> >> >> this situation?
>> >> > You are not trying to do anything unreasonable. In fact, it is a
>> >> > very
>> >> > common issue. Since DRI between databases are not supported, people
>> >> > usually use triggers (a way of procedural referential integrity) to
>> >> > get
>> >> > the job done.
>> >> > --
>> >> > Anith
> I haven't repeated my benchmarks on 2005, but on 2000 FK is a clear
> winner.|||Hi Charles
There is an example in the link Alex posted.
John
"Charles Law" wrote:
> Hi John
> Any idea how performance compares between use of FK constraint (if it were
> available), trigger and function?
> Charles
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7243FE54-4405-4EC3-80A6-F9ABFB98117E@.microsoft.com...
> > Hi
> >
> > There is also the option of using a function in the constraint.
> >
> > John
> >
> > "Charles Law" wrote:
> >
> >> Hi Anith
> >>
> >> Thanks for the quick reply. As I replied to Russell, with your and his
> >> vote,
> >> it looks like I will be looking at triggers.
> >>
> >> Cheers.
> >>
> >> Charles
> >>
> >>
> >> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> >> news:uyA15%23UUIHA.5404@.TK2MSFTNGP03.phx.gbl...
> >> >> Is there any way round this, or am I trying to do something
> >> >> unreasonable? If it's not possible, what would others normally do in
> >> >> this situation?
> >> >
> >> > You are not trying to do anything unreasonable. In fact, it is a very
> >> > common issue. Since DRI between databases are not supported, people
> >> > usually use triggers (a way of procedural referential integrity) to get
> >> > the job done.
> >> >
> >> > --
> >> > Anith
> >> >
> >>
> >>
> >>
>
>|||Thanks. It looks, though, like they do not fire if the settings are
incorrect, or if someone changes them. So long as the rules are followed
then there shouldn't be a problem?
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:53f039b7-b49f-4d75-86bc-5157ce745a97@.e23g2000prf.googlegroups.com...
> On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> > I hope you realize that whatever solution you choose, it might not be
>> > fully watertight. If you ever need to restore one of your databases
>> > from a backup, that may violate your integrity - I don't know a fully
>> > safe solution
>> Good point.
>> > Also be aware that sometimes trigger do not fire.
>> That would be worrying if it were random. Do you mean that they sometimes
>> don't fire for no apparent reason?
>> Charles
> not random. Described here:
> http://www.devx.com/dbzone/Article/31985|||On Jan 8, 5:24 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> Thanks. It looks, though, like they do not fire if the settings are
> incorrect, or if someone changes them. So long as the rules are followed
> then there shouldn't be a problem?
> Charles
> "Alex Kuznetsov" <alk...@.gmail.com> wrote in message
> news:53f039b7-b49f-4d75-86bc-5157ce745a97@.e23g2000prf.googlegroups.com...
> > On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> >> > I hope you realize that whatever solution you choose, it might not be
> >> > fully watertight. If you ever need to restore one of your databases
> >> > from a backup, that may violate your integrity - I don't know a fully
> >> > safe solution
> >> Good point.
> >> > Also be aware that sometimes trigger do not fire.
> >> That would be worrying if it were random. Do you mean that they sometimes
> >> don't fire for no apparent reason?
> >> Charles
> > not random. Described here:
> >http://www.devx.com/dbzone/Article/31985
Also use database permissions to make sure that your users cannot
truncate your parent table. Truncate also does not fire triggers. Bulk
insert also does not fire them either. Also dropping recreating a
parent table is a way around your triggers...|||> Also use database permissions to make sure that your users cannot
> truncate your parent table. Truncate also does not fire triggers. Bulk
> insert also does not fire them either. Also dropping recreating a
> parent table is a way around your triggers...
Ok. Point taken.
Cheers
Charles
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:35375a5f-32fd-4389-b3d9-b9160a923061@.i7g2000prf.googlegroups.com...
> On Jan 8, 5:24 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> Thanks. It looks, though, like they do not fire if the settings are
>> incorrect, or if someone changes them. So long as the rules are followed
>> then there shouldn't be a problem?
>> Charles
>> "Alex Kuznetsov" <alk...@.gmail.com> wrote in message
>> news:53f039b7-b49f-4d75-86bc-5157ce745a97@.e23g2000prf.googlegroups.com...
>> > On Jan 7, 1:06 pm, "Charles Law" <bl...@.nowhere.com> wrote:
>> >> > I hope you realize that whatever solution you choose, it might not
>> >> > be
>> >> > fully watertight. If you ever need to restore one of your databases
>> >> > from a backup, that may violate your integrity - I don't know a
>> >> > fully
>> >> > safe solution
>> >> Good point.
>> >> > Also be aware that sometimes trigger do not fire.
>> >> That would be worrying if it were random. Do you mean that they
>> >> sometimes
>> >> don't fire for no apparent reason?
>> >> Charles
>> > not random. Described here:
>> >http://www.devx.com/dbzone/Article/31985
> Also use database permissions to make sure that your users cannot
> truncate your parent table. Truncate also does not fire triggers. Bulk
> insert also does not fire them either. Also dropping recreating a
> parent table is a way around your triggers...
Cross table constraint -- possible?
Consider these four tables:
TableA:
Columns: ID (PK), Type (FK-TableA_Types), etc.
TableA_Types:
Columns: ID (PK), Name
Table B:
Columns: ID (PK), Type (FK-TableB_Types), etc.
TableB_Types:
Columns: ID (PK), Name
TableA:
Columns: ID (PK), Type (FK-TableA_Types), etc.
TableA_Types:
Columns: ID (PK), Name
Table B:
Columns: ID (PK), Type (FK-TableB_Types), etc.
TableB_Types:
Columns: ID (PK), Name
TableA has a FK column to the TableA_Types table (TableA.Type->TableA_Types.ID), and TableB has a FK column to the TableB_Types table (TableB.Type->TableB_Types.ID).
The problem is that TableA_Types and TableB_Types overlap, and should really be in the same table. The problem is, they're not, and the given the large amount of work that it would involve in consolidating the tables, it would preferable to simply add a constraint to TableA_Types and TableB_Types such that their IDs don't overlap.
In other words, I want to avoid this situation:
TableA_Types:
1 name1
2 name2
TableB_Types:
1 name3
2 name4
Can I add a constraint on TableA_Types such that it prevents new records from having the same IDs as any ID for records in the TableB_Types table, and vice versa?
If so, how? If not, any other suggestions?
Thanks!Are these identity fields? If you can get away with changing the identities for these tables, just re create the identitiy columns so that one table only uses odd numbers and another table only uses even numbers.|||
This is not possible with a constraint.
You could achieve this with a trigger if you really want this situation to be blocked.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||To directly answer you questions -No, not a table constraint; and yes, you have alternatives.
You can use the UNIQUEIDENTIFIER datatype for the primary key in each table -there will never be any overlap. But I don't think that that really makes much sense in this situation. The keys are too large and unwieldy.
Cam's suggestion is interesting, and just 'might' work for you. Set the IDENTITY seed = 1 in one table, and the seed = 2 in the other table, and the increment value = 2 in both tables. As long as the row counts in each table don't approach 1 billion, you 'should' be OK. Set a table constraint in each table that maintains the odd or even scheme.
And Wesley's suggestion to use a TRIGGER is the most 'bullet proof' method -all the while requiring the most server resources.
But the real issue is this: Why do you not take the time and effort and correct a underlaying defect that you know exists in the database?
Leaving it is like having dryrot in your house and just painting over it.
Once I know that dryrot has been painted over, I'm instantly on alert for other signifianct defects waiting like time bombs ...
|||You can "sort of" do it. A constraint can actually access other tables through a function, but it can get messy to do this. So like several other folks have said, a trigger is the thing to do|||I agree with Arnie though, correcting the database design is actually the best solution.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Thanks for the posts, guys. I've implemented a check using triggers. Not the most elegant or best solution, I agree, but it will have to do for now.Tuesday, February 14, 2012
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
>
>
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
>
>
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
>
>
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
>
>
Subscribe to:
Posts (Atom)