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