August 1, 2006 at 3:20 pm
I am about to pull my hair out:
Latest Errors:
(1 row(s) affected)
Checking identity information: current identity value '2', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 3728, Level 16, State 1, Procedure Categories_I, Line 24
'Category_Category_FK1' is not a constraint.
Msg 3727, Level 16, State 0, Procedure Categories_I, Line 24
Could not drop constraint. See previous errors.
If I run just the alter statement alone to create the constraint, then try running the EXEC Categories, I get different errors:
(0 row(s) affected)
Checking identity information: current identity value '0', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Dropped FK
(0 row(s) affected)
Inserted All Record
(1 row(s) affected)
Inserted Store Record
Inserted Rest of Category Records
re-create FK Call
Msg 547, Level 16, State 0, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'Chefs2', table 'Category', column 'CategoryID'.
ALTER PROCEDURE [domain\mylogin].[Categories_I]
AS
BEGIN
/* delete contents from Category table and reseed
Cannot use truncate on a table which contains constraints therefore
use DBCC to reset the seed and DELETE to clear the records
*/
DELETE dbo.Category
DBCC CHECKIDENT ('Category', RESEED, 0)
-- Now, insert the initial 'All' Root Record
ALTER TABLE dbo.Category DROP CONSTRAINT Category_Category_FK1
PRINT 'Dropped FK'
SET IDENTITY_INSERT
INSERT INTO dbo.Category
(CategoryId, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 1, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'
PRINT 'Inserted All Record'
INSERT INTO dbo.Category
(CategoryID, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 2, 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'
PRINT 'Inserted Store Record'
SET IDENTITY_INSERT Category OFF
/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/
WHILE (@@ROWCOUNT <> 0)
BEGIN
INSERT INTO dbo.Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
END
PRINT 'Inserted Rest of Category Records'
PRINT 're-create FK Call'
ALTER TABLE dbo.Category
ADD CONSTRAINT Category_Category_FK1 FOREIGN KEY
(
ParentCategoryID
) REFERENCES Category (
CategoryID
)
PRINT 'create FK2'
END
August 1, 2006 at 3:45 pm
Can you post your table DDL for both tables as well as some sample data from the CategoriesStaging table.
By the way, did you know that the foreign key constraint that you are creating is referencing the same table? Don't you want the foreign key to reference the CategoriesStaging table?
August 1, 2006 at 4:09 pm
A few comments...
I noticed in the ID/ParentID hierarchy that the first record's ParentID = 1. Is this column nullable? If so, set the first ParentID = null to ignore the constraint.
Also, rather than dropping/adding the key, have you tried disabling/enabling?
ALTER TABLE dbo.Category NOCHECK CONSTRAINT Category_Category_FK1 ALTER TABLE dbo.Category CHECK CONSTRAINT Category_Category_FK1
Or, if you don't want the constraint to check the RI on re-enable:
ALTER TABLE dbo.Category WITH NOCHECK CHECK CONSTRAINT Category_Category_FK1
August 1, 2006 at 11:03 pm
Thanks I like the disabling approach better.
Now, I need to figure a way to loop through the records without using @@ROWCOUNT since this below is no longer working because it's not evaluating Rowcount correctly
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
END
this is not working, I need to use something other than @@ROWCOUNT, that is the culprit
August 1, 2006 at 11:05 pm
>>>>>>By the way, did you know that the foreign key constraint that you are creating is referencing the same table? Don't you want the foreign key to reference the CategoriesStaging table?
No, the categoriesstaging table is just that, a throw away table and has nothing to do with this, it's only used as a temporary hold for the relationships between the categories.
Category table has a PK and FK referencing the same table becuase that's a more efficient way to manage category levels rather than having to create a new table for every single sub-category...I don't need so much normal form here...thus you use one table to manage the Parent and child relationships in the categories and sub-categories.
August 1, 2006 at 11:07 pm
schema info
August 1, 2006 at 11:08 pm
>>I noticed in the ID/ParentID hierarchy that the first record's ParentID = 1. Is this column nullable
I put 1 because I thought that would be better than just a null...anyway, I changed it to null. yes, the column is nullable now, wasn't before.
Again, back to the @@ROWCOUNT...any ideas?
August 2, 2006 at 1:44 am
Change to:
WHILE 1 = 1
BEGIN
...
IF @@ROWCOUNT = 0
BREAK
END
@@ROWCOUNT is like @@ERROR, the 1st time you access it changes to zero, so you may want:
SET @Rows = @@ROWCOUNT
IF @@Rows = 0
BREAK
I have also found that this gives better results:
WHERE NOT EXISTS (SELECT 1 FROM Category WHERE [Name] = s.[CategoryName])
Andy
August 2, 2006 at 6:05 pm
Sorry if I am missing the point, but why do you need to use @@ROWCOUNT anyway?
Looking at your code above, the only assumption that I can come to, is that you are trying to detect for an error. If this is true, then I would suggest adding some real error handling code, that checks for errors after every step so that you can correctly identify when/where problems are occuring.
ie:
declare @err int
INSERT...
SELECT @err = @@ERROR
IF @err <> 0 ...
If you are not trying to catch an error, then I don't see the point in having the @@ROWCOUNT in there. You are doing a set based INSERT, not single rows, therefore you are not stepping through anything that will cause the WHILE loop to repeat anyway.
Good luck!
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
