Add Foreign Key relationship between two Databases

Question

I have two tables in two different databases. In table1 (in database1) there is a column called column1 and it is a primary key. Now in table2 (in database2) there is a column called column2 and I want to add it as a foreign key.

I tried to add it and it gave me the following error:

Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key Database2.table2.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

How do I do that since the tables are in different databases.

Fala

Answer 1

You would need to manage the referential constraint across databases using a Trigger.

---

Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.

Example:

Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin

   If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END

END

---

Edited: Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you.

Personally

Can you explain me with an example – Fala

Similarly Do I have to create Update Trigger too. – Fala

@Fala Yes...but you can create one trigger for both Insert and update. See my edited answer – Personally

SO This trigger has to be created on table where I want to add foreign key right – Fala

@John Hartsock -- the above example can easily fail, without adding appropriate transaction handling. A decent discussion of the type of problem that can occur with "if not exists() then insert" can be found here - #108903 – Set

@Set ..Understood. This is clearly not a great way to handle referential constraint. I was simply trying to provide some insight – Personally

@John Hartsock - your solution has a loophole: if one of the two databases is restored from a backup, triggers do not fire of course. This is how we can end up with orphan rows. – Nichrome

@AlexKuznetsov Exactly. As I explained this is not the best approach but a potential work around. – Personally

This is just so wrong... I just hope the OP realizes that just the fact that he is asking of something like this, is a symptom that he is most likely doing something wrong... let alone think about triggers.. – Congressional

@Marco As I Posted in my answer "Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you." I explained that this is probably not a good idea. – Personally

@JohnHartsock fair enough. – Congressional

Answer 2

If you need rock solid integrity, have both tables in one database, and use an FK constraint. If your parent table is in another database, nothing prevents anyone from restoring that parent database from an old backup, and then you have orphans.

This is why FK between databases is not supported.

Nichrome

Answer 3

You could use check constraint with a user defined function to make the check. It is more reliable than a trigger. It can be disabled and reenabled when necessary same as foreign keys and rechecked after a database2 restore.

CREATE FUNCTION dbo.fn_db2_schema2_tb_A
(@column1 INT) 
RETURNS BIT
AS
BEGIN
    DECLARE @exists bit = 0
    IF EXISTS (
      SELECT TOP 1 1 FROM DB2.SCHEMA2.tb_A 
      WHERE COLUMN_KEY_1 =  @COLUMN1
    ) BEGIN 
         SET @exists = 1 
      END;
      RETURN @exists
END
GO

ALTER TABLE db1.schema1.tb_S
  ADD CONSTRAINT CHK_S_key_col1_in_db2_schema2_tb_A
    CHECK(dbo.fn_db2_schema2_tb_A(key_col1) = 1)

Leonerd

this is a better solution than accepted answer and you can also re-use it on multiple tables – Stokehold

I like it but you could simplify the function to DECLARE @exists BIT = 0, SELECT TOP(1) @exists = CAST(1 AS BIT) FROM DB2.SCHEMA2.tb_A WHERE COLUMN_KEY_1 = @COLUMN1;, RETURN @exists; – Braiding

Answer 4

In my experience, the best way to handle this when the primary authoritative source of information for two tables which are related has to be in two separate databases is to sync a copy of the table from the primary location to the secondary location (using T-SQL or SSIS with appropriate error checking - you cannot truncate and repopulate a table while it has a foreign key reference, so there are a few ways to skin the cat on the table updating).

Then add a traditional FK relationship in the second location to the table which is effectively a read-only copy.

You can use a trigger or scheduled job in the primary location to keep the copy updated.

Lessielessing

Re. "You can trigger or scheduled job in the primary location to keep the copy updated": Why not just use SQL Server Replication (specifically the Transaction vs. Merge type since the Subscriber's copy (the copy that has the Tables needing Foreign Key Constraints) just needs to be read-only)? See: link – Debutante

@Debutante yes, you can certainly use replication to keep a copy of the table updated in a remote database. – Lessielessing

Answer 5

Achieving referential integrity accross databases is not an easy task.

Here is a list of frequently employed mechanisms:

• Clone & Sync: The referenced data is regularly cloned/merged into the referencing database. This may be suitable if the referenced data is rarely changing. You end up with two physical copies of the same data, and need a reliable process to keep them in sync (e.g. with an ETL pipeline).
• Triggers: Changes to the referencing data and the referenced data are caught by SQL triggers, which ensure referential integrity. However, triggers can be slow, and may not fire at a database restore. It cannot hurt to run scheduled consistency checks as part of the operations monitoring. Write access to the referenced database is required for installing and maintaining the trigger.
• Check constraints: SQL-Server offers user-defined contraints, which ensure that every row satisfies a given condition. One can exploit this functionality by writing a user defined function that checks the existence of a row in the referenced data, and then use this function as a CHECK's predicate in the referencing table. This does not catch changes in the referenced data. It is an RDBMS-specific solution, but works accross server boundaries (e.g. using linked servers). It is a good choice for referencing globally unique IDs, such as article codes in a company's ERP system, which never get deleted or re-assigned.
• Re-think database architecture: When all the above mechanisms are unsatisfactory, multiple databases may be merged in a single database. The originating database names can become schema names, allowing effective grouping of database objects.

Buoyancy

Answer 6

The short answer is that SQL Server (as of SQL 2008) does not support cross database foreign keys--as the error message states.

While you cannot have declarative referential integrity (the FK), you can reach the same goal using triggers. It's a bit less reliable, because the logic you write may have bugs, but it will get you there just the same.

See the SQL docs @ http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx Which state:

Triggers are often used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the table creation statements (ALTER TABLE and CREATE TABLE); however, DRI does not provide cross-database referential integrity. To enforce referential integrity (rules about the relationships between the primary and foreign keys of tables), use primary and foreign key constraints (the PRIMARY KEY and FOREIGN KEY keywords of ALTER TABLE and CREATE TABLE). If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed (fired).

There is also an OK discussion over at SQLTeam - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31135

Set

Answer 7

As the error message says, this is not supported on sql server. The only way to ensure refrerential integrity is to work with triggers.

Rickets

Can you explain me with an example – Fala