My SQL is not working!!! My Hair is gone is the topic - FK creation not being called and mor

I have 2 problems:
1) When I run this, etch time I keep getting the error saying that constraint isn't found when I try to drop because my creation of the constraint at the end for some reason isn't creating it or being run
2) The first insert doesn't insert anything, although I get no errors
ALTER PROCEDURE Categories_I
3) I also get this when trying to run just the first 2 insert statements together
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'Category_PK'. Cannot insert duplicate key in object 'Category'

ALTER PROCEDURE [domainname\myaccountname].[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

Other 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, Line 6

'Category_Category_FK1' is not a constraint.

Msg 3727, Level 16, State 0, Line 6

Could not drop constraint. See previous errors.

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'Chefs2', table 'Category', column 'CategoryID'.

Schemas & Data:

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

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

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

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

1) To prevent error generation for deleting constraint which may not exists use simple check like below:

if exists(select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE where table_name = 'table_name' and constraint_name = 'constraint_name')

alter table dbo.table_name drop constraint constraint_name

2) Just make this select with no insert and you would probably see that either there are no rows in the table that meet your criteria or there are no rows in the table at all.

3) This error message tells you that you are trying to insert duplicate; what is unclear about that?