Possible To Have A Fixed Value As Foreign Key?

Hi everyone,

I'm trying to make a hierarchy in which some nodes are locked, preventing children nodes from being added.

The hierarchy table points to itself (the parent entry). I want to secure this from the DB side. Initially, no nodes have parents but can attach themselves to one.

I'd like my FK contraint to be like:
CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, 1) REFERENCES nde_recursion(hierarchy_id, allow_children)

This is so the parent_id can only be set if that entry allows children.

Here's my approach. Any better ideas? I feel like I'm adding an extra field (parent_allow) when I shouldn't.


CREATE TABLE hierarchy(
hierarchy_idINTIDENTITY(1,1) UNIQUE NOT NULL,
allow_childrenBITDEFAULT 1 NOT NULL,

parent_idINT,-- NULL is for root nodes
parent_allowBITDEFAULT 1 NOT NULL,

CONSTRAINT PK_hierarchy PRIMARY KEY (hierarchy_id, allow_children),
CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, parent_allow) REFERENCES nde_recursion(hierarchy_id, allow_children),
CONSTRAINT CK_hierarchy CHECK ((nde_status = 'Normal' AND parent_id IS NULL) OR (nde_status != 'Normal' AND parent_id IS NOT NULL)),
CONSTRAINT CK_hierarchy2 CHECK (parent_allow = 1)
)