Конфликт инструкции insert с ограничением foreign key как исправить - Большая энциклопедия инструкций и руководств

Вопрос:

Я получаю следующую ошибку. Не могли бы вы мне помочь?

Сообщение 547, уровень 16, состояние 0, строка 1
Оператор INSERT конфликтует с ограничением FOREIGN KEY “FK_Sup_Item_Sup_Item_Cat”. Конфликт произошел в базе данных “dev_bo”, таблица “dbo.Sup_Item_Cat”. Заявление было прекращено.

Код:

insert into sup_item (supplier_id, sup_item_id, name, sup_item_cat_id,
status_code, last_modified_user_id, last_modified_timestamp, client_id)
values (10162425, 10, 'jaiso', '123123',
'a', '12', '2010-12-12', '1062425')

Последний столбец client_id вызывает ошибку. Я попытался поместить значение, которое уже существует в dbo.Sup_Item_Cat в столбец, соответствующий sup_item.. но без радости 🙁

Лучший ответ:

В вашей таблице dbo.Sup_Item_Cat он имеет ссылку внешнего ключа на другую таблицу. Способ работы FK заключается в том, что он не может иметь значение в этом столбце, которое также не находится в столбце первичного ключа ссылочной таблицы.

Если у вас есть SQL Server Management Studio, откройте его и sp_help ‘dbo.Sup_Item_Cat. Посмотрите, в каком столбце включен FK, и в каком столбце которого он ссылается. Вы вставляете некоторые плохие данные.

Сообщите мне, если вам что-то объяснено лучше!

Ответ №1

Я сам столкнулся с этой проблемой, касающейся сообщения об ошибке, полученного при заполнении поля внешнего ключа. Я попал на эту страницу в надежде найти ответ. Проверенный ответ на этой странице действительно правильный, к сожалению, я чувствую, что ответ немного неполон для людей, не знакомых с SQL. Я довольно хорошо умею писать код, но запросы SQL для меня новы, как и создание таблиц базы данных.

Несмотря на то, что проверенный ответ является правильным:

Майк М wrote-

“Способ работы FK заключается в том, что он не может иметь значение в этом столбце, которого нет также в столбце первичного ключа в указанной таблице”.

Чего не хватает в этом ответе просто;

Сначала вы должны создать таблицу, содержащую первичный ключ.

Еще один способ сказать это;

Вы должны вставить данные в родительскую таблицу, содержащую первичный ключ, прежде чем пытаться вставить данные в дочернюю таблицу, содержащую внешний ключ.

Короче говоря, многие учебные пособия, похоже, не соответствуют этому факту, так что если вы попробуете самостоятельно и не поймете, что существует порядок операций, вы получите эту ошибку. Естественно, после добавления данных первичного ключа ваши данные внешнего ключа в дочерней таблице должны соответствовать полю первичного ключа в родительской таблице, в противном случае вы все равно получите эту ошибку.

Если кто-то прочитал это далеко. Я надеюсь, что это помогло сделать проверенный ответ более ясным. Я знаю, что некоторые из вас могут почувствовать, что подобные вещи довольно просты и что открытие книги ответило бы на этот вопрос до того, как она была опубликована, но правда в том, что не все учатся одинаково.

Ответ №2

Вы пытаетесь вставить запись со значением в столбце внешнего ключа, которого нет во внешней таблице.

Например: если у вас есть таблицы “Книги” и “Авторы”, в которых “Книги” имеют ограничение внешнего ключа для таблицы “Авторы”, и вы пытаетесь вставить запись книги, для которой нет записи об авторе.

Ответ №3

Вам нужно будет опубликовать свое выражение для получения дополнительных разъяснений. Но…

Эта ошибка означает, что таблица, в которую вы вставляете данные, имеет отношение внешнего ключа к другой таблице. Прежде чем данные могут быть вставлены, сначала значение в поле внешнего ключа должно находиться в другой таблице.

Ответ №4

Проблема не в client_id из того, что я вижу. Это больше похоже на проблему с 4-м столбцом, sup_item_cat_id

Я бы запустил

sp_helpconstraint sup_item

и обратите внимание на столбец constraint_keys, возвращенный для внешнего ключа FK_Sup_Item_Sup_Item_Cat, чтобы подтвердить, какой столбец является реальной проблемой, но я уверен, что это не тот, который вы пытаетесь исправить. Кроме того, “123123” также выглядит подозрительным.

Ответ №5

Я обнаружил, что все поля должны соответствовать ТОЧНО.

Например, отправка “cat dog” не совпадает с отправкой “catdog”.

То, что я сделал для устранения этого неполадки, заключалось в script из кода FK из таблицы, в которую я вставлял данные, обратите внимание на “Foreign Key” с ограничениями (в моем случае было 2) и убедитесь, что эти 2 значения полей соответствовали ТОЧНО, поскольку они были в таблице, которая бросала ошибку ограничения FK.

Как только я исправил 2 поля, дающие мои проблемы, жизнь была хорошей!

Если вам нужно лучшее объяснение, сообщите мне.

Ответ №6

Это означает именно то, что он говорит. Вы пытаетесь вставить значение в столбец с ограничениями FK на нем, который не соответствует никаким значениям в таблице поиска.

Ответ №7

Данные родительской таблицы не существуют, поэтому она вызывает проблему. Для вышеуказанной проблемы данные недоступны в таблице dbo.Sup_Item_Cat “

Ответ №8

Дважды проверьте поля в отношении, для которого задан внешний ключ. SQL Server Management Studio, возможно, не выбрали нужные вам поля при определении отношения. Это сожгло меня в прошлом.

Ответ №9

запустить sp_helpconstraint
заплатите ВНИМАНИЕ столбцу constraint_keys, возвращенному для внешнего ключа.

Ответ №10

Отсутствие данных родительской таблицы вызывает проблему.
В вашей проблеме не доступность данных в “dbo.Sup_Item_Cat” вызывает проблему

Ответ №11

У меня была такая же проблема, когда я использовал первые преобразования кода для создания моей базы данных для приложения MVC 5. В конце концов я нашел метод seed в моем файле configuration.cs, чтобы вызвать проблему. Мой метод seed создавал запись таблицы для таблицы, содержащей внешний ключ, перед созданием записи с соответствующим первичным ключом.

Ответ №12

Я столкнулся с этой проблемой, когда мои поля значений вставки содержали вкладки и пробелы, которые не были очевидны для невооруженного глаза. Я создал свой список значений в Excel, скопировал и вставил его в SQL, и запустил запросы, чтобы найти несоответствия в моих FK-полях.

В запросах совпадения не было обнаружено, что в моем поле FK есть вкладки и пробелы, но INSERT распознал их и продолжал генерировать ошибку.

Я снова тестировал, копируя содержимое поля FK в одной записи и вставляя его в запрос вставки. Когда эта запись также потерпела неудачу, я подошел ближе к данным и, наконец, обнаружил вкладки/пробелы.

Как только я очистил удаленные вкладки/пробелы, моя проблема была решена. Надеюсь, это поможет кому-то!

Ответ №13

Я также получил ту же ошибку в моем коде SQL, это решение работает для меня,

Проверьте данные в первичной таблице. Возможно, вы вводите значение столбца, которого нет в столбце первичного ключа.

Ответ №14

Иногда это происходит, когда вы пытаетесь Insert/Update объект, в то время как foreign key который вы пытаетесь Insert/Update самом деле не существует. Поэтому убедитесь, что foreign key существует, и повторите попытку.

Источник

February 5, 2019
MSSQL, TSQL

You can get this error when you want to inset data into a table that has the Foreing Key. It means that there is no relevant record in the Primary table that Foreign Key is linked to. The record must first be added to the primary table.

Let’s make an example for a better understanding.

Example:

We create two tables as follows. In the Person table we set the ID column as Primary Key.

