-1

Using SQL Server on Azure: 12.0.2000.8

I've defined the following unique index for TablePrimary:

CREATE UNIQUE NONCLUSTERED INDEX [IX_TablePrimary_Id] 
ON [dbo].[TablePrimary] ([PrimaryId] ASC)
WHERE [PrimaryId] IS NOT NULL

The WHERE clause allows multiple rows to have NULL in this column, but every non-NULL value in this column must be unique.

Should the indexed column in TablePrimary be suitable as the primary key for a foreign key relationship to TableForeign? Every non-NULL value must be unique and NULL in the foreign table wouldn't create a foreign key relationship anyway. But I am getting an error trying to place the foreign key relationship on TableForeign.

Unable to create relationship 'FK_TableForeign_TablePrimary'.
There are no primary or candidate keys in the referenced table 'dbo.TablePrimary' that match the referencing column list in the foreign key 'FK_TableForeign_TablePrimary'.

Before I dig deeper into this, I'd like to verify that what I am trying to do is indeed possible. Basically, not every row in TablePrimary will have children in TableForeign. But those rows which do exist in TableForeign must have a matching PrimaryId in TablePrimary (there are other ways to get the job done but in this case, I'd need a third table as the cross-ref between TablePrimary and TableForeign and I'd like to avoid that if it's not necessary, although, if it is necessary, then it is necessary).

Dale K
  • 25,246
  • 15
  • 42
  • 71
