Sql-server – Getting around Primary Key requiring non-nullable columns

constraintforeign keyprimary-keysql serversql server 2014

Word Problem

I need to be able to create a table that has a column which is a foreign key and is nullable, but is also part of the (composite) primary key. Obviously the problem is that (at least with SQL Server 2014) I cannot have a nullable column in a primary key.

Example

CREATE TABLE Test
(
    Id1 INT NOT NULL,
    Id2 INT NOT NULL,
    PRIMARY KEY (Id1, Id2)
)

CREATE TABLE Test2
(
    TestId1 INT NOT NULL,
    TestId2 INT NULL,
    PRIMARY KEY (TestId1, TestId2),
    FOREIGN KEY (TestId1, TestId2) REFERENCES Test (Id1, Id2)
)

The error is on the Primary Key of Test2:

A primary key constraint cannot be defined on the nullable column 'TestId2' in the table or user-defined table type …

My workaround:

CREATE TABLE Test3
(
    TestId1 INT NOT NULL,
    TestId2Calc AS COALESCE(TestId2, -1) PERSISTED NOT NULL,
    TestId2 INT NULL,
    PRIMARY KEY (TestId1, TestId2Calc),
    FOREIGN KEY (TestId1, TestId2) REFERENCES Test (Id1, Id2)
)

But this feels dirty. Is this my only option?

Best Answer

There is no physical difference between Primary Key and a Unique Clustered Index. So unless you are using some tool that requires a Primary Key, you can do

CREATE TABLE Test2
(
    TestId1 INT NOT NULL,
    TestId2 INT NULL,
    UNIQUE CLUSTERED (TestId1, TestId2),
    FOREIGN KEY (TestId1, TestId2) REFERENCES Test (Id1, Id2)
)
Related Question