?
Solved

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_bvc_ProductXCategory_bvc_Product'

Posted on 2005-02-24
21
Medium Priority
?
1,455 Views
Last Modified: 2012-08-13
I'm running into this error and I need a workaround: help is appreciated...

DELETE FROM BVC2004jds.dbo.bvc_ProductXCategory

INSERT INTO BVC2004jds.dbo.bvc_ProductXCategory (ProductID, CategoryID)      
SELECT BVC2004jds.dbo.bvc_Product.ID, 9    
FROM BVC2004jds.dbo.bvc_Product

DELETE FROM BVC2004jds.dbo.bvc_Product

INSERT INTO BVC2004jds.dbo.bvc_Product (ID, ProductTypeID, MetaKeywords, MetaDescription, ImageFileSmall, ImageFileMedium, MetaTitle, ProductName, ShortDescription, LongDescription, ListPrice, SitePrice, SiteCost)
SELECT inv.inv_id, 168, '', '', '', '', '', inv.style, inv.ticket_desc, inv.full_desc, inv_dtl.sugg_price, inv_dtl.current_price, inv_dtl.last_cost
FROM WinRetail.dbo.inv, WinRetail.dbo.inv_dtl
WHERE inv.inv_id=inv_dtl.inv_dtl_id

0
Comment
Question by:ngaymerjones
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 3
  • +2
21 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13395730
I'm guessing that you have referential integrity set up between bvc_ProductXCategory and bvc_Product so that you can't delete the Category as long as there are products with that category.

Try reversing the order of your deletes so that you delete the products first

you might want to try using TRUNCATE TABLE tablename instead of the DELETE ...it's a little more efficient
0
 

Author Comment

by:ngaymerjones
ID: 13395787
I tried that and I got the same error... I think I need to drop the key and add the key back? Got any more advice..
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13395816
truncate would be easier if you didnt have foreign key constraints on the table
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 7

Expert Comment

by:TitoBob
ID: 13395836
TRUNCATE TABLE BVC2004jds.dbo.bvc_ProductXCategory
TRUNCATE TABLE BVC2004jds.dbo.bvc_Product

INSERT INTO BVC2004jds.dbo.bvc_Product (ID, ProductTypeID, MetaKeywords, MetaDescription, ImageFileSmall, ImageFileMedium, MetaTitle, ProductName, ShortDescription, LongDescription, ListPrice, SitePrice, SiteCost)
SELECT inv.inv_id, 168, '', '', '', '', '', inv.style, inv.ticket_desc, inv.full_desc, inv_dtl.sugg_price, inv_dtl.current_price, inv_dtl.last_cost
FROM WinRetail.dbo.inv, WinRetail.dbo.inv_dtl
WHERE inv.inv_id=inv_dtl.inv_dtl_id

INSERT INTO BVC2004jds.dbo.bvc_ProductXCategory (ProductID, CategoryID)      
SELECT BVC2004jds.dbo.bvc_Product.ID, 9    
FROM BVC2004jds.dbo.bvc_Product

This shouldn't cause problems.  If you get an error, can you post it so that we can better determine what is wrong?
0
 

Author Comment

by:ngaymerjones
ID: 13395839
truncate doesn't work either...
0
 

Author Comment

by:ngaymerjones
ID: 13395892
TIToBOB:

Server: Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'BVC2004jds.dbo.bvc_Product' because it is being referenced by a FOREIGN KEY constraint.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13395977
There is probably another table separate from ProductXCategory that is referencing the Product Table.  You can either remove the reference, turn off the referential integrity associated with that reference, or delete the records from each of those tables that are causing the conflict.
0
 

Author Comment

by:ngaymerjones
ID: 13396050
I probably want to try to delete the records from the tables that are causing the conflict. How do I find these, once I do so I would just do a DELETE FROM 'TABLE' WHERE 'TABLE'
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 13396097
This will disable and re-enable the constraints and triggers for the database.


Print 'Disabling all Constraints'
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Print 'Disabling all Triggers'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

DELETE FROM BVC2004jds.dbo.bvc_ProductXCategory

INSERT INTO BVC2004jds.dbo.bvc_ProductXCategory (ProductID, CategoryID)      
SELECT BVC2004jds.dbo.bvc_Product.ID, 9    
FROM BVC2004jds.dbo.bvc_Product

DELETE FROM BVC2004jds.dbo.bvc_Product

INSERT INTO BVC2004jds.dbo.bvc_Product (ID, ProductTypeID, MetaKeywords, MetaDescription, ImageFileSmall, ImageFileMedium, MetaTitle, ProductName, ShortDescription, LongDescription, ListPrice, SitePrice, SiteCost)
SELECT inv.inv_id, 168, '', '', '', '', '', inv.style, inv.ticket_desc, inv.full_desc, inv_dtl.sugg_price, inv_dtl.current_price, inv_dtl.last_cost
FROM WinRetail.dbo.inv, WinRetail.dbo.inv_dtl
WHERE inv.inv_id=inv_dtl.inv_dtl_id

Then to enable

Print 'Enabling all Constraints'
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Print 'Enabling all Triggers'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
0
 

Author Comment

by:ngaymerjones
ID: 13396108
So I found all the tables that are connected by doing a sp_helpconstraint tname

so what should I do at this point
0
 

Author Comment

by:ngaymerjones
ID: 13396188
ptjb: tried that and got this, what does that mean...

Disabling all Constraints
Warning: The table 'inv_promotion' has been created but its maximum row size (16599) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_memorized_reports_detail' has been created but its maximum row size (72060) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_reports_detail' has been created but its maximum row size (72060) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'vend' has been created but its maximum row size (11940) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'ccemp' has been created but its maximum row size (112050) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_delete_header' has been created but its maximum row size (32038) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_memorized_reports_header' has been created but its maximum row size (88061) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_reports_header' has been created but its maximum row size (80059) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'imported_upc' has been created but its maximum row size (40031) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'inv_ticket_batch' has been created but its maximum row size (9094) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'inv_transfer' has been created but its maximum row size (8611) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'inv_grid' has been created but its maximum row size (19784) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'inv_promotion_days_hist' has been created but its maximum row size (8615) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Disabling all Triggers
Warning: The table 'inv_promotion' has been created but its maximum row size (16599) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_memorized_reports_detail' has been created but its maximum row size (72060) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_reports_detail' has been created but its maximum row size (72060) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'vend' has been created but its maximum row size (11940) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'ccemp' has been created but its maximum row size (112050) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_delete_header' has been created but its maximum row size (32038) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_memorized_reports_header' has been created but its maximum row size (88061) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'sys_reports_header' has been created but its maximum row size (80059) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'imported_upc' has been created but its maximum row size (40031) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'inv_ticket_batch' has been created but its maximum row size (9094) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'inv_transfer' has been created but its maximum row size (8611) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'inv_grid' has been created but its maximum row size (19784) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'inv_promotion_days_hist' has been created but its maximum row size (8615) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

(0 row(s) affected)


(10929 row(s) affected)

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_bvc_ProductXCategory_bvc_Product'. The conflict occurred in database 'BVC2004jds', table 'bvc_ProductXCategory', column 'ProductID'.
The statement has been terminated.

(0 row(s) affected)

0
 
LVL 27

Expert Comment

by:ptjcb
ID: 13396397
SQL Server has a maximum number of bytes per row: 8060.

It looks as if you have a number of tables that have rows that exceed 8060. That is a completely different issue.

Using the information from sp_helpconstraint, you could write several alter table statements for each constraint that affects the bvc_productXCategory table.
ALTER TABLE tablename  NOCHECK CONSTRAINT ConstraintName

and then

ALTER TABLE tablename CHECK CONSTRAINT constraintname

0
 

Author Comment

by:ngaymerjones
ID: 13396441
What about the code you wrote above how would it all come together?
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 1000 total points
ID: 13396468
If the tables are transactions or something like that then I doubt you want to delete them so my question is why are you emptying the Product and ProductCategory tables?  Why don't you just append your new records then you don't have to worry about FK conflicts.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 13396476
You would have a series of alter table statements (specific for each constraint on the table)

alter table tablename nocheck
alter table
alter table

Then your original statement

then a series of alter table statements specific for each constraint to re-enable them

alter table tablename check
alter table
alter table


The method that I used will not work on your system because you have a number of tables that exceed maximum capacity specifications for SQL Server. You must disable and the re-enable each constraint that is tied to the table you want to delete. It will be more work, but it will do what you want it to do.

Bon chance
0
 

Author Comment

by:ngaymerjones
ID: 13396503
BRICROWE,

How would I append new records when I don't know what to insert them as?
0
 
LVL 13

Expert Comment

by:KarinLoos
ID: 13401718
<< So I found all the tables that are connected by doing a sp_helpconstraint tname  >>

Well now that you have a list of tables that are connected by foreign constraint   you can do what you wanted:

<<I probably want to try to delete the records from the tables that are causing the conflict. How do I find these, once I do so I would just do a DELETE FROM 'TABLE' WHERE 'TABLE' >>

Alhtough I agree with BriCrow, why are you deleting all the records out of  bvc_ProductXCategory and vbc_Product.  As was mentioned just
append the records you dont have yet..
ie    :
INSERT INTO BVC2004jds.dbo.bvc_ProductXCategory (ProductID, CategoryID)      
SELECT a.ID, 9  
FROM         BVC2004jds.dbo.bvc_Product                 a
LEFT JOIN   BVC2004jds.dbo.bvc_ProductXCategory  b ON (b.ProductID = a.ID
                                                                             AND  b.CategoryID = 9)

and

INSERT INTO BVC2004jds.dbo.bvc_Product (ID, ProductTypeID, MetaKeywords, MetaDescription, ImageFileSmall, ImageFileMedium, MetaTitle,
                                                                ProductName, ShortDescription, LongDescription, ListPrice, SitePrice, SiteCost)
SELECT inv.inv_id, 168, '', '', '', '', '', inv.style, inv.ticket_desc, inv.full_desc, inv_dtl.sugg_price, inv_dtl.current_price, inv_dtl.last_cost
FROM WinRetail.dbo.inv                              inv
JOIN   WinRetail.dbo.inv_dtl                        inv_dtl  ON ( inv.inv_id=inv_dtl.inv_dtl_id )
LEFT JOIN BVC2004jds.dbo.bvc_Product      b          ON ( b.ID  = inv.inv_id  )
WHERE  b.ID IS NULL

where b.ProductID IS NULL


                                                                     



 
0
 
LVL 13

Assisted Solution

by:KarinLoos
KarinLoos earned 1000 total points
ID: 13401729
oops typo:
INSERT INTO BVC2004jds.dbo.bvc_ProductXCategory (ProductID, CategoryID)      
SELECT a.ID, 9  
FROM         BVC2004jds.dbo.bvc_Product                 a
LEFT JOIN   BVC2004jds.dbo.bvc_ProductXCategory  b ON (b.ProductID = a.ID
                                                                             AND  b.CategoryID = 9)
WHERE  b.ProductID IS NULL
-------------------------------------------------
INSERT INTO BVC2004jds.dbo.bvc_Product (ID, ProductTypeID, MetaKeywords, MetaDescription, ImageFileSmall, ImageFileMedium, MetaTitle,
                                                                ProductName, ShortDescription, LongDescription, ListPrice, SitePrice, SiteCost)
SELECT inv.inv_id, 168, '', '', '', '', '', inv.style, inv.ticket_desc, inv.full_desc, inv_dtl.sugg_price, inv_dtl.current_price, inv_dtl.last_cost
FROM WinRetail.dbo.inv                              inv
JOIN   WinRetail.dbo.inv_dtl                        inv_dtl  ON ( inv.inv_id=inv_dtl.inv_dtl_id )
LEFT JOIN BVC2004jds.dbo.bvc_Product      b          ON ( b.ID  = inv.inv_id  )
WHERE  b.ID IS NULL
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question