mysql 修改表結構文法

ALTER TABLE 只允許添加可包含空值或指定了 DEFAULT 定義的列。

　　如果：

IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = N'tab_test') AND NOT EXISTS(SELECT 1 FROM SYSOBJECTS A, SYSCOLUMNS B WHERE B.ID = A.ID AND B.NAME = N'col_test' and a.name = N'tab_test')
alter table tab_test
add col_test smallint NOT NULL
GO

　　將會收到錯誤資訊。

　　要增加非空列怎麼辦呢？

　　第1種辦法：在add column時指定列有default

alter table tab_test
add col_test NOT NULL CONSTRAINT DFtab_test_col_test DEFAULT 1

　　第2種辦法：先給表增加個table_constraint default,再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

　　第3種辦法：只好先增加空列，在修改為非空啦：

IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = N'tab_test') AND NOT EXISTS(SELECT 1 FROM SYSOBJECTS A, SYSCOLUMNS B WHERE B.ID = A.ID AND B.NAME = N'col_test' and a.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

 

文法如下：

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 } ]    }