Database column with default constraint

Question

I am creating a database column like this:

Alter table tablename
add column columnname null
add constraint df_columnname default 0

After executing above SQL, the new column is added to table with null values.

Does the constraint df_cloumnname have no meaning here?

Please clarify on this..

Answer 1

If your column is nullable, then adding it with a default constraint has no impact - it can be null, and will remain null. The DEFAULT CONSTRAINT in that case only applies to new rows that are being added (and that do not explicitly specify a value for your column).

If your column were NOT NULL, then the default constraint would be applied right away.

If you're using SQL Server (you didn't specify clearly enough - SQL is the query language - but not a database product...), and you want a nullable column witha default constraint and you want the value to be applied to the existing rows, use this syntax:

ALTER TABLE dbo.tablename
ADD columnname NULL
  CONSTRAINT df_columnname DEFAULT 0 WITH VALUES

Add the WITH VALUES to your command and you should get the desired result.