T-SQL: foreign key that's not referencing a primary key

I have the following database:

CREATE TABLE ContentNodes
(
   Id UNIQUEIDENTIFIER NOT NULL, 
   Revision INT IDENTITY(1,1) NOT NULL,
   ParentId UNIQUEIDENTIFIER NULL
   PRIMARY KEY (Id, Revision)
)

How do I limit ParentId to only contain values from the Id column. Trying to make Par开发者_如何学编程entId a foreign key gives me:

PRINT 'FK_ContentNodes_ParentId_ContentNodes';
ALTER TABLE ContentNodes
ADD CONSTRAINT FK_ContentNodes_ParentId_ContentNodes FOREIGN KEY (ParentId) REFERENCES ContentNodes(Id);
GO

Error:

There are no primary or candidate keys in the referenced table 'ContentNodes' that match the referencing column list in the foreign key 'FK_ContentNodes_ParentId_ContentNodes'.

---

Since you have a compound primary key (Id, Revision) on your ContentNodes, you have to use both columns in a foreign key relation.

You cannot reference only parts of a primary key - simply cannot be done.

You have to either introduce a surrogate primary key into your table which is just a simple INT IDENTITY and then you can self-reference that single PK column, or you can (if it's possible in your data model) put a UNIQUE INDEX on that one column you want to reference:

CREATE UNIQUE NONCLUSTERED INDEX UIX_ID 
  ON ContentNodes(Id)

Once you have a UNIQUE INDEX on that column, then you can use it as a FK reference.