CODEWITHSUNDEEP
sqlsql-server
VegasLearner702
VegasLearner702

Reputation: 11

INSERT statement conflicted with foreign key constraint?

I'm having issue with an assignment I'm working on right now. The question is:

Write an INSERT statement that adds this row to the Products table:

Use a column list for this statement.

And the answer I came up with is:

INSERT INTO Products(CategoryID, ProductCode, ProductName, Description, ListPrice, DiscountPercent, DateAdded)
VALUES (4, 'dgx_640', 'Yamaha DGX 640 88-Key Digital Piano', 'Long description to come.', 799.99, 0, SYSDATETIME())

But when I try to execute it, an error comes up saying

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Products__Catego__3B75D760". The conflict occurred in database "MyGuitarShop", table "dbo.Categories", column 'CategoryID'. The statement has been terminated.

Any help would be appreciated

Upvotes: 1

Views: 25684

Answers (3)

Aakburns
Aakburns

Reputation: 307

enter image description hereThe reference may not need to be enforced.

Make a new Database Diagram and add your two tables. Right click the connection line going between them, choose properties. Where it says 'Enforce Foreign Key Constraint' change it to No. This fixed the issue for me.

It is also possible that the number you're trying to insert and reference is an auto number row as a primary key in the connected table. I can only assume this can cause as issue, vs having the column be it's own editable column value.

Upvotes: 1

idrissAbbou
idrissAbbou

Reputation: 105

you are trying to insert a value in the foreign key that doesn't exist in the table it reference, in you'r case it's the CategoryID. to solve this you need to insert number 4 in the CategoryID column in the table catego first

Upvotes: 0

marc_s
marc_s

Reputation: 755541

The error is very clear - you're trying to insert a value into the CategoryID column of Products which causes a violation of the foreign key constraint to the category table.

This means: you're trying to insert a value (4) into Products.CategoryID which does not exist in the Category table. The foreign key constraint's job is to prevent exactly this case - it will not allow you to do this - for good reason.

So basically: you need to ensure that the values you're inserting into foreign key columns do exist in the referenced table (Category) before you do the INSERT.

Upvotes: 4

Related Questions