The delete statement conflicted with the reference constraint ошибка - Ремонт и установка крупной бытовой техники

I tried to truncate a table with foreign keys and got the message:

«Cannot truncate table because it is being referenced by a FOREIGN KEY constraint«.

I read a lot of literature about the problem and thought that I found the solution by using delete

DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

But I still got an error message:

«The DELETE statement conflicted with the REFERENCE constraint«.

When I try to delete with Microsoft Management Studio and execute the previous query

DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

it doesn’t give an error and works properly. I want to delete all information from a table and add new into it, but I don’t want to drop and create foreign keys.

Rob♦

27.3k16 gold badges82 silver badges97 bronze badges

asked Sep 23, 2010 at 7:42

The error means that you have data in other tables that references the data you are trying to delete.

You would need to either drop and recreate the constraints or delete the data that the Foreign Key references.

Suppose you have the following tables

dbo.Students
(
StudentId
StudentName
StudentTypeId
)


dbo.StudentTypes
(
StudentTypeId
StudentType
)

Suppose a Foreign Key constraint exists between the StudentTypeId column in StudentTypes and the StudentTypeId column in Students

If you try to delete all the data in StudentTypes an error will occur as the StudentTypeId column in Students reference the data in the StudentTypes table.

EDIT:

DELETE and TRUNCATE essentially do the same thing. The only difference is that TRUNCATE does not save the changes in to the Log file. Also you can’t use a WHERE clause with TRUNCATE

AS to why you can run this in SSMS but not via your Application. I really can’t see this happening. The FK constraint would still throw an error regardless of where the transaction originated from.

answered Sep 23, 2010 at 7:50

codingbadgercodingbadger

42.4k13 gold badges94 silver badges109 bronze badges

Have you considered applying ON DELETE CASCADE where relevant?

answered Sep 23, 2010 at 9:56

annakataannakata

74.2k17 gold badges113 silver badges180 bronze badges

You are trying to delete a row that is referenced by another row (possibly in another table).

You need to delete that row first (or at least re-set its foreign key to something else), otherwise you’d end up with a row that references a non-existing row. The database forbids that.

answered Sep 23, 2010 at 7:46

Konrad RudolphKonrad Rudolph

527k130 gold badges931 silver badges1208 bronze badges

To DELETE, without changing the references, you should first delete or otherwise alter (in a manner suitable for your purposes) all relevant rows in other tables.

To TRUNCATE you must remove the references. TRUNCATE is a DDL statement (comparable to CREATE and DROP) not a DML statement (like INSERT and DELETE) and doesn’t cause triggers, whether explicit or those associated with references and other constraints, to be fired. Because of this, the database could be put into an inconsistent state if TRUNCATE was allowed on tables with references. This was a rule when TRUNCATE was an extension to the standard used by some systems, and is mandated by the the standard, now that it has been added.

answered Sep 23, 2010 at 9:51

Jon HannaJon Hanna

110k10 gold badges145 silver badges250 bronze badges

In SQL server
go to the database diagram and choose relation properties
go to insert and update Specification column
make the delete rule set to null

answered Dec 7, 2022 at 23:43

Источник

Remove From My Forums

Question
I am following the sample here:

http://csharp.net-informations.com/dataadapter/deletecommand-sqlserver.htm

I get a message that reads ‘The DELETE statement conflicted with the REFERENCE constraint “FK_Orders_Details_Products”’
In the Orders table, I can see that each order has an OrderID and in the Products table I see ProductID.

So, I have a couple questions:

#1)
How can I see the relationships between the tables in SQL Server?
I’ve seen it before and now I forget how to get back there.

#2)
How can I see the relationships on the tables themselves?
I thought I could see it in ‘Design’ view, but I’m not seeing it anywhere.

#3)
How can I ensure that all references to a Delete query are actually deleted?
Basically, how can I change the code to make this delete work?

Answers

HI !

You need to find on which table this FK constraint has been defined. Look at
sysconstraints table.

