The INSERT statement conflicted with the FOREIGN KEY constraint

AVELIU 26 Reputation points
2022-01-23T18:39:54.883+00:00

I have an issue with the this:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_f_salesDocType_02084FDA". The conflict occurred in database "NeptunDWBI", table "dbo.d_docType", column 'id'.".

Here are the tabels:

167388-image.png

This is the f_sales:

167491-image.png

Any ide what is the problem here!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,655 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} vote

2 answers

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points
    2022-01-24T06:58:02.747+00:00

    Hi,@AVELIU

    The conflict occurred in database "NeptunDWBI", table "dbo.d_docType", column 'id'."

    The problem occurs when you are trying to insert a record with a value in the foreign key column that doesn't exist in the parent table.
    Run sp_help 'dbo.Sup_Item_Cat'. See which column that foreign key is on, and which column of which table it references.

    To solve thie issue ,you may Insert Data into the parent table(d_docType) containing the Primary Key before attempting to insert data into the child table(f_sales) containing the Foreign Key.
    If SQL Server 2016 (13.x) and later, have a look on this document: Disable Foreign Key Constraints with INSERT and UPDATE Statements

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 100.8K Reputation points MVP
    2022-01-23T19:20:48.997+00:00

    The problem can be illustrated by this script:

    CREATE TABLE Customers (CustomerID   int NOT NULL,
                            CustomerName nvarchar(40) NOT NULL,
                            MoreData     nvarchar(200) NOT NULL,
                            CONSTRAINT pk_Customers PRIMARY KEY(CustomerID)
    )
    
    CREATE TABLE Orders(OrderID   int NOT NULL,
                        CustomerID int NOT NULL,
                        MoreData   nvarchar(300) NOT NULL,
                        CONSTRAINT pk_Orders PRIMARY KEY (OrderID),
                        CONSTRAINT fk_Orders_Customers 
                           FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    )
    go
    INSERT Customers(CustomerID, CustomerName, MoreData)
       VALUES(1, 'John Doe', 'Address etc')
    go
    -- Runs successfully.
    INSERT Orders (OrderID, CustomerID, MoreData)
       VALUES(1, 1, 'OrderDate, DiscountCode etc.')
    go
    -- Fails - no CustomerID 2!
    go
    INSERT Orders (OrderID, CustomerID, MoreData)
       VALUES(2, 2, 'OrderDate, DiscountCode etc.')
    go
    DROP TABLE Orders, Customers
    

    The error message is:

    Msg 547, Level 16, State 0, Line 24

    The INSERT statement conflicted with the FOREIGN KEY constraint "fk_Orders_Customers". The conflict occurred in database "tempdb", table "dbo.Customers", column 'CustomerID'.

    That is, you are try to insert a row into the table f_sales with an id that is not available in the table d_DocType.

    Since I don't see your query, but only images of which I don't really know the relevance, I will have to guess. But I see that in f_sales there is a value 1110 in the DocTypeId column. In the first table (I guess this is d_DocType?), there is no id 1110. There is a value 1110 in the DocumentId column, but we can tell from the error message the foreign key is set up against the column id.

    A tip for the next time when you have a problem: include any query you are running, so that we have to read your mind.