My SQL is not working!!! My Hair is gone is the topic - FK creation not being called and more
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'
ALTERPROCEDURE [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
ALTERTABLE dbo.CategoryDROPCONSTRAINT Category_Category_FK1
PRINT'Dropped FK'
SETIDENTITY_INSERT
INSERTINTO dbo.Category
(CategoryId, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 1, 1, CategoryName,'', 1, 1,'', 1,GETDATE(), 1FROM CategoriesStagingWHERE CategoryName='All'
PRINT'Inserted All Record'
INSERTINTO dbo.Category
(CategoryID, ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID)
SELECT 2, 1, CategoryName,'', 1, 1,'', 1,GETDATE(), 1FROM CategoriesStagingWHERE CategoryName='Store'
PRINT'Inserted Store Record'
SETIDENTITY_INSERT CategoryOFF
/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/
WHILE(@@ROWCOUNT<> 0)
BEGIN
INSERTINTO 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 cINNERJOIN CategoriesStaging sON c.[Name]= s.ParentCategoryName
WHERENOTEXISTS(SELECT 1FROM Category cWHERE s.[CategoryName]= c.[Name])
END
PRINT'Inserted Rest of Category Records'
PRINT're-create FK Call'
ALTERTABLE dbo.Category
ADDCONSTRAINT Category_Category_FK1FOREIGNKEY
(
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:
