SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[PageNode] (
    [Id]               INT     NOT NULL,
    [ParentNodeId]     INT     NULL,
    [SiteSectionId]  TINYINT NOT NULL,
    CONSTRAINT [PK_PageNode] PRIMARY KEY CLUSTERED ([Id] ASC),
    constraint [UK1] UNIQUE ([Id], [SiteSectionId]),
    CONSTRAINT [FK_PageNode_PageNode] FOREIGN KEY ([ParentNodeId], [SiteSectionId]) REFERENCES [dbo].[PageNode] ([Id], [SiteSectionId]),
    CONSTRAINT [CK_PageNode_Parent] CHECK ([ParentNodeId]<>[Id])
);
insert [dbo].[PageNode] ([Id],[ParentNodeId],[SiteSectionId])
values 
  (10010000, null, 1),
  (10010002, 10010000, 1),
  (10010001, null, 1),
  (10010003, 10010001, 1),
  (10010004, null, 2),
  (10010005, 10010004, 2)
  
6 rows affected

-- fails
insert [dbo].[PageNode] ([Id],[ParentNodeId],[SiteSectionId])
values 
  (10010006, 10010001, 2)
Msg 547 Level 16 State 0 Line 2
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_PageNode_PageNode". The conflict occurred in database "fiddle_9aea4c5fbdc4450c88c8d71f31d48cf4", table "dbo.PageNode".
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.