Self referencing table with foreign key issue

Question

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

The current foreign key does not provide the required level of integrity. Although the parent must exist in the table, it can be the wrong SiteSection. In other words, it works - to a point - but not perfectly. — John Ohara, Apr 04 '22 at 11:34
That's all a foreign key does, John, checks to see if the value already exists in the table, and if not generates an error. Though you haven't explained your question still. You talkm about if can be the wrong `SiteSection`; how do you know it's the wrong section and (more importantly) how would SQL Server? — Thom A, Apr 04 '22 at 11:39
It seems your "parent" reference should actually be a composite key - which also might mean your primary key is similarly flawed. There seems to be a relationship between "parent" and "sitesection" - likewise you also seem to have a "root" parent that is also not well defined (but that's a guess). Much depends on a more detailed understanding of your actual model — SMor, Apr 04 '22 at 11:41
@Larnu: suppose I have a utility page with ID 1, and a blog post with ID 100. Both would have a parent ID (based on the current code), but there is no guarantee it will be from the same section. Therein lies the problem. — John Ohara, Apr 04 '22 at 11:44
So the combination of the columns should likely be a composite key, @JohnOhara . Seems both the answers below have the idea, which utilise a unique index/constraint alongside the foreign key. — Thom A, Apr 04 '22 at 11:46
I disagree that it should be a composite key, including SiteSectionId. That would mean every other table that uses PageNodeId adopting it when it isn't necessary. Also, neither of the answers below work. — John Ohara, Apr 04 '22 at 12:02
A FK references a declared UNIQUE. PK is short for UNIQUE NOT NULL. You seem to maybe want a child to be in the same section as its parent. So a FK to reference (node, section). (But your description of how your table represents a situation is not clear.) — philipxy, Apr 04 '22 at 12:38

Serg · Answer 1 · 2022-04-04T12:13:54.583

1

"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

edited Apr 04 '22 at 12:13

answered Apr 04 '22 at 11:42

Serg

22,285
5
21
48

1

Please show insert which fails. Added the fiddle – Serg Apr 04 '22 at 12:13
Ultimately every section will work upwards towards the home page, '10010000'. The problem therefore is that you cannot link to '10010000' from a section with an ID of '2' because it doesn't match. You can have both of these: (10010006, 10010000, 1) and (10010007, 10010000, 2) because the home page can only ever have one SiteSectionId. – John Ohara Apr 04 '22 at 12:20
Isn't it what was required? Your `[SiteSectionId] TINYINT` is declared NOT NULL. Thus _every_ page belongs to the exactly one section. – Serg Apr 04 '22 at 12:24
Yes Serg. It works perfectly - until the parent is the home page, then it fails. Every page on site will ultimately cascade up to the home page, so if we have a chain of blog posts, SiteSectionId, '2' and a chain of utility pages siteSectionId '1', they will both try to connect to the home page with different SiteSectionId - and that isn't allowed as the code is. – John Ohara Apr 04 '22 at 12:28
I suggest you return back to your design and just make [SiteSectionId] nullable. 0 - top of the site, 1- top of section 1, 2- top of the section2. All the rest pages rows [SiteSectionId] = null. And the real section is computed on the fly by traversing the chain up to the top. – Serg Apr 04 '22 at 12:55
Tested and doesn't work either. – John Ohara Apr 04 '22 at 15:07

score 0 · Answer 2 · answered Apr 04 '22 at 11:42

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

Both answers are the same, but they don't execute. I suspect it's because the Home page '10010000' has a SiteSectionId of '1' which does match both sections. — John Ohara, Apr 04 '22 at 11:58

Self referencing table with foreign key issue

2 Answers2