1

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.

closed as unclear what you're asking by marc_s, Max Vernon, Paul White, Colin 't Hart, RLF Mar 6 '15 at 14:17

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What exact statement did you try that gave you this error? – ypercubeᵀᴹ Mar 5 '15 at 12:07
12

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.