Could a null column be part of a primary key?
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])
) ;