I have a companies table which holds all the companies that my company does business with and it resides on a companies database.
I would like to create a relationship with between that table and another table(personnel table) which is in a totally different database(Personnel Database).
Both databases are on the same server.
How do I achieve this?
"Although you cannot use a foreign key in this situation, there are workarounds – you can use either triggers or UDFs wrapped in check constraints. Either way, your data integrity is not completely watertight: if the database with your parent table crashes and you restore it from a backup, you may easily end up with orphans."
To select the data using a common key you can create a view that includes a three-part qualifier: database-schema-table. Below I assume that each personnel entry has a CompanyID field that ties them together and that dbo is the schema. If such a field doesn't exist, you will need to create and populate one:
CREATE VIEW vCompanyPersonnel
AS
SELECT * FROM tblCompany t1
JOIN dbPersonnel.dbo.tblPersonnel
t2 ON t1.ID = t2.CompanyID
Similarly, in order to make updates to either table and maintain referential integrity, you will need to create stored procedures and only allow applications to update the data using those procs (which is a best practice, anyway). You should also make sure that the CompanyID field in the personnel table does not allow null values. Some procs to consider:
