Sql-server – Database can be referenced by tables

database-designforeign keysql server

I am a computer science student and yesterday in my class on database management systems and talking about distributed systems and good practices he said that we can reference other instances through something he called 'connetion stream' or 'connetion String 'I really did not quite understand what he said. But I raised the following question:

I theoretically own a license db, application X and application Y, are totally different applications and only share the access user. In the first database are stored company information, license and access users; In database Y and X are different data that need to share the same user that is in the license database.

I tried in SQL Server as follows:

CREATE TABLE Licences;
USE Licenses;
CREATE TABLE Persons(
 Id int identity(1, 1),
 Name varchar(100) unique not null,
 //Other user information
 primary key(Id)
);

CREATE TABLE X;
USE X;
CREATE TABLE XInformation(
 Id int identity(1, 1),
 //Other X information
 PersonId int not null references Licenses..Persons(Id)
);

CREATE TABLE Y;
USE Y;
CREATE TABLE YInformation(
 Id int identity(1, 1),
 //Other Y information
 PersonId int not null references Licenses..Persons(Id)
);

But I get an error saying that sql server does not support foreign keys between databases.

But if I create these tables without referencing I can query them like this:

SELECT * from Persons as p inner join X..XInformation as xi on p.Id = xi.PersonId;

What is the correct way to do this referencing?

How would a dba solve this case?

Can I only reference other databases in Views?

Thanks in advance.

Best Answer

Unfortunate, there isn't a way to directly create the FK constraint you'd like. Run through some alternatives instead.

(1) Is it really necessary to have those two tables in different databases? Is there a reason why you can't just move the table? You won't be able to enforce any rules on schema changes unless you have the tables in the same db. Push and prod developers to redo it 'the right way'.

(2) Workaround: Create a trigger on insert/update to the referencing table, which does the test FK constraint usually does. Create a trigger on update/delete on the referenced table to implement cascading if needed. You can create an additional DDL trigger that prevents changes to the referenced table's structure. The problem though is that nothing fully prevents table structure changes before the FK is dropped. Another problem can occur when the two databases are not restored together - leading to possible inconsistent configurations.

(3) Create synchronization/replication scheme so that a reasonably up to date copy of the referenced table can be kept in the database where referencing table is located. Can either be a job that copies new rows (Change tracking in mssql) or use SSIS to build an ETL package that incrementally loads new rows into the copy on the referenced table. If the two databases are on separate servers, there are more options using replication features of MSSQL. Loading data into a database local copy is another chance to check schema, but again you can't prevent changes on the referenced table while the FK exists.

For performance, it should still be using the indexes.

Cross database querying are not uncommon, nor are cross server distributed queries. DBA's best friend are scripts and automation, and you can script checks to make sure there's nothing amiss that would break your cross db/server queries and shoot you an email otherwise. There are also other ways to set up alerts (on ddl triger, etc.,) so that you know if there's going to be a problem.

As for your question regarding views, I'm not sure what you mean. You can access tables on another db from within a view, but the constraint part of the FK relationship still can't be directly maintained on a table from another db. You can't schemabind a view using tables from different database either.

Related Question