Chave estrangeira para chave não primária

Eu tenho uma tabela que contém dados e uma dessas linhas precisa existir em outra tabela. Então, eu quero uma chave estrangeira para manter a integridade referencial.

CREATE TABLE table1 ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, AnotherID INT NOT NULL, SomeData VARCHAR(100) NOT NULL ) CREATE TABLE table2 ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, AnotherID INT NOT NULL, MoreData VARCHAR(30) NOT NULL, CONSTRAINT fk_table2_table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID) ) 

No entanto, como você pode ver, a tabela que eu chave estrangeira para, a coluna não é o PK. Existe uma maneira de criar essa chave estrangeira, ou talvez uma maneira melhor de manter essa integridade referencial?

Se você realmente quiser criar uma chave estrangeira para uma chave não primária, ela DEVE ser uma coluna que tenha uma restrição exclusiva.

De livros online :

Uma restrição FOREIGN KEY não precisa estar vinculada apenas a uma restrição PRIMARY KEY em outra tabela; Ele também pode ser definido para referenciar as colunas de uma restrição UNIQUE em outra tabela.

Então, no seu caso, se você fizer AnotherID único, será permitido. Se você não pode aplicar uma restrição única, você está sem sorte, mas isso realmente faz sentido se você pensar sobre isso.

Embora, como já foi mencionado, se você tem uma chave primária perfeitamente boa como chave candidata, por que não usar isso?

Como outros apontaram, idealmente, a chave estrangeira seria criada como uma referência a uma chave primária (geralmente uma coluna IDENTIDADE). No entanto, não vivemos em um mundo ideal e, às vezes, até mesmo uma “pequena” alteração em um esquema pode ter efeitos de ondulação significativos na lógica do aplicativo.

Considere o caso de uma tabela Customer com uma coluna SSN (e uma chave primária burra) e uma tabela Claim que também contém uma coluna SSN (preenchida pela lógica de negócios dos dados do Cliente, mas não existe FK). O design é falho, mas está em uso há vários anos e três aplicativos diferentes foram criados no esquema. Deveria ser óbvio que arrancar o Claim.SSN e colocar uma relação PK-FK real seria ideal, mas também seria uma revisão significativa . Por outro lado, colocar uma restrição UNIQUE em Customer.SSN e adicionar um FK em Claim.SSN poderia fornecer integridade referencial, com pouco ou nenhum impacto sobre os aplicativos.

Não me entenda mal, sou a favor da normalização, mas às vezes o pragmatismo vence o idealismo. Se um design medíocre pode ser ajudado com um band-aid, a cirurgia pode ser evitada.

Necromancing.
Eu suponho que quando alguém chega aqui, ele precisa de uma chave estrangeira para coluna em uma tabela que contém chaves não exclusivas.

O problema é que, se você tiver esse problema, o esquema do database será desordenado.

Por exemplo, você está mantendo as salas em uma tabela, com uma chave primária de sala, um campo DateFrom e DateTo e outro uid, aqui RM_ApertureID para acompanhar a mesma sala e um campo de exclusão suave, como RM_Status, em que 99 significa ‘eliminado’ e <> 99 significa ‘ativo’.

Portanto, quando você cria a primeira sala, insere RM_UID e RM_ApertureID como o mesmo valor de RM_UID. Então, quando você encerra a sala em uma data e a restabelece com um novo intervalo de datas, RM_UID é newid (), e o RM_ApertureID da input anterior se torna o novo RM_ApertureID.

Portanto, se esse for o caso, RM_ApertureID é um campo não exclusivo e, portanto, você não pode definir uma chave estrangeira em outra tabela.

E não há como definir uma chave estrangeira para uma coluna / índice não-exclusiva, por exemplo, em T_ZO_REM_AP_Raum_Reinigung (WHERE RM_UID é, na verdade, RM_ApertureID).
Mas para proibir valores inválidos, você precisa definir uma chave estrangeira, caso contrário, o lixo de dados é o resultado mais cedo ou mais tarde …

Agora, o que você pode fazer neste caso (com exceção de rewrite todo o aplicativo) é inserir uma restrição CHECK, com uma function escalar verificando a presença da chave:

 IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_Constaint_ValidRmApertureId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[fu_Constaint_ValidRmApertureId] GO CREATE FUNCTION [dbo].[fu_Constaint_ValidRmApertureId]( @in_RM_ApertureID uniqueidentifier ,@in_DatumVon AS datetime ,@in_DatumBis AS datetime ,@in_Status AS integer ) RETURNS bit AS BEGIN DECLARE @bNoCheckForThisCustomer AS bit DECLARE @bIsInvalidValue AS bit SET @bNoCheckForThisCustomer = 'false' SET @bIsInvalidValue = 'false' IF @in_Status = 99 RETURN 'false' IF @in_DatumVon > @in_DatumBis BEGIN RETURN 'true' END IF @bNoCheckForThisCustomer = 'true' RETURN @bIsInvalidValue IF NOT EXISTS ( SELECT T_Raum.RM_UID ,T_Raum.RM_Status ,T_Raum.RM_DatumVon ,T_Raum.RM_DatumBis ,T_Raum.RM_ApertureID FROM T_Raum WHERE (1=1) AND T_Raum.RM_ApertureID = @in_RM_ApertureID AND @in_DatumVon >= T_Raum.RM_DatumVon AND @in_DatumBis <= T_Raum.RM_DatumBis AND T_Raum.RM_Status <> 99 ) SET @bIsInvalidValue = 'true' -- IF ! RETURN @bIsInvalidValue END GO IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] GO -- ALTER TABLE dbo.T_AP_Kontakte WITH CHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung WITH NOCHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] CHECK ( NOT ( dbo.fu_Constaint_ValidRmApertureId(ZO_RMREM_RM_UID, ZO_RMREM_GueltigVon, ZO_RMREM_GueltigBis, ZO_RMREM_Status) = 1 ) ) GO IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung CHECK CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] GO