You can add a new column by using the ADD COLUMN clause. You can specify the location of the column to be added by using the FIRST or AFTER keyword.
If the newly added column has the NOT NULL constraint but no DEFAULT constraint, it will have the hard default when the database server configuration parameter, add_column_update_hard_default is set to yes. However, when the parameter is set to no, the column will have NULL even with the NOT NULL constraint.
If the newly added column has the PRIMARY KEY or UNIQUE constraints, an error will be returned when the database server configuration parameter add_column_update_hard_default is set to yes. When the parameter is set to no, all data will have NULL. The default value of add_column_update_hard_default is no.
For add_column_update_hard_default and the hard default, see CHANGE Clause.
ALTER [ TABLE | CLASS | VCLASS | VIEW ] table_name
ADD [ COLUMN | ATTRIBUTE ] [(]<column_definition>[)] [ FIRST | AFTER old_column_name ]
column_definition ::=
column_name column_type
{ [ NOT NULL | NULL ] |
[ { SHARED <value_specification> | DEFAULT <value_specification> }
| AUTO_INCREMENT [(seed, increment)] ] |
[ UNIQUE [ KEY ] |
[ PRIMARY KEY | FOREIGN KEY REFERENCES
[ referenced_table_name ]( column_name_comma_list )
[ <referential_triggered_action> ... ]
]
] } ...
<referential_triggered_action> ::=
{ ON UPDATE <referential_action> } |
{ ON DELETE <referential_action> } |
{ ON CACHE OBJECT cache_object_column_name }
<referential_action> ::=
CASCADE | RESTRICT | NO ACTION | SET NULL
CREATE TABLE a_tbl;
ALTER TABLE a_tbl ADD COLUMN age INT DEFAULT 0 NOT NULL;
INSERT INTO a_tbl(age) VALUES(20),(30),(40);
ALTER TABLE a_tbl ADD COLUMN name VARCHAR FIRST;
ALTER TABLE a_tbl ADD COLUMN id INT NOT NULL AUTO_INCREMENT UNIQUE;
ALTER TABLE a_tbl ADD COLUMN phone VARCHAR(13) DEFAULT '000-0000-0000' AFTER name;
SELECT * FROM a_tbl;
name phone age id
======================================================================
NULL '000-0000-0000' 20 NULL
NULL '000-0000-0000' 30 NULL
NULL '000-0000-0000' 40 NULL
--adding multiple columns
ALTER TABLE a_tbl ADD COLUMN (age1 int, age2 int, age3 int);