CREATE TABLE [dbo].[Person](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NULL,

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED

(

[ID] ASC

)

) ON [PRIMARY]

CREATE TABLE [dbo].[PersonDetails](

[ID] [int] IDENTITY(1,1) NOT NULL,

[PersonID] int NOT NULL,

[SurName] [varchar](50) NULL,

[Age] int

) ON [PRIMARY]

Now let’s set the PersonID column in the PersonDetails table as the Foreign Key of the Primary Key in the Person table.

ALTER TABLE [dbo].[PersonDetails] WITH CHECK ADD CONSTRAINT [FK_PersonDetails_Person] FOREIGN KEY([PersonID])

REFERENCES [dbo].[Person] ([ID])

ALTER TABLE [dbo].[PersonDetails] CHECK CONSTRAINT [FK_PersonDetails_Person]

Then, when there is no record in the Primary table, try to add a record to the Foreign table.

USE [TestDB]

INSERT INTO [dbo].[PersonDetails] ([PersonID],[SurName],[Age]) VALUES (1,‘CAKIR’,34)

We’ll get the error as follows. Because the Primary table does not have the corresponding ID value.

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the FOREIGN KEY constraint “FK_PersonDetails_Person”.

The conflict occurred in database “TestDB”, table “dbo.Person”, column ‘ID’.

The statement has been terminated.

If we add a record to the primary table first, the process of adding records to the foreign table will also be completed successfully.

USE [TestDB]

INSERT INTO [dbo].[Person] ([ID],[Name]) VALUES (1,‘Nurullah’)

INSERT INTO [dbo].[PersonDetails] ([PersonID],[SurName],[Age]) VALUES (1,‘CAKIR’,34)

Источник

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint «FK_Sup_Item_Sup_Item_Cat». The conflict occurred in database «dev_bo», table «dbo.Sup_Item_Cat». The statement has been terminated.

insert into sup_item (supplier_id, sup_item_id, name, sup_item_cat_id, 
                      status_code, last_modified_user_id, last_modified_timestamp, client_id)   
values (10162425, 10, 'jaiso', '123123',
        'a', '12', '2010-12-12', '1062425')

The last column client_id is causing the error. I tried to put the value which already exists in the dbo.Sup_Item_Cat into the column, corresponding to the sup_item.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

asked Jun 3, 2010 at 12:24

SmartestVEGASmartestVEGA

8,37526 gold badges84 silver badges137 bronze badges

Your table dbo.Sup_Item_Cat has a foreign key reference to another table. The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table.

If you have SQL Server Management Studio, open it up and sp_help ‘dbo.Sup_Item_Cat‘. See which column that FK is on, and which column of which table it references. You’re inserting some bad data.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Jun 3, 2010 at 12:29

The answer on this page by Mike M. is correct:

The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table.

What is missing from that answer is: You must build the table containing the primary key first.

You must insert data into the parent table, containing the primary key, before attempting to insert data into the foreign key.

After adding the primary key data, your foreign key data in the child table must conform to the primary key field in the parent table.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Jan 17, 2014 at 20:50

plasmasnakeneoplasmasnakeneo

2,2211 gold badge13 silver badges16 bronze badges

You are trying to insert a record with a value in the foreign key column that doesn’t exist in the foreign table.

For example: If you have Books and Authors tables where Books has a foreign key constraint on the Authors table and you try to insert a book record for which there is no author record.

Katianie

5851 gold badge9 silver badges37 bronze badges

answered Jun 3, 2010 at 12:31

Matthew SmithMatthew Smith

1,2771 gold badge9 silver badges19 bronze badges

That error means that the table you are inserting data into has a foreign key relationship with another table. Before data can be inserted, the value in the foreign key field must exist in the other table first.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Jun 3, 2010 at 12:27

Justin NiessnerJustin Niessner

242k40 gold badges408 silver badges536 bronze badges

