Add DEFAULT constraint with a generated name

Question

In SQL Server I have an existing table. Now I want to add a DEFAULT constraint to one of the columns - without having to define a name for it.

-- generated name works fine when creating new column
ALTER TABLE [xyz]
ADD [newColumn] int NOT NULL DEFAULT 0

-- when adding a default value afterwards I seem to have to provide a name
ALTER TABLE [xyz]
ADD CONSTRAINT [iDontWantToPutANameHere] DEFAULT 0 FOR [existingColumn]

Answer 1

You can create default constraint for an existing column, without specifying its name, e.g.:

create table test
(
    existing_column int
)

alter table test
    add default 0 for existing_column

However, there are plenty articles, suggesting that it is better to name constraints explicitly. Because of it is harder to drop or other way alter system-named constraint. Also if you have multiple instances of the same database, the default name will no be the same across them.

Answer 2

Pretty sure the only way you can add a default constraint to an existing column is by naming it.

If it's THAT important to you to get a system-generated name, you can do this:

1. Alter Table adding new column with un-named DEFAULT value.
2. Update Table, setting the values of the new column to the values of the old column.
3. Drop the old column.
4. Rename the new column to the name of the old column.

Personally I wish that SQL Server didn't give any option for not naming your constraints. I hate dealing with the system generated names that some lazy developer left behind.