Multiple Errors with SQL Statement dealing with constraints

Question

Multiple Errors with SQL Statement dealing with constraints

dba321

Hall of Fame

Points: 3917
More actions
August 1, 2006 at 3:20 pm

#366956

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

Also, I notice only one of the two of the first inserts ever works, sometimes one vs. the other, it switches every so often on tis own.

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

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

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?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Lance Sanders SSC Eights! Points: 903 More actions · Answer 2

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

dba321 Hall of Fame Points: 3917 More actions · Answer 3

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

WHILE (@@ROWCOUNT <> 0)

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

dba321 Hall of Fame Points: 3917 More actions · Answer 4

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

dba321 Hall of Fame Points: 3917 More actions · Answer 5

schema info

http://www.webfound.net/category.bmp

http://www.webfound.net/category.sql

http://www.webfound.net/categoriesstaging.sql

http://www.webfound.net/stagingdata.txt

At this point, I need just to find a way to get the WHILE loop to work again. It was working in the past but I think due to some changes we made such as enabling IDENTITY inserts or something else, it no longer holds a value so the while loop is never initiated. It needs to loop through the CategoriesStaging table until the end then stop.

dba321 Hall of Fame Points: 3917 More actions · Answer 6

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

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 7

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

John Beggs SSCommitted Points: 1921 More actions · Answer 8

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