Конфликт инструкции insert с ограничением foreign key sql server - Arganabio.ru - инструкции по эксплуатации и многое другое

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.7k6 gold badges37 silver badges81 bronze badges

asked Jun 3, 2010 at 12:24

SmartestVEGASmartestVEGA

8,26524 gold badges83 silver badges135 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.7k6 gold badges37 silver badges81 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.7k6 gold badges37 silver badges81 bronze badges

answered Jan 17, 2014 at 20:50

plasmasnakeneoplasmasnakeneo

2,2111 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

5771 gold badge9 silver badges36 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.7k6 gold badges37 silver badges81 bronze badges

answered Jun 3, 2010 at 12:27

Justin NiessnerJustin Niessner

241k40 gold badges406 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.7k6 gold badges37 silver badges81 bronze badges

answered May 30, 2014 at 21:06

John WaclawskiJohn Waclawski

9161 gold badge10 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.7k6 gold badges37 silver badges81 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.7k6 gold badges37 silver badges81 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.7k6 gold badges37 silver badges81 bronze badges

answered Apr 28, 2021 at 22:12

MikeMike

19.6k25 gold badges97 silver badges133 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.7k6 gold badges37 silver badges81 bronze badges

answered May 14, 2014 at 19:12

Paulie22Paulie22

1111 gold badge1 silver badge9 bronze badges

Источник

I think there are two cases:

1.

In your table dbo.tblBook, it has a column named CatID act as foreign key reference to dbo.tblCategory table. It seems that you’re trying to use CatID property that haven’t been created yet.if so, go back and create a CatID first.

2.

Maybe there are more than one foreign key that related to your table.
if you’re using sql server so below sql statement will help you find all of them, check it out and drop the leftover, good luck!

    USE <database_name>;  
GO  
SELECT   
    f.name AS foreign_key_name  
   ,OBJECT_NAME(f.parent_object_id) AS table_name  
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name  
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object  
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name  
   ,is_disabled  
   ,delete_referential_action_desc  
   ,update_referential_action_desc  
FROM sys.foreign_keys AS f  
INNER JOIN sys.foreign_key_columns AS fc   
   ON f.object_id = fc.constraint_object_id   
--WHERE f.parent_object_id = OBJECT_ID('HumanResources.Employee'); 
GO 
SELECT 
f.name AS foreign_key_name 
,OBJECT_NAME(f.parent_object_id) AS table_name 
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name 
,OBJECT_NAME (f.referenced_object_id) AS referenced_object 
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name 
,is_disabled 
,delete_referential_action_desc 
,update_referential_action_desc 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
ON f.object_id = fc.constraint_object_id 
--WHERE f.parent_object_id = OBJECT_ID('HumanResources.Employee');

Источник

Remove From My Forums

Question
I have recently installed a reporting software for vmware vsphere however I am getting a SQL error. I have opened a support request with vmware but so far they have not come up with a solution. The error is Caused by: com.microsoft.sqlserver.jdbc.SQLServerException:
The INSERT statement conflicted with the FOREIGN KEY constraint «FK_CB_VSM_NETWORK_VC_ID». The conflict occurred in database «VCChargebackVCC02», table «dbo.CB_VSM_SERVER», column ‘VC_ID’. I dont know much about SQL so I am lost as far as troubleshooting
is concerned. If anyone has any ideas I’d love to hear them.

— ATG —

Answers

The issue is most likely in your reporting software or in the associated database setup. You should contact the vendor of that software for support.
- Marked as answer by
  
  Saturday, September 15, 2012 7:20 AM

Источник

I’m working on a data migration from one database to a another.

So far everything is working but with one table I always get the same error. There is a problem with the foreign key that I also want to migrate in this table. I think I have to declare something special for a foreign key?

This is my query:

INSERT INTO [asd].HolidayTracker.dbo.AnnualVacation(UserId,WorkingTime,VacationDays,FromDate,ToDate)
SELECT u1.user_htUserId,
       u1.vacationDays,
       u1.workingTime,
       CAST('2013-01-01' AS DATETIME),
       CAST('2013-12-31' AS DATETIME)

FROM    HolidayTracker.dbo.AnnualVacation u1 
            LEFT JOIN  [asd].HolidayTracker.dbo.AnnualVacation u2
                ON u1.user_htUserId = u2.UserId AND 
                   u1.vacationDays = u2.VacationDays AND
                   u1.workingTime = u2.WorkingTime

This is the error message I get:

error message The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AnnualVacation_HtUser1". The conflict occurred in database "HolidayTracker", table "dbo.HtUser", column 'UserId'.

This is the table structure: Source table

Destination Table

My User table where all users are saved

Thanks for help and fast answer

Изменено

3 апреля 2013 г. 7:53

Источник

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)

Источник

I think there are two cases:

1.

2.

Question

Answers