Sql-server – Can a PRIMARY KEY constraint (with a NONCLUSTERED INDEX) check for only certain values

check-constraintsdatabase-designnonclustered-indexsql serversql-server-2008

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?

Best Answer

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.

Related Question