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?

You are certainly going to `ALTER` your table, since it's the only way to modifiy its structure. — Quentin Pradet, Mar 07 '12 at 14:41
For which column? You have two columns with defaults. The approach will be the same, just want to make sure explicit examples are treating the column you're talking about. — Aaron Bertrand, Mar 07 '12 at 14:53

Aaron Bertrand · Answer 1 · 2012-03-07T14:47:17.520

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.

score 5 · Accepted Answer · answered Mar 07 '12 at 15:12

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

Thanks. This is exactly what I was searching for. Thanks for the careful explanation. — Petar Petkov, Mar 07 '12 at 15:19
Clearly, I've spent far too much time fixing unnamed defaults. — Philip Kelley, Mar 07 '12 at 15:22
See also http://stackoverflow.com/questions/1397440/what-is-the-purpose-of-constraint-naming/1397493#1397493 — Philip Kelley, Mar 07 '12 at 15:23

Călin Darie · Answer 3 · 2012-03-07T15:37:48.760

0

You can get the name of the default constraint using a query on sys.default_constraints, then drop it by altering your table:

declare @constraintname varchar(128);
select @constraintname = d.name
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = 'Persons' AND c.name = 'P_Id';

declare @sql nvarchar(256);
set @sql = 'ALTER TABLE Persons DROP CONSTRAINT ' + @constraintName;
EXEC sp_executesql @sql;

edited Mar 07 '12 at 15:37

answered Mar 07 '12 at 14:42

Călin Darie

5,937
1
18
13

This syntax doesn't work either. You can't build the command while calling `sp_executesql`, you need to build the command first. Did you try it? Also, `varchar(max)`, really? – Aaron Bertrand Mar 07 '12 at 14:57
Edited it. The 128 size is a hint taken from another SO question http://stackoverflow.com/questions/5808332/sql-server-maximum-character-length-of-object-names – Călin Darie Mar 07 '12 at 15:22
Still won't work. Strongly recommend trying your code before posting. `sp_executesql` requires an `nvarchar` command. – Aaron Bertrand Mar 07 '12 at 15:23

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

3 Answers3