There seems to be some confusion. The
CHECK (Id > 1000) is not a
PRIMARY KEY constraint, it is a
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.