Self referencing table with foreign key issue-CodePudding

I have a small table, below is a screenshot:

There is a parent node which references back to the ID. Every row had a parent except the home page (for obvious reasons).

Here's the code with some bits removed for brevity:

CREATE TABLE [dbo].[PageNode] (
    [Id]               INT     NOT NULL,
    [ParentNodeId]     INT     NULL,
    [SiteSectionId]  TINYINT NOT NULL,
    CONSTRAINT [PK_PageNode] PRIMARY KEY CLUSTERED ([Id] ASC)
    CONSTRAINT [FK_PageNode_PageNode] FOREIGN KEY ([ParentNodeId]) REFERENCES [dbo].[PageNode] ([Id])
    CONSTRAINT [CK_PageNode_Parent] CHECK ([ParentNodeId]<>[Id])
);

As you can see, there's an additional column named SiteSectionId. It's used to split the site into its main sections, primarily 'Main' and 'Blog'.

This means that the referential integrity is compromised because a blog post could be set up with a parent in the main section and vice versa.

So, I'm trying to address the integrity issue, but where I come unstuck is that the home page is relevant to all SiteSection types.

Here's a fiddle - the data needs to contain at least one entry with ParentNodeId '10010000' (the home page) and a SiteSectionId of '1' and another with ParentNodeId of '10010000' and SiteSectionId of '2', but it should fail if there is a mismatch between the SiteSectionId's for Id and it's parent ParentNodeId in all other circumstances other than the parent being the home page.

db-fiddle

CodePudding user response：

If you want to enforce that a child node always belong to the same section of the parent node you can add the section to the key and foreign key. For example:

CREATE TABLE [dbo].[PageNode] (
  [Id]               INT     NOT NULL,
  [ParentNodeId]     INT     NULL,
  [SiteSectionId]    TINYINT NOT NULL,
  CONSTRAINT [PK_PageNode] PRIMARY KEY CLUSTERED ([Id] ASC)
  constraint uq_section_node unique (Id, SiteSectionId),
  CONSTRAINT [FK_PageNode_PageNode] FOREIGN KEY (ParentNodeId, SiteSectionId)
    REFERENCES [dbo].[PageNode] ([Id], SiteSectionId)
  CONSTRAINT [CK_PageNode_Parent] CHECK ([ParentNodeId]<>[Id])
);

CodePudding user response：

"The same [SiteSectionId]" rule can be implemented by declaring a UNIQUE constraint which includes a PK and [SiteSectionId] and referencing this UK in the FK in question

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])
);

db<>fiddle