The problem is not with client_id from what I can see. It looks more like the problem is with the 4th column, sup_item_cat_id

I would run

sp_helpconstraint sup_item

and pay attention to the constraint_keys column returned for the foreign key FK_Sup_Item_Sup_Item_Cat to confirm which column is the actual problem, but I am pretty sure it is not the one you are trying to fix. Besides ‘123123’ looks suspect as well.

answered Jun 3, 2010 at 12:44

CobusveCobusve

1,56210 silver badges23 bronze badges

All the fields have to match EXACTLY.

For example, sending ‘cat dog’ is not the same as sending ‘catdog’.

To troubleshoot this: Script out the FK code from the table you ae inserting data into, take note of the «Foreign Key» that has the constraints and make sure those fields’ values match EXACTLY as they were in the table throwing the FK Constraint error.

Fix the fields.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered May 30, 2014 at 21:06

John WaclawskiJohn Waclawski

9361 gold badge11 silver badges20 bronze badges

My insert value fields contained tabs and spaces that were not obvious to the naked eye. I had created my value list in Excel, copied, and pasted it to SQL, and run queries to find non-matches on my FK fields.

The match queries did not detect there were tabs and spaces in my FK field, but the INSERT did recognize them and it continued to generate the error.

I tested again by copying the content of the FK field in one record and pasting it into the insert query. When that record also failed, I looked closer at the data and finally detected the tabs/spaces.

Once I cleaned removed tabs/spaces, my issue was resolved.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Oct 23, 2015 at 13:40

Double check the fields in the relationship the foreign key is defined for. SQL Server Management Studio may not have had the fields you wanted selected when you defined the relationship. This has burned me in the past.

answered Jan 6, 2014 at 19:27

run sp_helpconstraint
pay attention to the constraint_keys column returned for the foreign key

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Apr 12, 2014 at 16:20

In my case, I was inserting the values into the child table in the wrong order:

For the table with 2 columns: column1 and column2, I got this error when I mistakenly entered:

INSERT INTO Table VALUES('column2_value', 'column1_value');

The error was resolved when I used the below format:-

INSERT INTO Table (column2, column1) VALUES('column2_value', 'column1_value');

answered Sep 12, 2020 at 6:45

The problem was reproducible and intermittent for me using mybatis.
I had correct DB configuration (PK, FK, auto increment etc)
I had correct order of insertions (parent records first); in debug I could see the parent record inserted with respective PK and just after that next statement failed with inserting the child record with the correct FK inside.

The problem was fixed by reseeding identity with

DBCC CHECKIDENT ('schema.customer', RESEED, 0);
DBCC CHECKIDENT ('schema.account', RESEED, 0);

The code that failed before started to work.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Apr 28, 2021 at 22:12

MikeMike

19.9k25 gold badges97 silver badges138 bronze badges

I used code-first migrations to build my database for an MVC 5 application. The seed method in my configuration.cs file was causing the issue, creating a table entry for the table containing the foreign key before creating the entry with the matching primary key.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered May 14, 2014 at 19:12

Paulie22Paulie22

1111 gold badge1 silver badge9 bronze badges

The error you’re encountering is due to a violation of the FOREIGN KEY constraint FK_Sup_Item_Sup_Item_Cat when attempting to insert a value into the client_id column of the sup_item table. The error message indicates that the value you’re trying to insert does not exist in the referenced table Sup_Item_Cat in the dev_bo database.

To resolve this issue, you should ensure that the value you’re inserting into the client_id column matches an existing value in the referenced table Sup_Item_Cat. You can verify the available values in the Sup_Item_Cat table and make sure you’re providing a valid client_id.

Here’s an example query to retrieve the existing client_id values from the Sup_Item_Cat table:

SELECT client_id
FROM dbo.Sup_Item_Cat;

Review the output of this query and confirm that the value you’re trying to insert into the client_id column exists in the Sup_Item_Cat table.

To identify which other tables and columns a particular table depends on, you can use the information from the Keys folder and Depends on dbForge Studio for SQL Server.

