How to change the default value of a column in SQL Server

Question

Supposedly I have a table called Person defined like this:

CREATE TABLE Persons
(
    P_Id uniqueidentifier Default newsequentialid() NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes'
)

How can I remove the default value for that column using an sql query? Furthermore how can I add it if it is not present to begin with. I know it is possible to add it via altering the table and adding a constraint over the P_Id column but I am not sure if that is the only way. I have come across this article however what is suggested there doesn't seem to really work.

Any ideas?

Solution 1

In SQL Server, defaults are defined as constraints associated with a specific column in a table. All constraints are assigned a name; this is important, because once the constraint is created, if you want to modify it you have to reference it by this name. (And I’ll be watching this question, to see if I’m wrong.)

Based on this sample table:

CREATE TABLE MyTable
 (
   MyTableId  int          not null
  ,SomeData   varchar(50)  not null  default 'Foo'
  ,MoreData   datetime     not null  default CURRENT_TIMESTAMP
 )

Step 1: Determine if a constraint exists on a column. Several ways to do this, all involving system views and/or metadata functions. Here’s a quick one, where ‘MyTable’ and ‘SomeData’ could be set as parameters:

SELECT name
 from sys.default_constraints
 where parent_object_id = object_id('MyTable')
  and parent_column_id = columnproperty(object_id('MyTable'), 'SomeData', 'ColumnId')

Try it, and you’ll see that the name generated is essentially random blather. To determine if a default exists on a column, you could do:

IF exists (select name
            from sys.default_constraints
            where parent_object_id = object_id('MyTable')
             and parent_column_id = columnproperty(object_id('MyTable'), 'SomeData', 'ColumnId'))
    PRINT 'Default found'
ELSE
    PRINT 'NoDefault'

To drop an existing default where you don’t know the name, you’ll have to build dynamic SQL. (that code in the referenced article is wrong, and clearly never tested.) @Călin does it slightly differently than I’d do it, but the idea’s the same:

DECLARE @Command nvarchar(max)

SELECT @Command = 'ALTER TABLE MyTable drop constraint ' + name
 from sys.default_constraints
 where parent_object_id = object_id('MyTable')
  and parent_column_id = columnproperty(object_id('MyTable'), 'SomeData', 'ColumnId')

IF @Command is not null
    EXECUTE sp_executeSQL @Command

(With error checking, parameters for the table and and column being checked, and so on.)

Lastly, you can avoid most of this by naming the defaults when you create the constraint, like so:

CREATE TABLE MyTable
 (
   MyTableId  int          not null
  ,SomeData   varchar(50)  not null
    constraint DF_MyTable__SomeData  default 'Foo'
  ,MoreData   datetime     not null
    constraint DF_MyTable__MoreData  default CURRENT_TIMESTAMP
 )

Or like so:

IF not exists (select name
                from sys.default_constraints
                where parent_object_id = object_id('MyTable')
                 and parent_column_id = columnproperty(object_id('MyTable'), 'SomeData', 'ColumnId'))
    ALTER TABLE MyTable
     add constraint DF_MyTable__SomeData
      default 'Foo' for SomeData

Solution 2

The easiest way to figure out the syntax for a table that already exists is to right-click the table in Object Explorer and choose Script As > Create To > New Window. This will help you generate the script for a similar table should you need to create one.

I don't know of an easy way to do this for ALTER, though. In your case if that default constraint were not on that column, I would say:

ALTER TABLE dbo.Persons ADD CONSTRAINT dfCity DEFAULT ('Sandnes') FOR City;

And this is just because I already know the syntax for adding constraints to a table, since I've had to do it quite a bit. This article is a bit dated, but it might be useful since most of it is still relevant and it is not missing much in terms of new features:

Working with Default Constraints

And of course the ALTER TABLE topic in Books Online:

http://msdn.microsoft.com/en-us/library/ms190273.aspx

If you want to remove the constraint, you first need to find the name and then you can say:

ALTER TABLE dbo.Persons DROP CONSTRAINT constraint_name;

@Calin posted a query that will get the name of the constraint, but I don't think you can pass @constraintname to the ALTER TABLE statement that way.