Please let me know if you still need assistance.

Thanks, Hasham
- Marked as answer by
  
  Monday, October 24, 2011 6:03 PM
Also try to look at View object Dependencies.

thanks, Hasham
- Marked as answer by
  ryguy72
  Monday, October 24, 2011 6:03 PM

Источник

I’m trying to delete all users but getting the error:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_M02ArticlePersons_M06Persons". The conflict occurred in database "workdemo.no", table "dbo.M02ArticlePersons", column 'M06PersonId'.
The statement has been terminated.

The query:

DELETE FROM [workdemo.no].[dbo].[M06Persons] 
WHERE ID > '13'
GO

Seems I need to use on delete cascade; but I’m stuck.

asked Apr 4, 2013 at 11:50

You don’t need to use the on delete cascade. Somebody (the schema design author) had made sure you cannot delete a person that is still referenced by an article. It succeeded, you were just trying to do this and was blocked, kudos to the designer.

Now go and talk with that somebody that designed the schema and knows the constraints and ask him how to properly delete the records you’re trying to delete, in the correct order and taking the proper precautions to keep the database consistent.

answered Apr 4, 2013 at 12:02

Remus RusanuRemus Rusanu

51.5k3 gold badges93 silver badges171 bronze badges

You have two real choices here, you can disable constraints on the table. This usually not a great idea as you can end up with a bad data condition if you’re messing with data that relates to other tables, but not know the full extent of your schema and it may suit your purposes:

ALTER TABLE [workdemo.no].[dbo].[M06Persons] NOCHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons]

Remember to turn the constraint back on after the delete with

ALTER TABLE [workdemo.no].[dbo].[M06Persons] WITH CHECK CHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons]

The second choice would be to drop and re-add the constraint with the ON DELETE CASCADE option using:

ALTER TABLE [workdemo.no].[dbo].[M06Persons] DROP CONSTRAINT [FK_M02ArticlePersons_M06Persons]

ALTER TABLE [workdemo.no].[dbo].[M06Persons] WITH NOCHECK ADD CONSTRAINT [FK_M02ArticlePersons_M06Persons] FOREIGN KEY(M06PersonId)
REFERENCES <parent table here> (<parent column here>)
ON DELETE CASCADE

Based on your FK name it looks like your parent table is M02ArticlePersons and the parent column is M06Persons.

If you did not author this schema please try to consider why the constraints may be present, and understand that violating them in this manner may have unintended side effects.

answered Apr 4, 2013 at 12:10

dbo.M02ArticlePersons table of column M06PersonId is reffered in another table.
So before delete statement, disable this relationships and try again

below is for disbling the foreign key

 ALTER TABLE dbo.M02ArticlePersons NOCHECK CONSTRAINT FK_M02ArticlePersons_M06Persons

DELETE FROM [workdemo.no].[dbo].[M06Persons] 
  WHERE ID > '13'
GO

and this is to enable it

ALTER TABLE dbo.M02ArticlePersons CHECK CONSTRAINT FK_M02ArticlePersons_M06Persons

Hope this will work

answered Apr 4, 2013 at 12:04

There is another manual option too:

You can go to the child table and delete the child rows referenced by the parent key. Then you can delete the parent row. This is essentially what the cascade delete does. This way, you do not have to drop/recreate/alter your constraints.

answered Apr 4, 2013 at 14:49

StanleyJohnsStanleyJohns

5,9422 gold badges21 silver badges44 bronze badges

This little code will help for any table that you want to delete records from. It takes care of referential integrity as well …

Below code will generate DELETE statements .. Just specify the schema.table_Name

Declare @sql1 varchar(max)
      , @ptn1 varchar(200)
      , @ctn1 varchar(200)
      , @ptn2 varchar(200)
      , @ctn2 varchar(200)
