Add a column with a default value to an existing table in SQL

1. Using SQL Query Add a column with a default

ALTER TABLE table_name
    ADD column_name tada_type NOT NULL 
    CONSTRAINT constraint_name DEFAULT default_value;

If you set the new column nullable, that column value for all existing rows will be NULL instead of the default value. In that case, you can add WITH VALUES to the statement:

ALTER TABLE table_name
    ADD column_name data_type NULL 
    CONSTRAINT constraint_name DEFAULT default_value 


    ADD store_code TEXT NULL 
    CONSTRAINT store_code_d DEFAULT "store1"

2. Using TablePlus GUI Tool Add a column with a default

  • From data table view, switch to database structure view using the Structure button at the window bottom, or use shortcut keys Cmd + Ctrl + ].
  • From the structure editor, click + Column to add a new column. You can also double-click on the empty row to insert.
  • Enter your default column value at column_default field
  • Hit Cmd + S to commit changes to the server.
Add column with default value

And here is the result:

New column with default value

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple relational databases. It’s free anyway.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

TablePlus for MS SQL Server

See More: 1. UPDATE from a SELECT SQL Server

2. What is the difference between “INNER JOIN” and “OUTER JOIN”?

Leave a Reply

Your email address will not be published. Required fields are marked *