Could a null column be part of a primary key?

Question

I'm developing a SQL Server 2012 database and I have a question about a One-to-Zero-Or-One relationship.

I have two tables, Codes and HelperCodes. A code could have zero or one helper code. This is the sql script to create these two tables and their relationships:

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

Is that correct?

A Code and a HelperCode are both different entities. A HelperCode can be an used (none Code references it), or used (only one Code references it).

Maybe Code.HelperCodeId must be part of Code table primary key. But I'm not sure if a null column could be part of a primary. Doing this, I want to prevent that two or more Codes reference the same HelperCode.

Answer 1

To answer the question in the title, no, all the primary columns have to be NOT NULL.

But without altering the design of the tables, you could add a filtered index on the Code (HelperCodeId) column:

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

The filter (WHERE HelperCodeId IS NOT NULL) is needed because of the way SQL-Server treats nulls in unique constraints and unique indexes. Without the filter, SQL-Server would not allow more than one row with NULL in HelperCodeId.

---

An alternative design would be remove the HelperCodeId from Code and add a third table that will store the Code - HelperCode relationships. The relationship between the two entities seem to be Zero-or-One--to--Zero-or-One (both a Code can have no HelperCode and a HelperCode might be used by no Code):

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 remains unchanged:

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

The additional table will have two UNIQUE contraints (or one primary and one unique) to ensure that every Code is related to (maximum) one HelperCode and each HelperCode is related to (maximum) one Code. Both columns would be 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])
) ;

Answer 2

Try using a unique constraint instead. Supposedly the ANSI standard declared nulls as a primary key to be invalid, but I have never seen the standard and don't wish to purchase it to verify this.

Not having null keys seems to be one of those things that developers have a very hard belief on one way or the other. My preference is to use them because I find it helpful for lookup tables containing tooltips and related data for comboboxes that are not been populated.

I was taught that Null value indicates that a variable has never been set and empty value indicates that the value has been set in the past. Of course this is up to the developer to define for the application, but I find it nonsensical to allow empty primary keys but not null primary keys.