How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005? ALTER TABLE SomeTable ADD SomeCol Bit NULL --Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)--Optional Default-Constraint.
You cannot alter a column to specify a default value if one of the following conditions exists: The table is referenced by a view.
Right-click the control that you want to change, and then click Properties or press F4. Click the All tab in the property sheet, locate the Default Value property, and then enter your default value. Press CTRL+S to save your changes.
If a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause. When you specify a default value, the database immediately updates each row with the default value. Note that this can take some time, and that during the update, there is an exclusive DML lock on the table.
Try this:
ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;
From the documentation that you linked to:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
...
ADD [COLUMN] (col_name column_definition,...)
...
To find the syntax for column_definition
search a bit further down the page:
column_definition clauses use the same syntax for ADD and CHANGE as for CREATE TABLE. See Section 12.1.17, “CREATE TABLE Syntax”.
And from the linked page:
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
Notice the word DEFAULT there.
Like this?
ALTER TABLE `tablename` ADD `new_col_name` INT NOT NULL DEFAULT 0;
table users (user_id int unsigned PK, username varchar(32))
alter table users add column verified tinyint unsigned default 0
Simply add default 0
at the end of your ALTER TABLE <table> ADD COLUMN <column> <type>
statement
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With