How to set foreign key in table in one database that references a table in another database [closed]

Question

Msg 1768, Level 16, State 0, Line 1
Foreign key 'FK_id' references object 'dbo.student' which is not a user table.

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

Answer 1

A FOREIGN KEY constraint cannot reference a table in a different database. It's by design.

Here's an example:

CREATE DATABASE test1
GO

USE test1;
GO

CREATE TABLE tableA (
     PK int PRIMARY KEY CLUSTERED 
    ,someColumn varchar(50)
)
GO

CREATE DATABASE test2
GO

USE test2;
GO

CREATE TABLE tableB (
     PK int PRIMARY KEY CLUSTERED 
    ,aForeignKey int NULL FOREIGN KEY REFERENCES test1.dbo.tableA(PK)
)
GO

This will throw an error:

Msg 1763, Level 16, State 0, Line 19
Cross-database foreign key references are not supported. Foreign key 'test1.dbo.tableA'.
Msg 1750, Level 16, State 0, Line 19
Could not create constraint. See previous errors.

An (ugly) workaround is a trigger that checks whether the data is present in the referenced table:

CREATE TRIGGER TR_Fake_FK ON dbo.tableB
    FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT *
        FROM INSERTED AS I
        WHERE NOT EXISTS (
            SELECT *
            FROM test1.dbo.tableA AS A
            WHERE I.aForeignKey = A.PK
        )
    )
    BEGIN
        RAISERROR('Violation of fake constraint',16,1);
        ROLLBACK;
    END

END

The trigger won't handle cascading behavior of foreign keys and you'll also need another trigger on the referenced table to handle UPDATEs and DELETEs. In other words: don't do it.

EDIT: In case you're wondering if a VIEW or a SYNONYM to "copy" the referenced table to the second database will work (which seems to be what you're trying to do), it won't.