15

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.

  • 1
    Why would you want HelperCodeId to be part of the PK? Is it, by any chance, because you want to prevent two or more Codes to reference the same HelperCode? – Andriy M Sep 18 '15 at 8:17
  • Yes, I want to prevent that two or more codes reference the same HelperCode. Another option is to set HelperCodeId column as Unique. – VansFannel Sep 18 '15 at 8:21
  • @ypercube Could you please add the complete sql sentence as answer? I don't work very often with sql and I don't know how to do it. Thanks. – VansFannel Sep 18 '15 at 8:31
  • Conceptually, the DBMS engineers couldn't have permitted NULLs in primary keys without going against the entire relational data model. And the relational model is part of what makes relational databases so useful. You may or may not be interested in this aspect, but it's important to point out for future visitors. – Walter Mitty Sep 18 '15 at 10:33
  • @WalterMitty I never understood why having a null value in a PK would destroy the value that an RDBMS brings. I have heard it many times. Can you elaborate? – usr Sep 19 '15 at 22:11
23

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])
) ;
  • Thanks, you could alter the design if you want. I could learn a lot. – VansFannel Sep 18 '15 at 8:52
  • Thanks for your design. I haven't added a new table because I thought these tables are used only in many-to-many relationship. – VansFannel Sep 19 '15 at 6:16
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.