sql example 4 -- alter

alter table user1 add age int not null default 10;           # 添加到最后一列
alter table user1 add age1 int not null default 10 first;    # 添加到第一列
alter table user1 add age2 int not null default 10 after age;
alter table user1 add age3 int not null, add age4 int not null;

alter table user1 drop age;
alter table user1 drop age1, drop age2, drop age3, drop age4;

create table test1 (
id int ,
username varchar(10) not null
);

create table test2 (
id2 int not null,
username2 varchar(10) not null
);

primary key:

alter table test2 add constraint pk_test2_id primary key(id2);

show columns from test2;

alter table test2 drop primary key;

show columns from test2;

创建 foreign key:

alter table test2 add foreign key (id2) references test1(id);      # 类型要相同

Can‘t create table ‘test.#sql-158e_11‘ (errno: 150)                # 类型不同 150

alter table test2 modify id2 int not null;
alter table test1 modify id int not null;
alter table test1 add foreign key (id) references test2(id2);

Can‘t create table ‘test.#sql-158e_11‘ (errno: 150)
alter table test2 add constraint pk_test2_id primary key(id2);      # 父表一定要是主键
alter table test1 add foreign key (id) references test2(id2);
show columns from test2;

go to modify

删除 foreign key:

show keys from test1;
show index from test1;
show create table test1;
                #  test1_ibfk_1
alter table test2 drop foreign key test1_ibfk_1;

alter table test2 add age int not null ;
alter table test2 alter age set default 15;    # 添加 default 属性
alter table test2 alter age drop default;      # 删除 default 属性

alter table test2 add unique (username2);
show columns from test2;

alter table test2 modify id smallint not null FIRST;
alter table test2 modify id tinyint not null FIRST;   # 把 *大整数* 切换到 *小整数* 注意防止数据丢失

alter table test2 change pid p_id tinyint;
alter table test2 change p_id pid int;

rename table test1 to test3;
alter table test3 rename to test1;

drop table test1, test2;