SQLServer two foreign keys pointing to same column

Question

I have this concept code (with some comments):

IF EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'test')
SET NOEXEC ON
GO

-- didn't use EXEC sp_executesql for Syntax Highlighting and Code Compeltion in MSSMS
CREATE SCHEMA [test]
GO

SET NOEXEC OFF
GO

BEGIN TRANSACTION

-- child is a list
CREATE TABLE [test].[Child] (
    [ChildId] BIGINT NOT NULL,
    CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED ([ChildId]) WITH (IGNORE_DUP_KEY = ON)
)

-- parent is a tree
CREATE TABLE [test].[Parent] (
    [ParentId] BIGINT NOT NULL,
    [ParentPid] BIGINT DEFAULT NULL,
    FOREIGN KEY([ParentPid]) REFERENCES [test].[Parent] ([ParentId]),
    CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED ([ParentId]) WITH (IGNORE_DUP_KEY = ON)
)

-- each child can have any number of parents
CREATE TABLE [test].[ChildParent] (
    [ChildId] BIGINT NOT NULL,
    FOREIGN KEY([ChildId]) REFERENCES [test].[Child] ([ChildId]) ON DELETE CASCADE,
    [ParentId] BIGINT NOT NULL,
    FOREIGN KEY([ParentId]) REFERENCES [test].[Parent] ([ParentId]) ON DELETE CASCADE,
    CONSTRAINT [PK_ChildParent] PRIMARY KEY CLUSTERED ([ChildId], [ParentId]) WITH (IGNORE_DUP_KEY = ON)
)

-- () This table is internal and used readonly, generated by a trigger on ChildParent
-- that creates associations between each Child & Parent + all the Parent's Parents
-- this way flatteting the Parent structure and making deep querying easier.
-- () ChildId + ParentPid point directly to the original Child + Parent relationship
-- that when it's deleted, it automatically deletes all the Child + Parent's Parents.
-- () I also need the table to automagically delete any row when any Child/Parent's gone.
CREATE TABLE [test].[Parent_Child] (
    [ParentId] BIGINT NOT NULL,
    FOREIGN KEY([ParentId]) REFERENCES [test].[Parent] ([ParentId]) ON DELETE CASCADE,
    [ChildId] BIGINT NOT NULL,
    FOREIGN KEY([ChildId]) REFERENCES [test].[Child] ([ChildId]) ON DELETE CASCADE,
    [ParentPid] BIGINT NOT NULL,
    FOREIGN KEY([ParentPid]) REFERENCES [test].[Parent] ([ParentId]) ON DELETE CASCADE,
    FOREIGN KEY([ChildId], [ParentPid]) REFERENCES [test].[ChildParent] ([ChildId], [ParentId]) ON DELETE CASCADE,
    CONSTRAINT [PK_Parent_Child] PRIMARY KEY CLUSTERED ([ParentId], [ChildId], [ParentPid]) WITH (IGNORE_DUP_KEY = ON)
)

COMMIT TRANSACTION

The last table won't create and I know why, even if I don't agree with it at all as I can't see how this exact scenario can break anything or make anything difficult for SQLServer. MySQL does (or at least did 1yr ago when I last used it) this without a problem.

I also know that any change I make (drop DELETE CASCADE, drop FOREIGN KEYS all together) in the above (illegal SQL) will force me to do cleanup on client side (C#) and break my neatly designed database.

Considering I'd like to keep all Parent_Child maintenance SQLServer-side so the C# client just reads it, is there any workaround for this situation?

PS: Thought of using a INSTEAD OF DELETE trigger but doc says you can't use it on tables with FOREIGN KEY DELETE CASCADE.

ERROR: may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints..

One workaround is to only make change through a stored procedure and embed required logic in there. — Nick.Mc, Aug 11 '15 at 10:34

score 0 · Answer 1 · answered Aug 11 '15 at 11:53

I managed to create a workaround to the situation while not doing any work client-side:

Created a new table where I flatten the entire Parent hierarchy with a Trigger on Parent.
Use this new table for queries being only a join away from my original design but not being recursive.

Table structure:

CREATE TABLE [test].[ParentTree]
(
    [ParentId] BIGINT NOT NULL, -- no fkey, but I double the value on column 2 so no need
    [ParentPid] BIGINT NOT NULL,
    FOREIGN KEY([ParentPid]) REFERENCES [dbo].[Parent] ([ParentId]) ON DELETE CASCADE,
    CONSTRAINT [PK_ParentTree] PRIMARY KEY CLUSTERED ([ParentId], [ParentPid]) WITH (IGNORE_DUP_KEY = ON)
)
GO

So the flat hierarchy is achieved with a trigger on Parent, a recursive CTE function and an OUTER APPLY to use the function against the Parents. If I have a 'z' child of 'y' child of 'x', then my flat table contains:

[Child] [Parent]
x x -- notice child is parent of child
y y -- notice child is parent of child
y x
z z -- notice child is parent of child
z y
z x

This allows me to do the deep queries on a flat data set. And I can also have DELETE CASCADE on column 2 as I double the child value so I can avoid no double fkeys.

To query all [Child] in [Parent] X (including those in child Y and Z of X) I just do:

DECLARE @ParentId BIGINT = 0
SELECT DISTINCT [ChildId]
FROM [test].[ChildParent] [t1]
    JOIN [test].[ParentTree] [t2]
        ON ([t2].[ParentId] = [t1].[ParentId] AND [t2].[ParentPid] = @ParentId)
GO

I might even reduce the complexity further but this is my workaround for the fkey cascade mess.

PS: Duplicating a fkey (so I can have just one) might have also worked on the original design but I'm OK with this solution so I won't try to change the original code to work like this.

SQLServer two foreign keys pointing to same column

1 Answers1