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 reason 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.