answered Jun 22 at 15:18

I experienced same issue while running the query:

INSERT INTO [dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

Meanwhile, I confirmed that the values 70004 and 150044 exist in the corresponding tables.

I fixed this by including the database name as follows:

INSERT INTO [TIS].[dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

answered Jun 28 at 1:11

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint «FK_Sup_Item_Sup_Item_Cat». The conflict occurred in database «dev_bo», table «dbo.Sup_Item_Cat». The statement has been terminated.

insert into sup_item (supplier_id, sup_item_id, name, sup_item_cat_id, 
                      status_code, last_modified_user_id, last_modified_timestamp, client_id)   
values (10162425, 10, 'jaiso', '123123',
        'a', '12', '2010-12-12', '1062425')

The last column client_id is causing the error. I tried to put the value which already exists in the dbo.Sup_Item_Cat into the column, corresponding to the sup_item.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

asked Jun 3, 2010 at 12:24

SmartestVEGASmartestVEGA

8,37526 gold badges84 silver badges137 bronze badges

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Jun 3, 2010 at 12:29

The answer on this page by Mike M. is correct:

The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table.

What is missing from that answer is: You must build the table containing the primary key first.

You must insert data into the parent table, containing the primary key, before attempting to insert data into the foreign key.

After adding the primary key data, your foreign key data in the child table must conform to the primary key field in the parent table.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Jan 17, 2014 at 20:50

plasmasnakeneoplasmasnakeneo

2,2211 gold badge13 silver badges16 bronze badges

You are trying to insert a record with a value in the foreign key column that doesn’t exist in the foreign table.

For example: If you have Books and Authors tables where Books has a foreign key constraint on the Authors table and you try to insert a book record for which there is no author record.

Katianie

5851 gold badge9 silver badges37 bronze badges

answered Jun 3, 2010 at 12:31

Matthew SmithMatthew Smith

1,2771 gold badge9 silver badges19 bronze badges

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Jun 3, 2010 at 12:27

Justin NiessnerJustin Niessner

242k40 gold badges408 silver badges536 bronze badges

The problem is not with client_id from what I can see. It looks more like the problem is with the 4th column, sup_item_cat_id

I would run

sp_helpconstraint sup_item

answered Jun 3, 2010 at 12:44

CobusveCobusve

1,56210 silver badges23 bronze badges

All the fields have to match EXACTLY.

For example, sending ‘cat dog’ is not the same as sending ‘catdog’.

Fix the fields.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered May 30, 2014 at 21:06

John WaclawskiJohn Waclawski

9361 gold badge11 silver badges20 bronze badges

The match queries did not detect there were tabs and spaces in my FK field, but the INSERT did recognize them and it continued to generate the error.

Once I cleaned removed tabs/spaces, my issue was resolved.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Oct 23, 2015 at 13:40

answered Jan 6, 2014 at 19:27

run sp_helpconstraint
pay attention to the constraint_keys column returned for the foreign key

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Apr 12, 2014 at 16:20

In my case, I was inserting the values into the child table in the wrong order:

For the table with 2 columns: column1 and column2, I got this error when I mistakenly entered:

INSERT INTO Table VALUES('column2_value', 'column1_value');

The error was resolved when I used the below format:-

INSERT INTO Table (column2, column1) VALUES('column2_value', 'column1_value');

answered Sep 12, 2020 at 6:45

The problem was fixed by reseeding identity with

DBCC CHECKIDENT ('schema.customer', RESEED, 0);
DBCC CHECKIDENT ('schema.account', RESEED, 0);

The code that failed before started to work.

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered Apr 28, 2021 at 22:12

MikeMike

19.9k25 gold badges97 silver badges138 bronze badges

philipxy

14.8k6 gold badges39 silver badges82 bronze badges

answered May 14, 2014 at 19:12

Paulie22Paulie22

1111 gold badge1 silver badge9 bronze badges

Here’s an example query to retrieve the existing client_id values from the Sup_Item_Cat table:

SELECT client_id
FROM dbo.Sup_Item_Cat;

Review the output of this query and confirm that the value you’re trying to insert into the client_id column exists in the Sup_Item_Cat table.

To identify which other tables and columns a particular table depends on, you can use the information from the Keys folder and Depends on dbForge Studio for SQL Server.

answered Jun 22 at 15:18

I experienced same issue while running the query:

INSERT INTO [dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

Meanwhile, I confirmed that the values 70004 and 150044 exist in the corresponding tables.

I fixed this by including the database name as follows:

INSERT INTO [TIS].[dbo].[work_flow_actions] ([work_flow_id] ,[actions_id]) VALUES (70004, 150044)

answered Jun 28 at 1:11

Error: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint «FK__Item__order__3AE27131». The conflict occurred in database «pmall», table «dbo.ItemSaved», column ‘id’.

Here’s my table:

ItemSavedUnits

id
ItemID (is set in this table a FK to Item.id)
…etc.

Here’s my insert statement:

insert into ItemSavedUnits (ItemID, name, Price)
select ItemID, name,Price
from ItemUnits where ItemID = 92439

I don’t really understand why if I a FK constraint on ItemSavedUnits.ItemID that is related to Item.ItemID and ItemUnits has no constraints at all why I’m getting a problem inserting into ItemSavedUnits. The ItemID I’m tryign to insert does exist in the Item table.

asked Dec 1, 2009 at 19:38

PositiveGuyPositiveGuy

46.5k110 gold badges304 silver badges471 bronze badges

Are you absolutely sure that ItemId 92439 exists in the Item table, and not just in ItemUnits?

Is your select statement returning null?

answered Dec 1, 2009 at 19:40

It looks like you need a row in ItemUnits with that ID first — what does the SELECT statement part of your insert return? No rows?

Also, is there a trigger on the ItemSavedUnits table that could be causing problems?

answered Dec 1, 2009 at 19:43

SqlRyanSqlRyan

33k33 gold badges114 silver badges198 bronze badges

Your foreign key constraint violation doesn’t appear to deal with the ItemSavedUnits table — the violation exception is being thrown by the constraint on the ItemSaved table, not the ItemSavedUnits table. Is there a trigger on ItemSavedUnits that’s trying to insert into ItemSaved?

answered Dec 1, 2009 at 19:45

DathanDathan

7,2663 gold badges27 silver badges46 bronze badges

Remove From My Forums

Вопрос

Здравствуйте, делаю проект с использованием EF. И он выдает ошибку

Конфликт инструкции INSERT с ограничением FOREIGN KEY «FK_dbo.Attachments_dbo.NotificationEFs_AttachmentId». Конфликт произошел в базе данных таблица «dbo.NotificationEFs», column ‘newId’.Выполнение данной инструкции было
прервано.»}

Вот сам код

var entity = ParserNotificationTender.GetNotifycationEntity(f);
var placingway = ParserNotificationTender.GetPlacingWayEntity(f);
                            var etp = ParserNotificationTender.GetETPEntity(f);
                            var attachments = ParserNotificationTender.GetAttachmentEntity(f);
                            printform.Notification = entity;
                            placingway.Notify = entity;
                            etp.NotifyEtp = entity;
                            foreach (var attachment in attachments)
                            {
                                attachment.notifyId = entity;
                                if (attachment.CryptoSignObg != null)
                                {
                                    attachment.CryptoSignObg.AttachmentNotify = attachment;
                                    listCryptosign.Add(attachment.CryptoSignObg);
                                }
                            }
                            if (entity != null)
                            {
                                listIntity.Add(entity);
                                listPrintForm.Add(printform);
                                lispPlacingWay.Add(placingway);
                                listETP.Add(etp);
                                if (attachments != null)
                                {
                                    listAttachment.Add(attachments);
                                }
                                
                            }

Далее вызывается этот метод

public static void AddCryptoSign(List<CryptoSignsAttachment> cryptosignrange)
        {
            using (var context = new DBEntity())
            {
                foreach (var obj in cryptosignrange)
                {
                    context.CryptoSignObj.Add(obj);
                    context.SaveChanges();
                }
            }
        }

и в нем как раз эта ошибка происходит, причем всегда во второй итерации.

Перемещено

11 января 2015 г. 12:46

Ответы

Ошибка связана с тем, что у вас вводится значиние которое отсуствует в свяной таблице (на которое указывает внешний ключ). Т.е. одному из полей сушности назначается неверное значение. Вам надо или задать его правильно или снять ограничение
внешнего ключа с таблицы.

Сделаем содержимое сообщества лучше, вместе!

The insert statement conflicted with the foreign key constraint error is an error you may face as a database administrator and developer.

This error occurs when data is being inserted into a table and violates the foreign key constraints defined in the database schema. In this article, you will explore what causes this error and practical solutions to resolve it.

Contents

Why Am I Triggering This Foreign Key Constraint Error?
- – Why the Foreign Key Triggers It and Not Anything Else?
- – Common Reasons Behind This Error
Quick and Reliable Solutions to the Database Insert Error
- – Disabling Foreign Keys Quick Fix – The Controversial One
- – Updating the Database Schema for a Lasting Solution
Conclusion

Why Am I Triggering This Foreign Key Constraint Error?

You are triggering the insert statement conflicted with the foreign key constraint error because of referencing a non-existent primary key in a foreign key constraint defined in the database schema. It occurs when a foreign key relationship between two tables in a database is violated.

When a foreign key relationship is defined in a database, the foreign key field in the child table must reference a valid primary key in the parent table. If the primary key you are referencing in the foreign key does not exist in the parent table, a conflict will occur when you insert data into the child table.

For example, consider two tables in a database: Orders and Customers. The Orders table has a foreign key that references the primary key of the Customers table. When you try to insert a new order into the Orders table, the foreign key in the Orders table must reference a valid customer in the Customers table.

If the customer ID referenced in the foreign key does not exist in the Customers table, a conflict will occur, and the database management system will generate this error message.

– Why the Foreign Key Triggers It and Not Anything Else?

A foreign key is a field in one table that refers to the primary key of another table. The purpose of a foreign key is to establish a relationship between two tables and enforce referential integrity. In other words, it ensures that data in one table matches existing data in another table.

A foreign key constraint is defined in the database schema and ensures that the data you insert into the child table (the table with the foreign key) references existing data in the parent table (the referenced table).

When you try to insert data into a table that has a foreign key constraint and the data you are inserting does not match the values in the referenced parent table, a conflict occurs, and the database management system generates this error message.

– Common Reasons Behind This Error

As a developer or a database administrator, you are likely to trigger this error on various platforms. Below is a quick look at the error and some of the platforms you are likely to trigger:

the insert statement conflicted with the foreign key constraint spring boot: The error can occur in a Spring Boot application.
the insert statement conflicted with the foreign key constraint laravel: This error can also occur in Laravel applications.
the insert statement conflicted with the foreign key constraint “fk_dbo. The error message is generated when you try to insert data into a database table that violates the foreign key constraint defined in the database schema. The error message fk_dbo is the name of the foreign key constraint that is causing the conflict.
the insert statement conflicted with the foreign key constraint null: This error message with null often occurs when you try to insert a null value into a foreign key column in a database table.
the insert statement conflicted with the foreign key constraint outsystems: The error can also occur in OutSystems applications when attempting to insert data into a database table that violates the foreign key constraint defined in the database schema.
the insert statement conflicted with the foreign key constraint entity framework core: This error can occur in Entity Framework Core, which is an Object-Relational Mapping (ORM) framework for .NET applications.
the insert statement conflicted with the foreign key constraint informatica: The error can occur in Informatica, which is a data integration and management software.
the insert statement conflicted with the foreign key constraint jpa: This error can arise when using Java Persistence API (JPA) to interact with a relational database. JPA is a Java-based API for accessing and managing data in a relational database.

Quick and Reliable Solutions to the Database Insert Error

The quick and reliable way to solve the insert statement conflicted with the foreign key constraint error is to ensure that the referenced primary key exists in the parent table. Moreover, you can also fix this database insert error by disabling foreign keys.

First, identify the parent table and find out which table the foreign key in the child table references. This is the parent table.

Second, check the parent table for the referenced primary key. For this, query the parent table to see if the primary key value referenced by the foreign key in the child table exists.

Third, you will need to insert the missing primary key if the primary key value does not exist in the parent table. You can do this by adding a new record to the parent table with the missing primary key value.

Lastly, update the foreign key value in the child table. Once the primary key value has been inserted into the parent table, you can update the foreign key value in the child table to match the newly inserted primary key value.

You will resolve this error by ensuring that the referenced primary key exists in the parent table. This ensures that the data in the child table is consistent with the data in the parent table, which is necessary for maintaining data integrity in a relational database.

– Disabling Foreign Keys Quick Fix – The Controversial One

Disabling the foreign key constraint can be a temporary solution to the “insert statement conflicted with the foreign key constraint” error. However, it is not advisable since it may lead to data integrity issues. That said, below are steps you can follow to disable the foreign key constraint.

First, you need to determine which foreign key constraint is causing the error. You can do this by checking the database schema or by examining the error message.

Second, depending on the database technology you are using, you may be able to temporarily disable the foreign key constraint using a SQL command or through a graphical interface. For instance, in SQL Server, you can use the ALTER TABLE statement to temporarily disable the foreign key constraint.

Third, after disabling the foreign key constraint, you should be able to insert the data that was causing the error. Lastly, Once you insert the data causing this error, you should re-enable the foreign key constraint to maintain data integrity.

Keep in mind that disabling the foreign key constraint can lead to data integrity issues if the data in the child table does not match the data in the parent table. It is better to identify the root cause of the error and solve it in a way that maintains data integrity.

In general, disabling the foreign key constraint should only be used as a last resort and with caution. Before disabling the constraint, it’s important to understand the implications and to have a backup of the data.

– Updating the Database Schema for a Lasting Solution

Updating the database schema can be a solution to this error when the schema does not reflect the current data in the tables. If the foreign key constraint is not necessary, you can remove it from the database schema.

However, if the constraint is necessary, you may need to update the schema to ensure that the data being inserted will not violate the constraint in the future.

To do this, determine the root cause of the error by examining the data in the tables and the relationships between them. Before making any changes to the schema, make sure you backup the database to ensure that you can revert to a previous state if necessary.

Now depending on the issue you identified, you may need to add or modify columns in the table, update relationships between tables, or make other changes to the schema to ensure that it reflects the current data.

After making changes to the schema, you may need to update the data in the tables to ensure that it conforms to the new schema.

Finally, test the changes by attempting to insert data that previously caused the error in question. If the error persists, you may need to make further changes to the schema.

Once the schema and data have been updated and tested, deploy the changes to the production environment. It is worth noting that updating the database schema is a complex process that requires a deep understanding of the data and the relationships between tables. Before making changes to the schema, make sure to understand the implications and that you have a backup of the data.

Conclusion

By reading this article, you have gained insight into the causes and solutions for the “insert statement conflicted with the foreign key constraint” error. The key takeaways include the following:

The error arises when referencing a non-existent primary key.
To solve this error, ensure the referenced primary key exists.
Another solution involves the controversial disabling of the foreign key constraint.
Also, you can solve this error by updating the database schema.

By becoming familiar with these concepts, you are now equipped with the knowledge necessary to address and resolve this error in a variety of database management systems.

Author
Recent Posts

Your Go-To Resource for Learn & Build: CSS,JavaScript,HTML,PHP,C++ and MYSQL. Meet The Team

Источник