question

mhashemi avatar image
mhashemi asked

Using synonyms table for referential integrity

Need to reference a field in Synonym table. What is the alternatives?
referential-integrity
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HUH??? Your question doesn't make any sense. Do you have a synonym table or are you talking about sys.synonyms? What do you mean by alternative? As in an alternative to what? Some kind of explanation about what you are trying to do is required for anybody to be able to help here.
0 Likes 0 ·
I am referencing a field in Synonym table as foreign key. Example: ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_REGION_FK FOREIGN KEY (REGION_ID) REFERENCES REG_TABLE (REGION_ID) ON DELETE NO ACTION ON UPDATE NO ACTION GO REG_TABLE is a synonym table
0 Likes 0 ·
That isn't a synonym, it is a lookup table. A synonym in sql server is something completely different. What is the question?
0 Likes 0 ·
I am using a synonym table as a reference for foreign key. mhashemi
0 Likes 0 ·
What is your question???
0 Likes 0 ·
and you should use comments instead of adding a new answer every time.
0 Likes 0 ·
DenisT avatar image
DenisT answered
The foreign key constraint can only exist within the same database. I assume this is what your problem is because your synonym points to a table in a different database. See [this answer][1] for a workaround. [1]: http://stackoverflow.com/questions/4452132/add-foreign-key-relationship-between-two-databases
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

EricI avatar image
EricI answered

For referential integrity to be defined, the tables need to be in the same database. But another solution that uses your synonym to copy data to a local table and have the RI defined against the local table might be an alternative option to consider in the future.


10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.