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.
How to set foreign key in table in one database that references a table in another database [closed]
-
2What exact statement did you try that gave you this error?– ypercubeᵀᴹMar 5, 2015 at 12:07
1 Answer
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.