How to resolve a conflict between an insert statement and a foreign key constraint in the SQL database

Question

1.00/5 (1 vote)

See more:

insert into Management.Events(EventName, StartDate, EndDate, Location, NoOfPeople, StaffRequired, EventTypeID, CustomerID, EmployeeID)
Values('fashion show', '2021-12-25', '2021-12-25', 'el cercado', '50', '2', '8672', '123451', '4'),
('exhibitions', '2022-01-25', '2022-01-27', 'el cercado', '45', '3', '8673', '123452', '2'),
('charity show', '2022-02-25', '2022-02/27', 'el cercado', '40', '5', '8674', '123453', '6')

When i try to execute this query it shows me this

Msg 547, Level 16, State 0, Line 38
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Events__EventTyp__20C1E124". The conflict occurred in database "ShowmanHouse", table "Events.EventTypes", column 'EventTypeID'.
The statement has been terminated.

What I have tried:

What i have tried to do is to check the insert statement again and try to see if i can find the problem

Posted 25-Jul-21 9:49am

Ummulkhair

Updated 25-Jul-21 12:45pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-07-25T11:18:00

You can't "resolve" this: its is exactly what a foreign key relationship is there for!

When you create a foreign key, you are saying that "the information in this row is related to that row of a different table" - hence the name being "Foreign" (as in to this table) and "key" to index the foreign table.

Take an example: Invoicing. You have an Invoice which contains multiple lines of product - the customer wants 3 Widgets and 2 Doodads. So your DB has an Invoices table which holds the customer account number, the invoice date, total due, and an invoice number; and it also contains an InvoiceLines table which has a row ID, a foreign key to the Invoices table via the Invoice Number, the product information, quantity, and price.

If you INSERT the InvoiceLines first, the DB has no idea what Invoice the are associated with - the data is inconsistent because it is incomplete.
What you have to do is create the Invoice first, which provides you with the Invoice Number you need to create the InvoiceLines rows.

So you can't "resolve" it as a problem: you have to create the "target" row first, and then create the foreign key data.

Patrice T · Answer 2 · 2021-07-25T12:45:00

Quote:
How to resolve a conflict between an insert statement and a foreign key constraint in the SQL database

Looks like you need to learn what is a 'FOREIGN KEY Constraint' : SQL FOREIGN KEY Constraint[^]
Assuming the constraint is not something random, the only way to resolve the conflict is to insert data that respect the constraint. The one which designed the database probably have good reasons to set the constraint, only him can tell you.

How to resolve a conflict between an insert statement and a foreign key constraint in the SQL database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0