Czy kolumna zerowa może być częścią klucza podstawowego?

Tworzę bazę danych SQL Server 2012 i mam pytanie dotyczące relacji jeden do zera lub jeden.

Mam dwa stoły Codesi HelperCodes. Kod może mieć zero lub jeden kod pomocniczy. To jest skrypt SQL, aby utworzyć te dwie tabele i ich relacje:

CREATE TABLE [dbo].[Code]
(
    [Id] NVARCHAR(20) NOT NULL, 
    [Level] TINYINT NOT NULL, 
    [CommissioningFlag] TINYINT NOT NULL, 
    [SentToRanger] BIT NOT NULL DEFAULT 0, 
    [LastChange] NVARCHAR(50) NOT NULL, 
    [UserName] NVARCHAR(50) NOT NULL, 
    [Source] NVARCHAR(50) NOT NULL, 
    [Reason] NVARCHAR(200) NULL, 
    [HelperCodeId] NVARCHAR(20) NULL,
    CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ),
    CONSTRAINT [FK_Code_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level]),
    CONSTRAINT [FK_Code_HelperCode]
       FOREIGN KEY ([HelperCodeId])
        REFERENCES [dbo].[HelperCode] ([HelperCodeId])
)

CREATE TABLE [dbo].[HelperCode]
(
    [HelperCodeId] NVARCHAR(20) NOT NULL, 
    [Level] TINYINT NOT NULL, 
    [CommissioningFlag] TINYINT NOT NULL, 
    [LastChange] NVARCHAR(50) NOT NULL,
    CONSTRAINT [PK_HelperCode] PRIMARY KEY CLUSTERED
    (
        [HelperCodeId] ASC
    ),
    CONSTRAINT [FK_HelperCode_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level])
)

Czy to jest poprawne?

Kod i Kod pomocniczy to różne podmioty. HelperCode może być używany (żaden kod go nie odwołuje) lub używany (tylko jeden kod go odwołuje).

Może Code.HelperCodeId musi być częścią klucza podstawowego tabeli kodów. Ale nie jestem pewien, czy pusta kolumna może być częścią podstawowej. W ten sposób chcę zapobiec, aby dwa lub więcej kodów odwoływało się do tego samego kodu pomocniczego.

Aby odpowiedzieć na pytanie w tytule, nie, wszystkie podstawowe kolumny muszą być NOT NULL.

Ale bez zmiany projektu tabel, możesz dodać filtrowany indeks do Code (HelperCodeId)kolumny:

CREATE UNIQUE INDEX 
    FUX_Code_HelperCodeId
ON dbo.Code 
    (HelperCodeId) 
WHERE 
    HelperCodeId IS NOT NULL ;

Filtr ( WHERE HelperCodeId IS NOT NULL) jest potrzebny ze względu na sposób, w jaki SQL Server traktuje wartości zerowe w unikalnych ograniczeniach i unikalnych indeksach. Bez filtra, SQL-Server nie pozwoli więcej niż jeden wiersz z NULLw HelperCodeId.

Alternatywnym rozwiązaniem byłoby usunięcie HelperCodeIdz Codei dodanie trzeciej tabeli, w której będą przechowywane relacje Code- HelperCode. Relacja między tymi dwoma podmiotami wydaje się być zerowa lub jedna do zerowej lub jedna (oba Kod nie może mieć kodu pomocniczego, a kod pomocniczy może być używany przez żaden kod):

CREATE TABLE [dbo].[Code]
(
    [Id] NVARCHAR(20) NOT NULL, 
    [Level] TINYINT NOT NULL, 
    [CommissioningFlag] TINYINT NOT NULL, 
    [SentToRanger] BIT NOT NULL DEFAULT 0, 
    [LastChange] NVARCHAR(50) NOT NULL, 
    [UserName] NVARCHAR(50) NOT NULL, 
    [Source] NVARCHAR(50) NOT NULL, 
    [Reason] NVARCHAR(200) NULL, 
    -- 
    -- removed:   [HelperCodeId] NVARCHAR(20) NULL,
    -- 
    CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ),
    CONSTRAINT [FK_Code_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level]),
) ;

HelperCode pozostaje bez zmian:

CREATE TABLE [dbo].[HelperCode]
(
    [HelperCodeId] NVARCHAR(20) NOT NULL, 
    [Level] TINYINT NOT NULL, 
    [CommissioningFlag] TINYINT NOT NULL, 
    [LastChange] NVARCHAR(50) NOT NULL,
    CONSTRAINT [PK_HelperCode] PRIMARY KEY CLUSTERED
    (
        [HelperCodeId] ASC
    ),
    CONSTRAINT [FK_HelperCode_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level])
) ;

Dodatkowa tabela będzie miała dwa ograniczenia UNIQUE(lub jeden główny i jeden unikalny), aby zapewnić, że każdy Kod jest powiązany (maksymalnie) z jednym Kodem Pomocniczym, a każdy Kod Pomocniczy jest powiązany z (maksymalnie) jednym Kodem. Obie kolumny to NOT NULL:

CREATE TABLE [dbo].[Code_HelperCode]
(
    [CodeId] NVARCHAR(20) NOT NULL, 
    [HelperCodeId] NVARCHAR(20) NOT NULL, 
    CONSTRAINT [UQ_Code_HelperCode_CodeId]
       UNIQUE (CodeId),
    CONSTRAINT [UQ_Code_HelperCode_HelperCodeId]
       UNIQUE (HelperCodeId),
    CONSTRAINT [FK_HelperCode_Code]
       FOREIGN KEY ([CodeId])
        REFERENCES [dbo].[Code] ([Id]),
    CONSTRAINT [FK_Code_HelperCode]
       FOREIGN KEY ([HelperCodeId])
        REFERENCES [dbo].[HelperCode] ([HelperCodeId])
) ;

Zamiast tego spróbuj użyć unikalnego ograniczenia. Podobno standard ANSI zadeklarował wartości null jako klucz podstawowy jako nieważny, ale nigdy nie widziałem tego standardu i nie chcę go kupować, aby to sprawdzić.

Brak kluczy zerowych wydaje się być jedną z tych rzeczy, w które programiści bardzo mocno wierzą w taki czy inny sposób. Preferuję ich używanie, ponieważ uważam, że jest to pomocne w tabelach odnośników zawierających podpowiedzi i powiązane dane dla pól kombinowanych, które nie są wypełnione.

Nauczono mnie, że wartość Null wskazuje, że zmienna nigdy nie była ustawiona, a pusta wartość wskazuje, że wartość została ustawiona w przeszłości. Oczywiście to programista musi zdefiniować aplikację, ale uważam, że niedorzeczne jest zezwalanie na puste klucze podstawowe, ale nie na klucze podstawowe zerowe.