Yossi G.
  • 939
  • 1
  • 8
  • 22
  • Not much of a "Primary ID" if it allows NULL. That is sort of like allowing NULL in a required field. – Sean Lange Feb 24 '20 at 20:24
  • 1
    As to the question at hand no this isn't possible as you describe it. If you have multiple rows with NULL in the column how would the other table know which row the foreign key is tied to? – Sean Lange Feb 24 '20 at 20:27
  • Sean, not sure what is the objection? NULL in the foreign key table is NEVER related to a corresponding primary table so why should an index which allows multiple NULLS be excluded as a candidate for such a primary key? As long as all non-NULL values are UNIQUE. Granted - the field is NOT required in the primary table, but if it is present it may have one or more children in the foreign table. (While the physical implementation may not allow this, I don't see why, from the logical perspective, this would not be a valid relational construct.) – Yossi G. Feb 24 '20 at 20:32
  • Because the point of a primary key is to identify the row in the table. If it is NULL you can't identify the row. – Sean Lange Feb 24 '20 at 20:33
  • What does "suitable as the primary key for a foreign key relationship to" mean? An SQL PK is UNIQUE NOT NULL; an SQL FK references a UNIQUE (NULL or not). Also a NULL in a FK is satisfied regardless of the values in the referenced table. Also SQL Server is non-standard in allowing only one NULL in a column. Explain exactly what restrictions on data & behaviour you want without using "PK" & "FK" when you're not sure exactly what they mean. PS Please clarify via edits, not comments. – philipxy Feb 24 '20 at 20:34
  • @philipxy what do you mean by "Also SQL Server is non-standard in allowing only one NULL in a column."? I'm not following that sentence. – Sean Lange Feb 24 '20 at 20:38
  • It's not the primary key for TablePrimary. That table has its own valid primary key column. This is a unique index in TablePrimary which I'd like to use as the primary key to a foreign key relationship. (Unfortunately, the term 'primary key' is overloaded in this manner.) Aside from the primary key of the table, which is necessarily unique, any unique index can function as the primary key to a foreign key column in a different table. This index is unique in terms of its non-null values, but it allows multiple nulls because it is optional within the model. Where it exists it will have children. – Yossi G. Feb 24 '20 at 20:38
  • 1
    Ahhh....you see a primary key can't be overloaded in practice or reality. It serves one and only one purpose which is to uniquely identify a row in a table. Your secondary column is not valid to use as a foreign key because it allows NULL. That is part of the requirement of a foreign key, so that the secondary table knows which row in the primary table is being referenced. If you had NULL in the main and the secondary table everything is ambiguous. Is the NULL because it references a row with NULL? If so which row? Or is it NULL because it has no reference. It just doesn't work that way. – Sean Lange Feb 24 '20 at 20:41
  • 1
    "Also SQL Server is non-standard in allowing only one NULL in a column." Sorry I meant in a UNIQUE. PS "Please clarify via edits, not comments." – philipxy Feb 24 '20 at 20:43
  • @philipxy gotcha. Thought might be what you meant but wasn't sure. :P – Sean Lange Feb 24 '20 at 20:44
  • @SeanLange What is "overloaded" & "secondary column" & "not valid" etc etc? (Rhetorical.) Please use enough words to be clear & use technical terms correctly & only when you know what they mean. – philipxy Feb 24 '20 at 20:45
  • @SeanLange "Your secondary column is not valid to use as a foreign key because it allows NULL." Ah - got it. OK, it is what it is. I'll have to create a second table, TableXref with FK to TablePrimary which will only have rows for those rows in TablePrimary which today have a non-NULL value in that column. That will allow me to create an FK between TableForeign and TableXref. OK, That's the state-of-the-art. Thanks! – Yossi G. Feb 24 '20 at 20:46
  • @philipxy huh? I was using terms from the OP. And trust me I fully understand how primary and foreign keys work. – Sean Lange Feb 24 '20 at 20:46
  • Again: An SQL FK does not need to reference a NOT NULL. Please edit your question to be clear if you want to get a sound informed answer. – philipxy Feb 24 '20 at 20:47
  • @SeanLange Using unexplained terms of the OP means both of you are unclear. – philipxy Feb 24 '20 at 20:48
  • @SeanLange Sean, if you want to supply your comment as an answer, I'll vote it as the answer. "Your secondary column is not valid to use as a foreign key because it allows NULL." (Love it - asked and answered. Clear enough for me!) Thanks! – Yossi G. Feb 24 '20 at 20:48
  • The referenced column list in a FK declaration must match a declared PK or UNIQUE list with the same number & types of columns plus typically other restrictions. Read the manual, give a [mre] for code questions--cut & paste & runnable code including DDL & table initialization. And your error will be a duplicate question, see [ask], [help] & the voting arrow mouseover texts. – philipxy Feb 24 '20 at 20:52
  • Clarification, from the original post - Should the indexed column in TablePrimary be suitable as the primary key for a foreign key relationship to... With emphasis on *indexed column*. – Yossi G. Feb 24 '20 at 20:52
  • I don't understand your "clarification". A FK does not have to reference a PK. It can reference a PK, UNIQUE or UNIQUE INDEX. You seem to mean "suitable as the referenced column list for a FK". A FK list must reference list declared UNIQUE in a CREATE TABLE. But those don't allow WHERE. The problem is not "because it allows NULL". – philipxy Feb 25 '20 at 01:56

1 Answers1

1

Can a UNIQUE 'WHERE' index which allows multiple NULLS be used as the primary key for a foreign key relationship?

No. Unique filtered indexes cannot be be referenced by foreign keys. Unique indexes (even with included columns, no filters though) can be referenced by foreign keys.

edit: for Nullability. Foreign keys can reference unique indexes/constraints with NULLable columns. Nullability is not a prerequisite for creating foreign keys. However, foreign keys are NOT checked for rows with at least one null value in any of the fk columns:

create table dbo.parent
(
id1 int null,
id2 int null,
constraint id1id2 unique(id1, id2)
);

insert into dbo.parent(id1, id2)
values(1, 1), (2, 2), (3, null);

go

create table dbo.child
(
childid int identity,
parentid1 int,
parentid2 int,
constraint fkparent foreign key(parentid1, parentid2) references parent(id1, id2)
)
go


insert into dbo.child(parentid1, parentid2)
values (1, 1), (2, 2) --ok
go

insert into dbo.child(parentid1, parentid2)
values (4, 4) -- fk violation, there is no 4,4 parent row
go

insert into dbo.child(parentid1, parentid2)
values (3, null) --do not get tricked here.... because the fk has a null value, the fk is NOT checked at all
go

--fk with one null value, fk is not checked at all 
insert into dbo.child(parentid1, parentid2)
values (100, null) -- but there is no 100, null parent row
go

select *
from parent;
select *
from child;
lptr
  • 1
  • 2
  • 6
  • 16