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.
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.