Including view primary keys in relationships

I have two separate databases. One db as several tables that have foreign
key fields whose parents reside in the second db. I would like to be able
to define relationships between these entities.
I was thinking I could create a view of the parent table in the child's
database, but I don't see any way of including a view table as a
relationship partner. Nor do I see how to reference a table in a separate
database as a relationship partner.
Any advice would be greatly appreciated.
TIA.Peter J. Hunter wrote:
> I have two separate databases. One db as several tables that have foreign
> key fields whose parents reside in the second db. I would like to be able
> to define relationships between these entities.
> I was thinking I could create a view of the parent table in the child's
> database, but I don't see any way of including a view table as a
> relationship partner. Nor do I see how to reference a table in a separate
> database as a relationship partner.
> Any advice would be greatly appreciated.
> TIA.
You cannot create foreign keys across databases.
Keep the referencing tables and the parent tables in the same database,
then create views or synonyms that point to them from the other
database.
It's also possible to use triggers as a "constraint" to enforce the
foreign key.
Or you could replicate the data in both databases by some means.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Peter J. Hunter" <pjhunter@.wintersnet.net> wrote in message
news:%23R3zIUbiGHA.3408@.TK2MSFTNGP05.phx.gbl...
>I have two separate databases. One db as several tables that have foreign
>key fields whose parents reside in the second db. I would like to be able
>to define relationships between these entities.
> I was thinking I could create a view of the parent table in the child's
> database, but I don't see any way of including a view table as a
> relationship partner. Nor do I see how to reference a table in a separate
> database as a relationship partner.
> Any advice would be greatly appreciated.
If you are inclined to define relationships across databases, then you
probably shouldn't be using multiple databases. Perhaps you can use
multiple schemas inside a single database.
Davdi