0

I'm in MS SQL 2016. I'm a bit confused. I have a field that I have put a unique index on. The DocID is not a primary key, but it will be unique (and is NULLable):

CREATE UNIQUE INDEX [IX_Table_Unique_DocID]
ON [dbo].[Table]([DocID] ASC) 
WHERE [DocID] IS NOT NULL;

So when I am creating a new table that will reference DocID, I want to make it a FK.

,constraint fk_Table2_DocID foreign key (DociD) references Table (DocID)

However, I get the below error:

There are no primary or candidate keys in the referenced table 'Table' that match the referencing column list in the foreign key 'fk_Table2_DocID'.

What I am reading from MSDN shows that this is the way to create a UNIQUE constraint on a NULLable field. I am not finding anything that showing me other wise :/

Can someone point me in the right direction? I don't even mind if it's a "Here let me google that for you" link because I just can't seem to find it :)

2
  • You can create a foreign key that references a unique index but not a unique filtered index Jan 19 at 15:57
  • The unique filtered index will functionally serve as a unique constraint to ensure no non-NULL duplicate DocID values. However, it doesn't seem SQL Server will allow a unique filtered index to be referenced by a foreign key constraint.
    – Dan Guzman
    Jan 19 at 15:58

1 Answer 1

2

In SQL Server a foreign key constraint can be created between one table to another.

It requires a unique index on the referenced column.

This can be created by either CREATE UNIQUE INDEX or a constraint based unique index (UNIQUE or PRIMARY KEY).

You are using CREATE UNIQUE INDEX but you also have a filter condition WHERE [DocID] IS NOT NULL

There is no facility to create a foreign key that references the subset of the table defined by a filtered index.

SQL Server would only allow one NULL DocID in a non filtered unique index. Presumably this is why you need the filtered index.

You would need another different table with all your distinct DocID - then you can put a non filtered unique index on that and reference that in foreign keys.

The only other way I can think of would be some kludgy workaround. For example if all legitimate DocID are positive integers you could assign unique negative integers to the ones where this is not applicable (instead of NULL) and then create a unique index on that.

Table2 could then reference Table1(DocID) in a foreign key and additionally have a check constraint that DocID > 0 to ensure it isn't referencing any of these bogus ids.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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