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.

• marc_s: @Avinash: then you should always use the sql-server tag for your questions.
• Sai Avinash: I did not under stood , WITH values option..can you please clarify..In my database, i am have an existing table with database columns nullable and also have a default constaint..I am Confused!!!
• Sai Avinash: The database column nullable , after making an entry into table , the value stored for that column is the default value specified in the constraint and not null
• marc_s: @Avinash: as you saw: just adding a nullable column with a default constraint does not add the values to the existing rows; when you use WITH VALUES, for each of the existing rows, the defined default values will be inserted.