Drop Default does not work 2

[seaport]

Here are the sql code:

CREATE TABLE [tblTableToChange] (
	[PersonID] [int] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [varchar] (50) NOT NULL ,
	[LastName] [varchar] (50) NOT NULL ,
	[Inactive] [bit] NOT NULL default (0),
	[StudentCode] [smallint] NULL ,
	[Notes] [varchar] (255)  DEFAULT (''),
	[LastUpdated] [datetime] NOT NULL
) ON [PRIMARY]
GO
alter TABLE [tblTableToChange]
alter column [Inactive] drop default
go

I got the error mesesge for dropping the default:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'default'.

I do not know what i did wrong. My code is exactly same from the MSDN:

http://msdn2.microsoft.com/en-us/library/ms174123.aspx

Seaport

 

[SQLDenis]

first check for the default

select column_name,column_default,* from information_schema.columns where table_name ='tblTableToChange'

find the name

select object_name(constid) from sysconstraints
where object_name(id) ='tblTableToChange'
and object_name(constid) like 'DF__tblTableT__Inact%'

now drop it

alter TABLE [tblTableToChange]
DROP CONSTRAINT DF__tblTableT__Inact__2057CCD0
go

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLBill]

This is from the BOL, use Index tab, enter DROP DEFAULT.

B. Drop a default that has been bound to a column
This example unbinds the default associated with the phone column of the authors table and then drops the default named phonedflt.

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'phonedflt'
AND type = 'D')
BEGIN
EXEC sp_unbindefault 'authors.phone'
DROP DEFAULT phonedflt
END
GO

-SQLBill

Posting advice: FAQ481-4875

 

[ESquared]

I think binding and unbinding defaults applies only to those which are defined separate from a table, as stand-alone objects, if you will. It's a holdover from SQL Server 6.5 or 7.0, I believe.

When adding defaults using the new SQL 2000 syntax, no binding or unbinding is necessary. You just need to know the name of the default (because you can have many defaults in one table and the server needs to know which one you're modifying).

That's why it's always good practice to adopt a naming convention and then always specify names for all constraints you ever use.

--Do not do:
CREATE TABLE Blah (
   BlahCount int DEFAULT (0)
)

--DO do:
CREATE TABLE Blah (
   BlahCount int CONSTRAINT DF_Blah_BlahCount DEFAULT (0)
-- or BlahCount int CONSTRAINT DF_Blah_ZeroStart DEFAULT (0)
)

--Of course there's the ALTER TABLE ADD CONSTRAINT syntax, too, if you create the default other than at table creation time.)

Because now you don't have to look up the default name in order to do anything with it. This can also be important when using a script against multiple databases and expecting them to be the same. If you don't specify the constraint name, they'll get different names in each database, and then your future scripts to work with this object will fail against other servers.

There is debate whether these things should be named Table_Column or Table_Description, but I'll leave that up to you.

 

[seaport]

ESquared,

Thanks for your advice. I will use it as a guideline. But I feel it will be hard to enforce the rule. Some people just like to use Enterprise Manager to change the table design.

Denis,

By using the sysconstraints table, I can somewhat make changing a column automatically. I understand that it is hard to figure out what kind of contraint from the Status field in sysconstraints table. However, if I am going to change a column, I can just drop all constrants for that column and then re-create them.

Seaport

 

[seaport]

By the way,

What do you think about the code

alter TABLE [tblTableToChange]
alter column [Inactive] drop default
go

which is used in the MSDN help
(

http://msdn2.microsoft.com/en-us/library/ms174123.aspx).

Is that a mistake?

Seaport

 

[ESquared]

The page you referenced is for SQL Server 2005. I have no reason to disbelieve the syntax.

Checking Books Online for SQL Server 2000, the only valid syntax seems to be:

ALTER TABLE [i]table[/i]
ALTER COLUMN [i]column_name[/i]
DROP [CONSTRAINT] [i]constraint_name[/i]

In 2000, one must specify name of the default constraint rather than just the word "default.