Post reply

How to refer a Primary key as Foreign key in different database?

  • May 28, 2008 at 12:18 pm

    #188275

    Hi I am having two database (Test1,Test2)

    In TEST1.dbo.Employee table ID is PK.

    How to refer this ID as FK in TEST2.dbo.Address?

    Thanks

  • May 28, 2008 at 12:24 pm

    #821383

    You can't. SQL Server doesn't support cross-database referential integrity enforcement through the DRI statements. You can set up a triggers, one to check the child table in the event of deletes from the parent table and another in the child to verify data in the parent prior to inserts or updates. That's your only option.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 28, 2008 at 2:11 pm

    #821454

    You can also use a check constraint to handle the inserts and updates rather than a trigger. You will need a trigger on at least one of the tables.

    This does work, but it will be a performance hit. You have to weigh the pros and cons in your database to determine if it is a good idea.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply