The DELETE statement conflicted with the REFERENCE constraint

rajesh prajapati 1

I have a two tables static_data_value and calc_invoice_volume Pk-FK relationship with on delete no action defined during table creation.

value_id =invoice_line_item_id (PK - FK relationship )

The cal_invoice_volume has no row for the corresponding value_id =29 when try to delete the record from static_data_volume table ,got the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_calc_invoice_volume_static_data_value". The conflict occurred in database "TRMTracker_LADWP", table "dbo.calc_invoice_volume", column 'invoice_line_item_id'.

Command executed:

DELETE FROM static_data_value where value_id =29
Delete from calc_invoice_volume where invoice_line_item_id IN (29)

The PK table should have no issue to delete 29 row. Please let me know the solution for this issue.

Note: calc_invoice_volume table has other FK relationship with other tables here.

Seeya Xi-MSFT 16,446 Reputation points

2021-07-30T07:00:23.42+00:00

Hi @rajesh prajapati ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

3 answers

Seeya Xi-MSFT 16,446 Reputation points

2021-07-28T08:13:04.723+00:00

Hi @rajesh prajapati ,

The error means that you have data in other tables that references the data you are trying to delete.
This is a similar thread.
You would need to drop and recreate the constraints.

Best regards,
Seeya

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
rajesh prajapati 1 Reputation point

2021-07-29T11:34:29.497+00:00

Do we need to drop and recreate the constraints when there is no corresponding record in FK table for 29 ?
select * from calc_invoice_volume where invoice_line_item_id IN (29)
[Empty set]

(0 rows affected)
Completion time: 2021-07-29T17:16:56.2464961+05:45

Why this statement is throwing an error when there is no records for 29 in FK table (calc_invoice_volume)
DELETE FROM static_data_value where value_id =29

Msg 547, Level 16, State 0, Line 48
The DELETE statement conflicted with the REFERENCE constraint "FK_calc_invoice_volume_static_data_value". The conflict occurred in database "TRMTracker_LADWP_UAT", table "dbo.calc_invoice_volume", column 'invoice_line_item_id'.
The statement has been terminated.

Completion time: 2021-07-29T17:18:54.0936326+05:45

rajesh prajapati 1 Reputation point

2021-08-02T12:20:42.167+00:00

Since my answer has not been resolved. I will get back to you again and try to cover more details.

Seeya Xi-MSFT 16,446 Reputation points

2021-08-04T07:49:03.727+00:00

Hi @rajesh prajapati ，

Before that, you can check it as mentioned by Erland, and then try to delete the constraint if it still fails.

Best regards,
Seeya

rajesh prajapati 1 Reputation point

2021-08-10T08:20:03.24+00:00

I have replied on it with additional updates.

Seeya Xi-MSFT 16,446 Reputation points

2021-08-10T09:40:48.907+00:00

Hi @rajesh prajapati ,

Did you have a look at the thread i post before?
If you want to DROP and ADD CONSTRAINT, you need to do something to avoid irreversible mistakes.
Go to database and right click the related tables:

Script Table As -> Create to... Now you got your table with all indexes and other stuff(like CONSTRAINT) which in the table.

Create these tables in your new database.

Copy your data from backup tables to the new.

If your database is not too big and there are not too many tables, you can directly back up the database.

Best regards,
Seeya
Sign in to comment
Erland Sommarskog 101.8K Reputation points MVP

2021-07-28T12:42:43.37+00:00

The cal_invoice_volume has no row for the corresponding value_id =29

The resolution for this type of problem is to double-check your assumptions. There is a fair chance that the computer is right and you are wrong.

DELETE FROM static_data_value where value_id =29
Delete from calc_invoice_volume where invoice_line_item_id IN (29)

Try running the command in reverse order.
Please sign in to rate this answer.
rajesh prajapati 1 Reputation point

2021-07-29T11:35:03.607+00:00

I tried :

error still exists

Msg 547, Level 16, State 0, Line 48
The DELETE statement conflicted with the REFERENCE constraint "FK_calc_invoice_volume_static_data_value". The conflict occurred in database "TRMTracker_LADWP_UAT", table "dbo.calc_invoice_volume", column 'invoice_line_item_id'.
The statement has been terminated.

Completion time: 2021-07-29T17:18:54.0936326+05:45

Tom Phillips 17,716 Reputation points

2021-07-29T17:36:36.597+00:00

Those are MORE FKs to other tables you need to delete.

You need to follow the entire chain to the bottom and delete from the bottom up.

rajesh prajapati 1 Reputation point

2021-08-02T12:24:00.6+00:00

Upon Checking, the chain has FK relationship with other tables' columns. It means this table has FK dataset compared with other related tables.

Any more insights from your side? what could have gone wrong ?
Sign in to comment
Erland Sommarskog 101.8K Reputation points MVP

2021-07-29T11:43:01.577+00:00

No, you don't need to remove the FK.

Just to get this out of the way, run DBCC CHECKDB. It could be a case of corruption.

It could also be that there is a cascading self-referencing FK on static_data_value, and this in its turn lead to the FK-violation. Yet a possibility is a trigger.
Please sign in to rate this answer.
rajesh prajapati 1 Reputation point

2021-08-10T08:19:01.19+00:00

Hi ErlandSommarskog,

Thank you for your reply. I run DBCC CHECKDB to check for DB corruption but found 0 allocation errors and 0 consistency errors in database. Also, there is neither a cascading self-referencing FK on static_data_value nor trigger enabled. Is this similar scenario has happened to anyone in previous thread? Just surprised as this thread still in search of solution.

Erland Sommarskog 101.8K Reputation points MVP

2021-08-10T21:00:37.03+00:00

The reason that this thread is still open is that you have not shared enough information. It may help if you script the tables, including constraints so that we can review them. In the meanwhile, you can review this script and note that we can delete the row with value_id with the constraint. If you uncomment the commented row, you will get an error - since that case there is an FK reference.

CREATE TABLE static_data_value (value_id int NOT NULL, data nvarchar(23) NOT NULL, CONSTRAINT pk_static PRIMARY KEY (value_id) ) INSERT static_data_value (value_id, data) VALUES(29, 'Data 29'), (20, 'Data 20') go CREATE TABLE calc_invoice_volume(invoice_line_item_id int NOT NULL, value_id int NULL, data nvarchar(23) NOT NULL, CONSTRAINT pk_calc PRIMARY KEY (invoice_line_item_id), CONSTRAINT fk_calc_data FOREIGN KEY (value_id) REFERENCES static_data_value(value_id) ON DELETE NO ACTION ) go INSERT calc_invoice_volume(invoice_line_item_id, value_id, data) VALUES(1, 20, 'Data 1-20') go DELETE static_data_value WHERE value_id = 29 go --DELETE static_data_value WHERE value_id = 20 go DROP TABLE calc_invoice_volume, static_data_value

rajesh prajapati 1 Reputation point

2021-08-16T09:12:40.137+00:00

hi ErlandSommarskog,

I tried to upload the script but the script is not uploading..as it is being stuck at 0%. For the full script there is characters limitation.

Erland Sommarskog 101.8K Reputation points MVP

2021-08-16T21:13:05.99+00:00

Don't really know what you mean with "characters limitation". We need the script of two tables, and possibly also some INSERT statements with sample data. Not a script for the full database.
Sign in to comment