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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Add Foreign Key relationship between two Databases

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to foreign-keys