SQL Server foreign key data issue

Question

I have 3 tables

• crm.Documents
• crm.GroupDocuments
• schedular.Groups

Documents table looks like

Id   Name
-----------
1    Test

GroupDocuments table looks like

Id | GroupId | DocumentId
---+---------+------------
1  |  1      | 1
2  |  2      | 1

Group table looks like

Id | Name
---+--------
1  | Sales
2  | Service
3  | Techs

The code to create these tables is :

CREATE TABLE [crm].[Documents]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [FileType] [int] NOT NULL,
    [Bytes] [varbinary](max) NOT NULL,
    [IsShared] [bit] NOT NULL,
    [UploadDate] [datetime2](7) NOT NULL,
    [LastSaveDate] [datetime2](7) NOT NULL,

    CONSTRAINT [PK_Documents] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [scheduler].[Groups]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,

    CONSTRAINT [PK_Groups] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [crm].[GroupDocuments]
(
    [GroupId] [int] NOT NULL,
    [DocumentId] [int] NOT NULL,

    CONSTRAINT [PK_GroupDocuments] 
        PRIMARY KEY CLUSTERED ([GroupId] ASC, [DocumentId] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [crm].[GroupDocuments] WITH CHECK 
    ADD CONSTRAINT [FK_GroupDocuments_Documents_GroupId] 
        FOREIGN KEY([GroupId]) REFERENCES [crm].[Documents] ([Id])
                ON DELETE CASCADE
GO

ALTER TABLE [crm].[GroupDocuments] CHECK CONSTRAINT [FK_GroupDocuments_Documents_GroupId]
GO

ALTER TABLE [crm].[GroupDocuments]  WITH CHECK 
    ADD CONSTRAINT [FK_GroupDocuments_Groups_DocumentId] 
        FOREIGN KEY([DocumentId]) REFERENCES [scheduler].[Groups] ([Id])
                ON DELETE CASCADE
GO

ALTER TABLE [crm].[GroupDocuments] CHECK CONSTRAINT [FK_GroupDocuments_Groups_DocumentId]
GO

When I run

INSERT INTO crm.GroupDocuments (GroupId, DocumentId) 
VALUES (3, 1)

I get this error:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_GroupDocuments_Documents_GroupId".
The conflict occurred in database "Scheduler", table "crm.Documents", column 'Id'.

There is an Id 3 on the group table and an Id 1 on the documents table. Can someone please explain why this error would occur?

Answer 1

You've criss-crossed the referenced columns in the definition of your foreign key constraints .... your DocumentID references Groups.Id, while GroupId references Documents.Id....

Change your FK statements to:

-- "GroupId" should reference "Groups.Id" (not "Documents.Id") .....
ALTER TABLE [crm].[GroupDocuments] WITH CHECK 
    ADD CONSTRAINT [FK_GroupDocuments_GroupId] 
        FOREIGN KEY([GroupId]) REFERENCES [scheduler].[Groups]([Id])
                ON DELETE CASCADE
GO

-- and "DocumentId" should reference "Documents.Id" (not "Groups.Id")        
ALTER TABLE [crm].[GroupDocuments]  WITH CHECK 
    ADD CONSTRAINT [FK_GroupDocuments_DocumentId] 
        FOREIGN KEY([DocumentId]) REFERENCES [crm].[Documents]([Id])
                ON DELETE CASCADE
GO

Answer 2

You have inverted your FOREIGN KEY references....

ALTER TABLE [crm].[GroupDocuments]  
ADD  CONSTRAINT [FK_GroupDocuments_Documents_GroupId] 
     FOREIGN KEY([**GroupId**]) REFERENCES [crm].[**Documents**] ([Id])