3

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?

MDCCL
  • 8,530
  • 3
  • 32
  • 63
John
  • 747
  • 7
  • 15

2 Answers2

6

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Mike Walsh
  • 18,278
  • 6
  • 50
  • 74
6

There seems to be some confusion. The CHECK (Id > 1000) is not a PRIMARY KEY constraint, it is a CHECK constraint.

You can have a partial (filtered*) unique index, if that's what you are after: UNIQUE (id) WHERE (id > 1000) (but this is not a primary key constraint or an index that can be used for the primary key constraint). Create it with:

CREATE UNIQUE INDEX Id_more_than_1000_PUQ
  ON MyTable (id) WHERE (id > 1000) ;

which means that all id values that are bigger than 1000 will appear only once on that column. All other values (1000 and lower) will be allowed as duplicates.

And similarly for the date column:

CREATE UNIQUE INDEX Id_when_created_on_2010_and_after_PUQ
  ON MyTable (id) WHERE (CreatedOn >= '20100101') ;

which means that for rows that have CratedOn on 2010 and afterwards, the id values will be unique. All other rows (that have CreatedOn at 2009 and earlier or NULL) will be ignored for that unique index. So you may have duplicate id values as long as all but one do not match the WHERE condition of the index.

These indexes will be non-clustered of course - all partial indexes are.
And they cannot be used as targets for FOREIGN KEY constraints.

*: See the CREATE INDEX and Fileterd indexes pages on MSDN or details of use and advantages.

About they "why can't we reference a partial index?":

This is what the SQL standard says and how it's implemented in SQL Server. An FK constraint has to reference a column (or columns) of a table. Not an index. A partial index by definition holds a part of the values that appear in the column, not all of them.

A related question I asked some time ago: Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?.

There is no inherent problem in the relational model that would disallow such FKs to be defined. But the SQL (committee and the various DBMS) have not added this as a feature.

Community
  • 1
ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306