Table constraint design to avoid a cycle

Transact-SQL
1

In the minimal reproducible example below, table C is invalid as it results in a cycle. I understand I can simplify C by removing the constraints, however the number of triggers required after goes up significantly if I want to preserve the cascading behavior from anywhere in the hierarchy (the actual use case more tables).

Is that the only option here, or is there a better way to achieve the automated removal of the entry in C when any dependency above it is removed? I would prefer not to split table B.

CREATE TABLE A
(
    Id UNIQUEIDENTIFIER NOT NULL DEFAULT (newsequentialid()) PRIMARY KEY,
    Name NVARCHAR(256)    NOT NULL
);

CREATE TABLE B
(
    Id UNIQUEIDENTIFIER NOT NULL DEFAULT (newsequentialid()) PRIMARY KEY,
    AId UNIQUEIDENTIFIER NOT NULL,
    Name NVARCHAR(256)    NOT NULL,
    CONSTRAINT FK_B_A FOREIGN KEY (AId) REFERENCES A (Id) ON DELETE CASCADE
);

CREATE TABLE C
(
    Id UNIQUEIDENTIFIER NOT NULL DEFAULT (newsequentialid()) PRIMARY KEY,
    PrimaryId UNIQUEIDENTIFIER NOT NULL,
    SecondaryId UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT FK_C_PrimaryId FOREIGN KEY (PrimaryId) REFERENCES B (Id),
    CONSTRAINT FK_C_SecondaryId FOREIGN KEY (SecondaryId) REFERENCES B (Id)
);
2

In what way does this design cause a cycle? It could prevent DELETE from table A since the FK’s on table C do not have CASCADE actions defined, but that’s not any definition of a “cycle” that I’m aware of.

I would suggest that Table C doesn’t require an extra column for a primary key, you could instead make PrimaryID,SecondaryID the primary key. Without a unique constraint on both of those columns you may have unwanted duplicates that complicate any upstream cascade action.