How add an optional many to many relationship to another many to many relation table?

Question

I've got the following tables defined:

- Group
  - GroupID (pk - int)
  - ...

- Item
  - ItemID (pk - int)
  - ...

- Groups_Items
  - GroupID (pk - int)
  - ItemID (pk - int)

There is a FK on the GroupID linked to the Group table and there is one on the ItemID which is linked to the Item table.

I need to add an optional relationship to the Groups_Items table i.e. RuleSetId

- Groups_Items
  - GroupId (pk - int)
  - ItemId (pk - int)
  - RuleSetId (int - nullable)

With the Ruleset table defined as follows:

- RuleSet
  - RuleSetId (int)
  - RuleId (int)

and the Rule table is defined as follows:

- Rule
  - RuleId (int)
  - ...

When I try to add an FK using the RuleSetId from the RuleSet table to the RuleSetId from the Groups_Items table, it's giving me an error telling me I'm not allowed.

Each Item must be associated to a group, and a group can have multiple items but I want to provide the option to allow one or more rules to be associated to an item via a RuleSet. Note that I don't want to associate the ruleset directly to the item itself but to the item associated to the group as the item could be the same but associated to different groups and a different set of rules may apply depending on the group the item belongs to.

I hope this makes sense.

I'm struggling to get this to work so I must be designing this incorrectly but I'm not sure how best to approach this.

Update - 1

Here's the diagram of what I'm trying to achieve. Note that I've added the RuleSet table in the meantime which just contains a primary key i.e. RuleSetId

I'm getting the following error when I try to add a relationship between the GroupsItems table and the RuleSet table:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 
"FK_GroupsItems.ERuleSet". The conflict occurred in database 
"MyDatabase", table "dbo.RuleSet", column 'RuleSetId'.

and I'm getting the following error when I try to add a relationship between the RuleSet table and the RuleSetRules table:

Unable to create relationship 'FK_RuleSetsRules.RuleSet'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 
"FK_RuleSetsRules.RuleSet". The conflict occurred in database "MyDatabase", 
table "dbo.RuleSet", column 'RuleSetId.

Answer 1

Update: Note that in this answer, I have changed the meaning of your 'ruleset' table. In this answer, the Ruleset table creates each ruleset (but not the rules). RulesetID is an int and PK. Then rules are linked to this ruleset in Ruleset_Rules.

---

I believe the Ruleset table also needs to have a primary key that is referenced by the foreign key. I suggest Ruleset and Rules also get a many-to-many table linking them (if you want to re-use rules) or have RulesetID as a field in rules. This example has the many-to-many:

CREATE TABLE Grp (GroupID int PRIMARY KEY)
CREATE TABLE Item (ItemID int PRIMARY KEY)

CREATE TABLE RuleSet(RulesetID int PRIMARY KEY)
CREATE TABLE Rules(RuleID int PRIMARY KEY)
GO

CREATE TABLE RuleSet_Rules (RulesetID int, RuleID int, PRIMARY KEY (RulesetID, RuleID))
CREATE TABLE Grp_Item (GroupID int, ItemID int, RuleSetID int, PRIMARY KEY (GroupId, ItemId))
GO

ALTER TABLE [dbo].[Grp_Item] ADD CONSTRAINT [FK_Grp_Item_Grp] FOREIGN KEY([GroupID]) REFERENCES [dbo].[Grp] ([GroupID])
GO
ALTER TABLE [dbo].[Grp_Item] ADD CONSTRAINT [FK_Grp_Item_Item] FOREIGN KEY([ItemID]) REFERENCES [dbo].[Item] ([ItemID])
GO
ALTER TABLE [dbo].[Grp_Item] ADD CONSTRAINT [FK_Grp_Item_Ruleset] FOREIGN KEY([RulesetID]) REFERENCES [dbo].[RuleSet] ([RulesetID])
GO

Here's a db<>fiddle with setup and example inserts.

UPDATE: Here's an updated DB<>fiddle from comments below. This has more FKs as well as explaining that some inserts deliberately fail.

Note this assumes that each combination of group/item can have either 0 or 1 ruleset assigned to it. If you need to have more than one ruleset, it becomes a little more complex in the many-to-many table for Grp_Item.

Also note I haven't created all FKs - just the ones relevant to the current problem in Grp_Item.