MySQL alter table structure syntax

Alter table only allows adding columns that can contain null values or specified the default definition.

If:

If exists (select 1 from sysobjects where name = n' tab _ test') and not exists (select 1 from sysobjects A, syscolumns B where B. id =. ID and B. name = n' Col _ test' and. name = n' tab _ test ')
Alter table tab_test
Add col_test smallint not null
Go

You will receive an error message.

What should I do if I want to add a non-empty column?

1st Methods: when adding column, specify that the column has default

Alter table tab_test
Add col_test not null constraint dftab_test_col_test default 1

Method 2: Add table_constraint default to the table and then alter column.

Alter table tab_test
Add constraint dftab_test_col_test default 1 for col_testalter table tab_test
Alter column col_test int not null

3rd Methods: You have to add an empty column first and change it to a non-empty one:

If exists (select 1 from sysobjects where name = n' tab _ test') and not exists (select 1 from sysobjects A, syscolumns B where B. id =. ID and B. name = n' Col _ test' and. name = n' tab _ test ')
Alter table tab_test
Add col_test smallint
Go
Alter table tab_test
Alter column col_test smallint not null
Go

 

Syntax:

ALTER TABLE table_name    ALTER COLUMN column_name    {    type_name[({precision[.scale]})][NULL|NOT NULL]   {DROP DEFAULT    | SET DEFAULT constant_expression    | IDENTITY [ ( seed , increment ) ]   } | ADD    { < column_definition > | < table_constraint > } [ ,...n ] | DROP    { [ CONSTRAINT ] constraint_name    | COLUMN column }] }< column_definition > ::=    { column_name data_type }    [ [ DEFAULT constant_expression ]       | IDENTITY [ ( seed , increment ) ]    ]    [ROWGUIDCOL]   [ < column_constraint > ] [ ...n ] ]< column_constraint > ::=    [ NULL | NOT NULL ]    [ CONSTRAINT constraint_name ]    {       | { PRIMARY KEY | UNIQUE }       | REFERENCES ref_table [ (ref_column) ]       [ ON DELETE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]       [ ON UPDATE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]   }< table_constraint > ::=    [ CONSTRAINT constraint_name ]    { [ { PRIMARY KEY | UNIQUE }       { ( column [ ,...n ] ) }       | FOREIGN KEY         ( column [ ,...n ] )        REFERENCES ref_table [ (ref_column [ ,...n ] ) ]       [ ON DELETE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]       [ ON UPDATE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]    }