--
SET @ptn1 = ''
--
SET @ctn1 = ''
--
SET @ptn2 = ''
--
SET @ctn2 = ''
--
SELECT @sql1 = case when (@ptn1 <> OBJECT_NAME (f.referenced_object_id)) then
                         COALESCE( @sql1 + char(10), '') + 'DELETE' + char(10) + ' ' + OBJECT_NAME (f.referenced_object_id) + ' FROM ' + OBJECT_NAME(f.parent_object_id) + ', '+OBJECT_NAME (f.referenced_object_id) + char(10) +' WHERE ' + OBJECT_NAME(f.parent_object_id) + '.' + COL_NAME(fc.parent_object_id, fc.parent_column_id) +'='+OBJECT_NAME (f.referenced_object_id)+'.'+COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
                    else
                         @sql1 + ' AND ' + OBJECT_NAME(f.parent_object_id) + '.' + COL_NAME(fc.parent_object_id, fc.parent_column_id) +'='+OBJECT_NAME (f.referenced_object_id)+'.'+COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
                    end + char(10)
     , @ptn1 = OBJECT_NAME (f.referenced_object_id)
     , @ptn2  = object_name(f.parent_object_id)
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('dbo.M06Persons'); -- CHANGE here schema.table_name
--
print  '--Table Depended on ' + @ptn2 + char(10) + @sql1

answered Apr 4, 2013 at 19:56

Kin ShahKin Shah

61.8k6 gold badges118 silver badges235 bronze badges

Источник

Remove From My Forums

Question
I have a procedure trying to delete data but it sometimes fails due to a foreign key constraint conflict.

However, if it is run several times it successfully executes without any change in data or constraint.
```
The DELETE statement conflicted with the REFERENCE constraint "FK_FOO_BAR".
The conflict occurred in database "D", table "dbo.TAble", column 'ColumnA'.
```
How is it getting the error randomly and it gets resolved without making any change at all?

I’m using SQL Server 2008

Answers

Hi Fred,

the failure of a DML statement due to FK violation error is not something that will manifest intermittently without an explanation. someone other than you/or some other process, is deleting the parent row during the several attempts that you make to delete
the data and hence your delete statement eventually succeeds. Any constraint conflict error is sql server should be treated like other TSQL code errors. The constraint conflict error is preventing you from making changes that negatively affects the quality
of data — in this case, its prevent you from creating orphaned rows in the child table. Please take a look at which process/user is deleting rows from the parent table.

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com
- Proposed as answer by
  
  Wednesday, May 29, 2013 8:12 AM
- Marked as answer by
  Fanny Liu
  Wednesday, June 5, 2013 8:32 AM
First, try deleting all data from dependent tables before you can delete the main record.

Another option for you is to change your foreign key constraints so that default action on delete is to propagate. If you enable this — all dependent records would be deleted automatically once you delete the main record.

This second option is something I wouldn’t really recommend, though there are probably cases where it’s useful.
- Proposed as answer by
  Ed Price — MSFTMicrosoft employee
  Wednesday, May 29, 2013 8:12 AM
- Marked as answer by
  Fanny Liu
  Wednesday, June 5, 2013 8:32 AM
Hello,

Please , could you provide the code of your stored procedure and the structure of the table from which you want to delete rows ?

Maybe there a simple explanation : you are deleting rows from a child table and afterwards you are trying to delete rows from the parent table ( «linked» to the child table by the constraint FK_FOO_BAR ) but the delete of rows from the child table
is not ended really or has failed.

Have a nice day

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
- Proposed as answer by
  Ed Price — MSFTMicrosoft employee
  Wednesday, May 29, 2013 8:12 AM
- Marked as answer by
  Fanny Liu
  Wednesday, June 5, 2013 8:32 AM

Источник

Deleting rows from a table is a pretty simple task right? Not always. Foreign keys, while providing a ton of benefits, do make deletes a bit more complicated.

-- Demo setup
CREATE TABLE Authors (
	Author_Id INT NOT NULL IDENTITY (1,1) 
			CONSTRAINT pk_Author PRIMARY KEY
	,Name varchar(100)
	);

CREATE TABLE Books (
	Book_Id INT NOT NULL IDENTITY (1,1) 
			CONSTRAINT pk_Books PRIMARY KEY
	,Author_Id INT 
			CONSTRAINT fk_Books_Author_Id FOREIGN KEY 
					REFERENCES Authors(Author_Id)
	,Name varchar(100)
	);

INSERT INTO Authors VALUES
	('Itzik Ben-Gan')
	,('Grant Fritchey')
	,('Kellyn Pot’Vin-Gorman');

INSERT INTO Books VALUES
	(1,'T-SQL Fundamentals')
	,(1,'T-SQL Window Functions: For data analysis and beyond (Developer Reference)')
	,(1,'Exam Ref 70-761 Querying Data with Transact-SQL')
	,(2,'SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance')
	,(2,'SQL Server Query Performance Tuning')
	,(2,'SQL Server Execution Plans')
	,(3,'Crushing the IT Gender Bias: Thriving as a Woman in Technology');

Now let’s say that I need to delete all of Grant’s books for whatever reason. He’s tired of SQL Server, want’s his name removed from it completely, and decided to take up butterfly farming or something.

DELETE FROM Authors WHERE name = 'Grant Fritchey';
DELETE FROM Books WHERE name IN ('SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance'
	,'SQL Server Query Performance Tuning'
	,'SQL Server Execution Plans');

Msg 547, Level 16, State 0, Line 34
The DELETE statement conflicted with the REFERENCE constraint “fk_Books_Author_Id”. The conflict occurred in database “WorkplaceInventory”, table “dbo.Books”, column ‘Author_Id’.
The statement has been terminated.

(3 rows affected)

Well, at least the rows from Books were deleted, and I could run it again and the row from Authors would also be deleted. Which I might do if this was a one-off and this simple (i.e. only two tables involved and no other code). But, if you are writing this code to be re-usable (in case Grant keeps changing his mind), then you really need this to work without flaws. In fact the inspiration for this particular post was a piece of code to be used for rolling back a production install that involves inserting some rows into a few tables. One of our requirements is that they run without flaw, for obvious reasons.

The first option (and not one I’d chose) is to either disable or drop the foreign key.

-- Disable
ALTER TABLE Books NOCHECK CONSTRAINT fk_Books_Author_Id;
-- or Delete
ALTER TABLE Books DROP CONSTRAINT fk_Books_Author_Id;

Then afterwards we add them back.

-- Enable
ALTER TABLE Books CHECK CONSTRAINT fk_Books_Author_Id;
-- or Add
ALTER TABLE Books ADD CONSTRAINT fk_Books_Author_Id FOREIGN KEY (Author_Id)
	REFERENCES Author(Author_Id);

The down side here is that when you add the constraint back (or enable it) it has to validate the data and for a large table that can take some time, and if you made any mistakes with your delete, i.e. leaving any child rows behind, it’s going to fail.

My preference here is to just flip the order of your deletes. Which sounds really simple, but you’d be surprised how often people don’t think of it. Basically we want to make sure we delete all of the children first, then the parent.

-- Always delete the children first.
DELETE FROM Books WHERE name IN ('SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance'
	,'SQL Server Query Performance Tuning'
	,'SQL Server Execution Plans');
DELETE FROM Authors WHERE name = 'Grant Fritchey';

Again, this is a pretty simple example, but try thinking about it when you have a whole series of foreign keys. Say half a dozen, or even more, of them. All of a sudden, if you aren’t familiar with the structure of your database it could take some time to get that order right. Make sure you do though, as I said above, we want these to run without fail.

For those of you who are thinking, what about cascading deletes? Turn those on on your foreign key and then you can just delete the parent and all of the children go automatically. And that is true, but I’m very leery of cascading deletes. On one notable occasion we had someone delete one row from a table. Only when the delete finished several minutes later did they realize they’d gotten rid of over a third of the database.

Источник

Question

Answers

Question

Answers

Возможно, вам также будет интересно: