Help Wir Dropping A Field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Help Wir Dropping A Field

Author

Topic

sca1
Starting Member

9 Posts

Posted - 2008-06-06 : 12:49:26

Can Some one Please Advise me what is wrong with these statments. I am useing Microsoft SQL Server Express

The Following Statement produces an error of "Incorrect syntax near the keyword 'DEFAULT'." but CustomerDataTable is a valid table and Customer_ID is a valid field with a default calue of 0

ALTER TABLE [CustomerDataTable] ALTER COLUMN [CUSTOMER_ID] DROP DEFAULT

This Next Statement produces an error of "The object 'DF__CustomerD__Custo__2003926C' is dependent on column 'Customer_ID'.
Msg 4922, Level 16, State 9, Line 1

ALTER TABLE DROP COLUMN Customer_ID failed because one or more objects access this column."

This Error appears to be because there is a default value set on the field I am tryingg to drop but for the life of me I can not get sql to let me delete this column or default value.

ALTER TABLE CustomerDataTable DROP COLUMN [Customer_ID]

Thanks for any help you can provide I ultimatly just need to drop this one column from this one table not useing the management studio.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-06 : 12:58:04

Drop the default constraint via ALTER TABLE/DROP CONSTRAINT.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sca1
Starting Member

9 Posts

Posted - 2008-06-09 : 08:51:56

The Table Does not have any constrants it was created with a simple create stament Pasted Below.

CREATE TABLE [dbo].[CustomerDataTable](
[CustomerNumber] [varchar](10) NOT NULL DEFAULT (''),
[CustomerType] [varchar](3) NOT NULL DEFAULT (''),
[Route] [varchar](3) NOT NULL DEFAULT (''),
[Sequence] [varchar](6) NOT NULL DEFAULT (''),
[CarrierRouteCode] [varchar](8) NOT NULL DEFAULT (''),
[BillToName] [varchar](30) NOT NULL DEFAULT (''),
[BillToAddr1] [varchar](30) NOT NULL DEFAULT (''),
[BillToAddr2] [varchar](30) NOT NULL DEFAULT (''),
[BillToCity] [varchar](15) NOT NULL DEFAULT (''),
[BillToState] [varchar](2) NOT NULL DEFAULT (''),
[BillToZip] [varchar](10) NOT NULL DEFAULT (''),
[ServName] [varchar](30) NOT NULL DEFAULT (''),
[ServAddr1] [varchar](30) NOT NULL DEFAULT (''),
[ServAddr2] [varchar](30) NOT NULL DEFAULT (''),
[ServCity] [varchar](15) NOT NULL DEFAULT (''),
[ServState] [varchar](2) NOT NULL DEFAULT (''),
[ServZip] [varchar](10) NOT NULL DEFAULT (''),
[DistrictCode] [varchar](3) NOT NULL DEFAULT (''),
[SwisCode] [varchar](6) NOT NULL DEFAULT (''),
[SBL] [varchar](30) NOT NULL DEFAULT (''),
[AcctiveAcct] [int] NOT NULL DEFAULT ((0)),
[HoldFinChgs] [int] NOT NULL DEFAULT ((0)),
[BillingCycle] [varchar](3) NOT NULL DEFAULT (''),
[SummerRes] [int] NOT NULL DEFAULT ((0)),
[ExcludeFromRelevy] [int] NOT NULL DEFAULT ((0)),
[Notes1] [varchar](60) NOT NULL DEFAULT (''),
[Notes2] [varchar](60) NOT NULL DEFAULT (''),
[WaterDist] [varchar](3) NOT NULL DEFAULT (''),
[SewerDist] [varchar](3) NOT NULL DEFAULT (''),
[MiscDist] [varchar](3) NOT NULL DEFAULT (''),
[Phone] [varchar](15) NOT NULL DEFAULT (''),
[UserDefStr1] [varchar](30) NOT NULL DEFAULT (''),
[UserDefStr2] [varchar](30) NOT NULL DEFAULT (''),
[UserDefStr3] [varchar](30) NOT NULL DEFAULT (''),
[UserDefStr4] [varchar](30) NOT NULL DEFAULT (''),
[UserDefDbl1] [float] NOT NULL DEFAULT ((0.00)),
[UserDefDbl2] [float] NOT NULL DEFAULT ((0.00)),
[PostNetCode] [varchar](12) NOT NULL DEFAULT (''),
[SeniorCitizen] [int] NOT NULL DEFAULT ((0)),
[BillingNotes] [varchar](60) NOT NULL DEFAULT (''),
[MultiUnitAcct] [int] NOT NULL DEFAULT ((0)),
[NumOfUnits] [int] NOT NULL DEFAULT (''),
[Customer_ID] [int] NOT NULL DEFAULT ('')
)

Again any help would be appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-09 : 12:38:37

Notice all of those DEFAULTs in your script, that's what I am referring to. Those are all constraints. Because you didn't provide a constraint name in the script, SQL Server had to name them for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sca1
Starting Member

9 Posts

Posted - 2008-06-11 : 13:12:11

Ok So how do I Drop the default constraint? Can you please give me an example useing the customer_ID in the defined table. Further is there something I should add to my create statment that could help with this problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 13:36:10

1. sp_helpconstraint 'CustomerDataTable'

2. CREATE TABLE Table1 (Column1 int NOT NULL, Column2 varchar(50) NOT NULL CONSTRAINT def_Column2 DEFAULT ('SomeValue')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources