Can a PRIMARY KEY constraint (with a NONCLUSTERED INDEX) check for only certain values?

Can I set a (logical) PRIMARY KEY constraint, served by a (physical) NONCLUSTERED INDEX, so that it only checks certain values of the constrained and indexed column or another column in the relevant table?

And, if so, can that column be established as a FOREIGN KEY constraint referenced from other tables?

I'm trying to basically see if I can remove certain rows from a parent table without removing those rows from a child table but still enforce the relationship for, e.g:

• IDs > 1000, or

• CreatedDateTime > RemovalDate.

For example, you can have that kind of constraint:

ALTER TABLE MyTable WITH NOCHECK ADD 
    CONSTRAINT PK_MyTable CHECK (Id > 1000)

but how would you set this column as a PRIMARY KEY fixed with a NONCLUSTERED INDEX?

And can I do something like the following definition:

ALTER TABLE MyTable WITH NOCHECK ADD 
    CONSTRAINT PK_MyTable CHECK (CreatedOn > '01/01/2010')

but have an INDEX for the column named Id?

You cannot have a Primary Key that only works for some of the rows. It would really cease being a Primary key at that point. Emphasis on Primary.

You can play with filtered indexes for a Unique index and that filter can be on values you specify.

But a primary key is there to enforce uniqueness so you can't make only some of the table part of the key.

You can disable the FKs while you make fixes, you can build a foreign key relationship on an existing table with NOCHECK - but that will mean that none of the relationships are checked. You have an untrusted foreign key and some of the benefits foreign keys offer like foreign key join elimination can't work that way because SQL isn't guaranteed that your row is there or not there.

Alternatively you could just not define that relationship and deal with the ramifications. But you are basically asking SQL server to break the referential integrity at the outset by making exceptions. If you could cart those exceptions off into a separate table and join to that table in a view or something like that you could get around this and keep the relationship on the "real" table moving forward.

Remember though: A filtered index just can give you some uniqueness on the other side of filter. This doesn't address FKs or even the PK. I think a better answer is fix the issue with the data. Even a -1 primary key value for "unknown" as is common in warehouses would be better than orphans.

StackExchange Database Administrators Q#157515, answer score: 6