Adicionar relacionamento de chave estrangeira entre dois bancos de dados

Eu tenho duas tabelas em dois bancos de dados diferentes. Na tabela1 (É no database1) existe uma coluna chamada coluna1 e é uma chave primária. Agora na tabela 2 (Está no databse2) existe uma coluna chamada coulmn2 e eu quero adicioná-la como uma chave estrangeira.

Eu tentei adicionar e me deu o seguinte erro:

Msg 1763, nível 16, estado 0, linha 1
Referências de chave estrangeira de database cruzado não são suportadas. Chave estrangeira Database2.table2.

Msg 1750, nível 16, estado 0, linha 1
Não foi possível criar restrição. Veja os erros anteriores.

Como faço isso desde que as tabelas estão em bancos de dados diferentes.

Você precisaria gerenciar a restrição referencial nos bancos de dados usando um Trigger.


Basicamente, você cria uma inserção, aciona a atualização para verificar a existência da chave na tabela de chave primária. Se a chave não existir, reverta a inserção ou atualização e, em seguida, manipule a exceção.

Exemplo:

Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update As Begin If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN -- Handle the Referential Error Here END END 

Editado: Só para esclarecer. Esta não é a melhor abordagem para impor a integridade referencial. Idealmente, você quer as duas tabelas no mesmo database, mas se isso não for possível. Então, o acima é um trabalho potencial para você.

Se você precisar de integridade sólida, tenha as duas tabelas em um database e use uma restrição FK. Se sua tabela pai estiver em outro database, nada impede que alguém restaure esse database pai de um backup antigo e, em seguida, você terá órfãos.

É por isso que o FK entre bancos de dados não é suportado.

Na minha experiência, a melhor maneira de lidar com isso quando a principal fonte autoritativa de informações para duas tabelas relacionadas tem que estar em dois bancos de dados separados é sincronizar uma cópia da tabela do local principal para o local secundário (usando T- SQL ou SSIS com verificação de erros apropriada – você não pode truncar e preencher novamente uma tabela enquanto ela tiver uma referência de chave estrangeira, portanto, há algumas maneiras de eliminar o gato na atualização da tabela).

Em seguida, adicione um relacionamento FK tradicional no segundo local à tabela, que é efetivamente uma cópia somente leitura.

Você pode usar um gatilho ou um trabalho agendado no local principal para manter a cópia atualizada.

Você poderia usar a restrição de verificação com uma function definida pelo usuário para fazer a verificação. É mais confiável que um gatilho. Ele pode ser desabilitado e reativado quando necessário, da mesma forma que as foreign keys e verificado novamente após uma restauração do database2.

 CREATE FUNCTION dbo.fn_db2_schema2_tb_A (@column1 INT) RETURNS BIT AS BEGIN DECLARE @exists bit = 0 IF EXISTS ( SELECT TOP 1 1 FROM DB2.SCHEMA2.tb_A WHERE COLUMN_KEY_1 = @COLUMN1 ) BEGIN SET @exists = 1 END; RETURN @exists END GO ALTER TABLE db1.schema1.tb_S ADD CONSTRAINT CHK_S_key_col1_in_db2_schema2_tb_A CHECK(dbo.fn_db2_schema2_tb_A(key_col1) = 1) 

A resposta curta é que o SQL Server (a partir do SQL 2008) não suporta foreign keys de database cruzado – como indica a mensagem de erro.

Enquanto você não pode ter integridade referencial declarativa (o FK), você pode alcançar o mesmo objective usando gatilhos. É um pouco menos confiável, porque a lógica que você escreve pode ter bugs, mas você chegará lá do mesmo jeito.

Veja os docs SQL @ http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx Que estado:

Os gatilhos são frequentemente usados ​​para impor regras de negócios e integridade de dados. O SQL Server fornece integridade referencial declarativa (DRI) através das instruções de criação de tabela (ALTER TABLE e CREATE TABLE); no entanto, a DRI não fornece integridade referencial entre bancos de dados. Para impor a integridade referencial (regras sobre os relacionamentos entre as chaves primária e externa das tabelas), use restrições de chave primária e externa (as palavras-chave PRIMARY KEY e FOREIGN KEY de ALTER TABLE e CREATE TABLE). Se existirem restrições na tabela de acionadores, elas serão verificadas após a execução do acionador INSTEAD OF e antes da execução do acionador AFTER. Se as restrições forem violadas, as ações do gatilho INSTEAD OF serão retrocedidas e o gatilho AFTER não será executado (triggersdo).

Há também uma discussão OK no SQLTeam – http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31135

Como a mensagem de erro diz, isso não é suportado no sql server. A única maneira de garantir a integridade refratária é trabalhar com gatilhos.