CODEWITHSUNDEEP
sql-server-2005t-sql
Yishai
Yishai

Reputation: 91911

How do you drop a default value from a column in a table?

How do you alter a column to remove the default value?

The column was created with:

 ALTER table sometable Add somecolumn nchar(1) NOT NULL DEFAULT 'N'

And then altered with:

 alter table sometable alter column somecolumn nchar(1) null

That allows nulls, but the default value remains. How can you remove it?

Upvotes: 81

Views: 125122

Answers (7)

Yishai
Yishai

Reputation: 91911

This is what I came up with (before seeing Josh W. answer, well actually I saw it but skimmed it so fast I misunderstood it):

declare @name nvarchar(100)
select @name = [name] from sys.objects where type = 'D' and parent_object_id = object_id('sometable')

if (@name is not null)
  begin
     exec ('alter table [sometable] drop constraint [' + @name +']')
  end

UPDATE: If the table has default constraints on different columns,the second line should be changed as suggested in MirrorBoy comment below

select @name = o.name from sys.objects o   
join sys.columns col on o.object_id = col.default_object_id where type = 'D' and
parent_object_id = object_id('sometable') and col.name = 'someColumn'

The advantage I have here is that I know that there is only one such constraint on the whole table. If there had been two, well I guess that is why you are supposed to name them ;).

(The issues is that that this is a modification made to 10 different customer databases, so there isn't one consistent name to put in a script)

Upvotes: 17

Mike Godin
Mike Godin

Reputation: 3947

If you are using SQL Server Management Studio this is pretty easy.

If there are several constraints associated with the table and you don't want to delete them all, right-click on the constraint and select "Script Constraint as -> CREATE to -> New Query Editor Window". This will show you the code that created the constraint including the column name and default value.

Then just right-click on the constraint you want to delete and select Delete.

Upvotes: 1

Tekin
Tekin

Reputation: 612

I also had the same problem, and altered the table and added Default value the same way as you did. But non of the above solutions solved my problem because despite several refreshes the default value was not listed in the constraint list however it was functioning while inserting into table.

I finally solved it by right clicking the table name in SQL Management Studio and selecting Design. Then I deleted the default values there in the column properties.

Upvotes: 0

Dan Rigby
Dan Rigby

Reputation: 17883

Its a default constraint, you need to perform a:

ALTER TABLE {TableName} 
DROP CONSTRAINT ConstraintName

If you didn't specify a name when you created the constraint, then SQL Server created one for you. You can use SQL Server Management Studio to find the constraint name by browsing to the table, opening its tree node, then opening the Constraints node.

If I remember correctly, the constraint will be named something along the lines of DF_SomeStuff_ColumnName.

EDIT: Josh W.'s answer contains a link to a SO question that shows you how to find the auto generated constraint name using SQL instead of using the Management Studio interface.

Upvotes: 81

Mise
Mise

Reputation: 3567

If you not know of constraints name 

DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__')
AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                        WHERE NAME = N'__ColumnName__'
                        AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

https://stackoverflow.com/a/13715343/2547164

Upvotes: 23

Daniyal Awan
Daniyal Awan

Reputation: 107

alter table <tablename> drop constraint <constraintname>

if you don't know the name of the constraint you can use sql server manangement studio to check the constraint name .... hope this helps.

Upvotes: -2

Roman
Roman

Reputation: 61

select name from sys.default_constraints where name like '%first_3_chars_of_field_name%'

Find your constraint and use DROP CONSTRAINT to drop it. Or run a cursor/while loop to drop all similar defaults in the database.

Upvotes: 6

Related Questions