January 25, 2005 at 5:30 pm
Hi
I need some help, I need to create a Foreign key constraint that references to a table in a external database which resides in the same server, I'm using the next sintax:
use Northwind
go
alter table TABLE_NAME
add constraint FK_REF_NAME foreign key (COLUMN_NAME)
references PUBS..TABLE_NAME(COLUMN_NAME)
go
SQL returns the next message:
Server: Msg 1763, Level 16, State 1, Line 2
Cross-database foreign key references are not supported. Foreign key 'pubs..TABLE_NAME'.
What can I do?
January 25, 2005 at 5:55 pm
I don't know if there is a better way, but I created triggers on the FK table that queried the parent table in the other database. If the FK is not in the other table, you can RAISERROR.
Also, if the parent table is a lookup table (particularly if it holds fairly static data such as State abbreviations or zip codes) you could replicate the parent table into the current database. Then you don't have cross-database marshalling to slow you down.
Luck, Dave
January 25, 2005 at 5:57 pm
Use replication and maintain an exact copy of the table in the same database as the dependant table.
Or use triggers instead of DRI to enforce integrity, becasue you can use cross-database joins in the trigger T-SQL.
January 25, 2005 at 6:17 pm
I wanted to avoid using triggers because the performance, but I see that there's not another way that using triggers or replication.
Thank you very much
January 25, 2005 at 6:29 pm
I wanted to avoid using triggers because the performance, but I see that there's not another way that using triggers or replication.
Thank you very much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply