DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_bvc_ProductXCategory_bvc_Product'

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

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..

truncate would be easier if you didnt have foreign key constraints on the table

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?

truncate doesn't work either...

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.

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.

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'

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'

So I found all the tables that are connected by doing a sp_helpconstraint tname

so what should I do at this point

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)

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

What about the code you wrote above how would it all come together?

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

BRICROWE,

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

<< 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