There were some recent changes that were done in regards to column defaults. In this blog entry I want to bring out, what used to happen and what happens now.

I have discussed the case of dropping a default on a column in this post, however, in a case where you are facing problems dealing with column specific constraints(especially dropping), the appraoch I suggest below should help you with that.

What used to happen in version 3.1 and earlier?


Consider the following query:

CREATE TABLE foo (col INT DEFAULT 1, col2 INT CONSTRAINT cons DEFAULT 2);

Usually when defining defaults one would choose the way it has been done for first column “col”, but even the other options as shown for second column “col2” also works.
In version 3.1 and earlier, if at all you ever decide that you don’t want the default on “col2”, you could issue a statement like this:

ALTER TABLE foo DROP CONSTRAINT cons;

Here, the default for “col2” as declared above will be stored as a constraint with name “cons”, so you could issue an ALTER statement on the table and drop it. It worked.

CREATE TABLE foo (col INT DEFAULT 1, col2 INT CONSTRAINT cons DEFAULT 2)
INSERT INTO foo (col) VALUES (0)
INSERT INTO foo (col2) VALUES (0)
INSERT INTO foo VALUES (0, 0)
INSERT INTO foo VALUES (default, default)
ALTER TABLE foo DROP CONSTRAINT cons
INSERT INTO foo (col) VALUES (0)
 SELECT * FROM foo
                 col                col2
----------------------------------------
                   0                   2
                   1                   0
                   0                   0
                   1                   2
                   0              (NULL)


What changed in version 3.5?


In version 3.5 and onwards the constraint as declared above will not be table constraint anymore, rather the default constraint on a column will be maintained as column property at column level.

So, the statement,

 ALTER TABLE foo DROP CONSTRAINT cons

would NOT work anymore. The “CONSTRAINT cons” from the original statement will be ignored. On issuing above mentioned ALTER statement, you will get an error saying its a bad constraint ID and it cannot be found.

To drop such constraint you will have to issue the following statement (this works on all versions and is the right way to drop the defaults than going  for constraint names):

ALTER TABLE foo ALTER COLUMN col2 DROP DEFAULT

To see the column properties, you can query information_schema.columns. Look for the row with your column name, there will be two columns (COLUMN_HASDEFAULT and  COLUMN_DEFAULT), they indicate whether you have the default on that column or not and the default value for that column, if any.

If you are feeling lazy to type in this, you can also do this through GUI, by editing the table schema in Visual Studio or SQL Server. In the table schema, go to the particular column and for the default property delete the value which you had previously entered. This is equivalent to issuing the above ALTER TABLE statement, in fact, behind the scenes they both use the same statement.


Ravi Tandon
SDET, SQL Server Compact