Default Restricted Database Column

Question

I create a database column as follows:

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

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

Here constraint df_cloumnname doesn't matter?

Please clarify this.

Answer 1

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

If your column was NOT NULL , then the default constraint will immediately apply.

If you are using SQL Server (you did not specify clearly enough - SQL is a query language ), but not a database product ...), and you want to have a null column with a default constraint and you want the value to be applied to existing rows use this syntax:

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

Add WITH VALUES to your team and you will get the desired result.