Foreign Key constraint error when moving category to top

[jmestep]

I had to get data into a site on a shared hoster and couldn't get the dataport utility to work, so I've had to do it another way and it has caused a problem I can't get past. This is the second time I've tried. Here is what I've done:
Imported the data from an Able 5.5 store into a test db on my dev server. Just categories and products and options. It all made it there with keeping the numbering the same, which is important to us. The categories were scrambled but we plan on moving them where they should be. From there,
I moved the data in 8 tables to the real database with this method:

1. Took all constraints off and cleaned out the tables to make sure they didn't have any records:
ALTER TABLE xpaintb_store.ac_CatalogNodes NOCHECK CONSTRAINT ALL
Delete from xpaintb_store.ac_CatalogNodes

ALTER TABLE xpaintb_store.ac_Categories NOCHECK CONSTRAINT ALL
Delete from xpaintb_store.ac_Categories

ALTER TABLE xpaintb_store.ac_CategoryParents NOCHECK CONSTRAINT ALL
Delete from xpaintb_store.ac_CategoryParents


ALTER TABLE xpaintb_store.ac_OptionChoices NOCHECK CONSTRAINT ALL
Delete from xpaintb_store.ac_OptionChoices

ALTER TABLE xpaintb_store.ac_Options NOCHECK CONSTRAINT ALL
Delete from xpaintb_store.ac_Options

ALTER TABLE xpaintb_store.ac_ProductOptions NOCHECK CONSTRAINT ALL
Delete from xpaintb_store.ac_ProductOptions

ALTER TABLE xpaintb_store.ac_Products NOCHECK CONSTRAINT ALL
Delete from xpaintb_store.ac_Products

ALTER TABLE xpaintb_store.ac_Manufacturers NOCHECK CONSTRAINT ALL
Delete from xpaintb_store.ac_Manufacturers

2. Modifed these tables, turning off the identity field:
ac_Categories, ac_OptionChoices, ac_Options, ac_Products, ac_Manufacturers

3. Added the identity back in to the 5 tables that had it before
Checked the identity seed on these tables because they had had the data in them from the previous copy routine
DBCC CHECKIDENT ('ac_Categories', NORESEED)
DBCC CHECKIDENT ('ac_OptionChoices', NORESEED)
DBCC CHECKIDENT ('ac_Options', NORESEED)
DBCC CHECKIDENT ('ac_Products', NORESEED)
DBCC CHECKIDENT ('ac_Manufacturers', NORESEED)

Checking identity information: current identity value '4912', current column value '4912'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '12523', current column value '12523'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '3366', current column value '3366'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '23420', current column value '23420'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '186', current column value '186'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator

These matched the numbers when run on the test database

4. Inserted the data from test server back into the 8 tables

5. Ran the DBCC CheckIdent NORESEED for all tables and it came up with the correct numbers for each table.
Checking identity information: current identity value '4909', current column value '4909'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '12523', current column value '12523'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '3366', current column value '3366'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '23419', current column value '23419'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '186', current column value '186'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

6. Checked that the constraints were back on
ALTER TABLE xpaintb_store.ac_CatalogNodes CHECK CONSTRAINT ALL
ALTER TABLE xpaintb_store.ac_Categories CHECK CONSTRAINT ALL
ALTER TABLE xpaintb_store.ac_CategoryParents CHECK CONSTRAINT ALL
ALTER TABLE xpaintb_store.ac_OptionChoices CHECK CONSTRAINT ALL
ALTER TABLE xpaintb_store.ac_Options CHECK CONSTRAINT ALL
ALTER TABLE xpaintb_store.ac_ProductOptions CHECK CONSTRAINT ALL
ALTER TABLE xpaintb_store.ac_Products CHECK CONSTRAINT ALL
ALTER TABLE xpaintb_store.ac_Manufacturers CHECK CONSTRAINT ALL

7. Categories and products are back in the way they were, I can add products, add categories, move products, delete products and categories and do everything EXCEPT move a category from a sub level to a top level. When I try that, I get this error:
The INSERT statement conflicted with the FOREIGN KEY constraint "ac_Categories_ac_CatalogNodes_FK1". The conflict occurred in database "xpaintb_directpb", table "xpaintb_store.ac_Categories", column 'CategoryId'.

I can try the same move in the test store and it works OK.
I understand that it is saying it's trying to add something to the ac_CatalogNodes table where there isn't a categoryID in the category table, but I don't understand why.

I did a join on the ac_CatalogNodes and ac_Categories table and found 78 records that are in ac_CatalogNodes that don't have a corresponding CategoryID in the ac_Categories table.

Any suggestions on what to do? I tried deleting those 78 records from teh ac_CatalogNodes table and the whole category structure in the store disappeared.

Thanks

[bobr2k]

Judy, you've probably already seen this post: viewtopic.php?f=42&t=7141

I wonder if the problem you're having is related to deleting a category that was connected to a cart or wishlist. I'm no expert on this database "stuff".

[jmestep]

Bob, thanks for the reply, but I don't think that was it. The database is new with nothing ever having been added to the cart.

[jmestep]

Well, I finally gave up with it and tried pushing the test database to the server into a new database with the database publishing wizard- the hoster can't restore a backup from another server. That worked fine, except that I couldn't change the database config string without editing the file directory- I couldn't do it from within the admin, so I'm going to check with the hoster on a permissions issue.

I finally looked at the Able help about moving an installation- I was afraid of the configuration problems I might run into, but it was a breeze compared to Able 5.5.
I should have tried this yesterday, but I'll leave the post up for reference of how not to do it.