0

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?

4

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)
)
  • Can this still be the target of FOREIGN KEY references? (Not that it matters, I don't think I can FOREIGN KEY reference my current situation.) – 410_Gone Sep 6 '18 at 13:51
  • SQL Server allows unique indexes to be the target of an FOREIGN KEY. But you still wouldn't be able to insert a NULL in Test2.TestId2 because of the constraint referencing Test. So the model still has issues. – David Browne - Microsoft Sep 6 '18 at 13:53
  • 1
    I was able to insert NULL into the Test2Id of both your version and mine. (Though I was also able to insert values that are not in Test, that's not part of my issue here, I have a second FK to prohibit that.) – 410_Gone Sep 6 '18 at 13:56
  • Here's a DBFiddle, Test2 is your version, Test3 is my workaround: dbfiddle.uk/… – 410_Gone Sep 6 '18 at 13:59

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.