Подтвердить что ты не робот

Внешний ключ к непервичному ключу

У меня есть таблица, в которой хранятся данные, и одна из этих строк должна существовать в другой таблице. Поэтому я хочу, чтобы внешний ключ поддерживал ссылочную целостность.

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

Однако, как вы видите, внешний ключ таблицы I, столбец не является PK. Есть ли способ создать этот внешний ключ или, возможно, лучший способ сохранить эту ссылочную целостность?

Ответ 1

Если вы действительно хотите создать внешний ключ для непервичного ключа, он ДОЛЖЕН быть столбцом с уникальным ограничением.

Из Интернет-магазин:

Ограничение FOREIGN KEY не обязательно должно быть связано только с PRIMARY Ограничение KEY в другой таблице; его также можно определить для ссылки столбцы ограничения UNIQUE в другой таблице.

Итак, в вашем случае, если вы сделаете AnotherID уникальным, оно будет разрешено. Если вы не можете применить уникальное ограничение, вам не повезло, но это действительно имеет смысл, если вы думаете об этом.

Хотя, как уже упоминалось, если у вас есть совершенно хороший первичный ключ в качестве ключа-кандидата, почему бы не использовать его?

Ответ 2

Как указывали другие, в идеале внешний ключ будет создан как ссылка на первичный ключ (обычно столбец IDENTITY). Однако мы не живем в идеальном мире, и иногда даже "небольшое" изменение схемы может иметь значительные эффекты пульсации для логики приложения.

Рассмотрим случай таблицы Customer с столбцом SSN (и немым первичным ключом) и таблицей требований, которая также содержит столбец SSN (заполненный бизнес-логикой из данных Customer, но FK не существует). Конструкция ошибочна, но используется уже несколько лет, и на схеме были построены три разных приложения. Должно быть очевидно, что срывание заявки .SSN и установление реальных отношений PK-FK было бы идеальным, но также было бы значительным капитальным ремонтом. С другой стороны, включение ограничения UNIQUE в Customer.SSN и добавление FK на Claim.SSN может обеспечить ссылочную целостность, практически не влияя на приложения.

Не поймите меня неправильно, я все для нормализации, но иногда прагматизм побеждает идеализм. Если посредственный дизайн может помочь с помощью бандажа, хирургию можно избежать.

Ответ 3

Necromancing.

Я предполагаю, что когда кто-то приземляется здесь, ему нужен внешний ключ для столбца в таблице, содержащей не уникальные ключи.

Проблема в том, что если у вас есть эта проблема, схема базы данных денормализуется.

Вы, например, сохраняете комнаты в таблице с первичным ключом номер-uid, поле DateFrom и DateTo и другим uid, здесь RM_ApertureID, чтобы отслеживать одну и ту же комнату и поле мягкого удаления, как RM_Status, где 99 означает "удаленный", а < > 99 означает "активный".

Итак, когда вы создаете первую комнату, вы вставляете RM_UID и RM_ApertureID в то же значение, что и RM_UID. Затем, когда вы завершаете комнату до даты и восстанавливаете ее с новым диапазоном дат, RM_UID является newid(), а RM_ApertureID из предыдущей записи становится новым RM_ApertureID.

Итак, если это так, RM_ApertureID - это не уникальное поле, поэтому вы не можете установить внешний ключ в другой таблице.

И нет способа установить внешний ключ для неуникального столбца/индекса, например. в T_ZO_REM_AP_Raum_Reinigung (WHERE RM_UID на самом деле RM_ApertureID).

Но чтобы запретить недопустимые значения, вам нужно установить внешний ключ, иначе данные-мусор будут результатом раньше, чем позже...

Теперь то, что вы можете сделать в этом случае (не переписывая все приложение), вставляет ограничение CHECK со скалярной функцией, проверяющей наличие ключа:

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