In QA I can alter a table with
ALTER TABLE dbo.Incidents ADD NewIncident int NOT NULL default 0
However later on I wish to remove the default on this column. However
upon creation the defualt is given a name like
DF__Incidents__NewIn__05D9AC15
Can I a) Give a name to the default upon creation or b) get the name of
the default for the column so i can then remove it like
DROP CONSTRAINT DF_Incidents_NewIncident
ThanksNever let SQL Server name your constraints, name them yourself:
create table t(c1 int constraint d_t default 1)
go
exec sp_helpconstraint t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rippo" <info@.rippo.co.uk> wrote in message
news:1126094026.545449.59440@.g14g2000cwa.googlegroups.com...
> In QA I can alter a table with
> ALTER TABLE dbo.Incidents ADD NewIncident int NOT NULL default 0
>
> However later on I wish to remove the default on this column. However
> upon creation the defualt is given a name like
> DF__Incidents__NewIn__05D9AC15
> Can I a) Give a name to the default upon creation or b) get the name of
> the default for the column so i can then remove it like
>
> DROP CONSTRAINT DF_Incidents_NewIncident
> Thanks
>|||I prefer to name them as Tibor recommended. See if this helps:
http://www.microsoft.com/communitie...72e7&sloc=en-us
AMB
"Rippo" wrote:
> In QA I can alter a table with
> ALTER TABLE dbo.Incidents ADD NewIncident int NOT NULL default 0
>
> However later on I wish to remove the default on this column. However
> upon creation the defualt is given a name like
> DF__Incidents__NewIn__05D9AC15
> Can I a) Give a name to the default upon creation or b) get the name of
> the default for the column so i can then remove it like
>
> DROP CONSTRAINT DF_Incidents_NewIncident
> Thanks
>
Showing posts with label creation. Show all posts
Showing posts with label creation. Show all posts
Wednesday, March 21, 2012
Saturday, February 25, 2012
My SQL is not working!!! My Hair is gone is the topic - FK creation not being called and more
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
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:
http:
http:
http:
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?
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
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:
http:
http:
http:
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?
Subscribe to:
Posts (Atom)