SQL Server - Constraining column in a table to the primary key OR null in same table

I'm trying to work out how in SQL Server to create a table that has a column that can either be null, or have a value. If it has a value, it needs to be constrained to an existing primary key in the table, except for the row the the value is stored in.

Do I need a check constraint? If so, what's the expression?

The reaso开发者_Python百科n for this is objects in the table can be children of other tables. Ideally I'd rather do this without a mapping table.

Primary key name is 'id'.

The column I am trying to constrain is 'parentId'. parentId CAN be null OR refer to a valid id for another row in the table.

What you need to do here is to create a foreign key relationship with the same table, for Example:

CREATE TABLE [dbo].[Category](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](150) NOT NULL,
    [ParentCategoryID] [int] NULL,
 CONSTRAINT [PK_tblCategory] PRIMARY KEY CLUSTERED 
([ID] ASC))
GO

ALTER TABLE [dbo].[Category] WITH CHECK ADD CONSTRAINT [FK_tblCategory_tblCategory] 
FOREIGN KEY([ParentCategoryID]) REFERENCES [dbo].[Category] ([ID])
GO

ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_tblCategory_tblCategory]
GO