Reputation: 11
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
Reputation: 307
The 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
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
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