How to cascade on a column?

I have a table that links to another — called PageTopic and TopicPageRole respectively.

The foreign key on PageTopic is:

CONSTRAINT [FK_PageTopic_TopicPageRole] FOREIGN KEY ([PageRoleId]) REFERENCES [dbo].[TopicPageRole] ([PageRoleId])

Both tables have a column named 'IsMultiMember'; I want to cascade update if IsMultiMember changes for the PageRoleId but I've realised that it may lead to unintended consequences — cascading updates if PageRoleId changes.

Have I got it wrong? If I'm right in my thinking, how do I only update the required column when data changes?

Here some code to better explain why I'm trying to do what I am. Some code removed for brevity.

    CREATE TABLE [dbo].[TopicPageRole] (
    [PageRoleId]    TINYINT NOT NULL,
    [MultiMember]   BIT     NOT NULL
    CONSTRAINT [PK_TopicPageRole] PRIMARY KEY CLUSTERED ([PageRoleId] ASC),
    CONSTRAINT [UQ_TopicPageRole_PageRoleId_MultiMember] UNIQUE NONCLUSTERED ([PageRoleId] ASC, [MultiMember] ASC)
);

CREATE TABLE [dbo].[PageTopic] (
    [PageId]      INT      NOT NULL,
    [TopicId]         SMALLINT NOT NULL,
    [PageRoleId]  TINYINT  NOT NULL,
    [MultiMember]   BIT      NOT NULL
    CONSTRAINT [PK_PageTopic] PRIMARY KEY CLUSTERED ([PageId] ASC),
    CONSTRAINT [FK_PageTopic_TopicPageRole] FOREIGN KEY ([PageRoleId]) REFERENCES [dbo].[TopicPageRole] ([PageRoleId]),
    CONSTRAINT [FK_PageTopic_TopicPageRole2] FOREIGN KEY ([PageRoleId], [MultiMember]) REFERENCES [dbo].[TopicPageRole] ([PageRoleId], [MultiMember]),

);

// Ensure only one entry per role type where MultiMember is false 

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_PageTopic_PageRoleId_Single]
    ON [dbo].[PageTopic]([TopicId] ASC, [PageRoleId] ASC) WHERE ([MultiMember]='0');

IsMultiMember is a property of table PageRole; in essence it declares whether a topic is allowed to have multiple pages for a particular role. So a hub page can only exist once per topic, while a post can exist multiple times. The TopicPage table contains pages along with their assigned topic. Currently, if I don't have a PageRole/IsMultiMember column also in that table I can't limit the number of pages. I'd be happy to listen to alternative ways.

Solution

I can't see that there would be an issue with using an ON UPDATE CASCADE on this foreign key. Since PageRoleId is the primary key for TopicPageRole, it's not actually likely to change ever (it certainly shouldn't be a common occurence). The only cascading would be on MultiMember.

However, you should ensure your foreign key is properly indexed if you want the cascading to work efficiently

CREATE NONCLUSTERED INDEX [IX_PageTopic_PageRoleId_MultiMember]
    ON [dbo].[PageTopic]([TopicId] ASC, [MultiMember] ASC);

Having said that, you don't need to use the extra column to enforce this constraint. There is another way to do multi-table constraints, using a an indexed view instead of a filtered index.

First, just remove the column and second foreign key from PageTopic

CREATE TABLE [dbo].[PageTopic] (
    [PageId]      INT      NOT NULL,
    [TopicId]         SMALLINT NOT NULL,
    [PageRoleId]  TINYINT  NOT NULL
    CONSTRAINT [PK_PageTopic] PRIMARY KEY CLUSTERED ([PageId] ASC),
    CONSTRAINT [FK_PageTopic_TopicPageRole] FOREIGN KEY ([PageRoleId]) REFERENCES [dbo].[TopicPageRole] ([PageRoleId]),
    -- make sure you  index the foreign key
    INDEX IX NONCLUSTERED (PageRoleId ASC)
);

Now we can create a joined indexed view, and put a unique constraint on it.

CREATE OR ALTER VIEW View_PageTopic_PageRoleId_Single
WITH SCHHEMABINDING
AS
SELECT
  pt.TopicId,
  pt.PageRoleId
FROM dbo.PageTopic pt
JOIN dbo.TopicPageRole pr ON pr.PageRoleId = pt.PageRoleId
WHERE pr.MultiMember = 0;

CREATE UNIQUE CLUSTERED INDEX [IX_PageTopic_PageRoleId_Single]
    ON dbo.View_PageTopic_PageRoleId_Single([TopicId] ASC, [PageRoleId] ASC);