The INSERT statement conflicted with the FOREIGN KEY constraint

Question

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!

Answer 1

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.

Answer 2

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.