<title>Sql–alter</title> Sql–altertable of Contents
- Character
- Engine
- Add Drop
- Primary key:
- FOREIGN key
- Default
- Modify Change
- Rename generally cannot be changed, the data may be wrong to change
Character
Create database test; Alter character set UTF8; Alter character set GBK;
Create table user1; Alter Table User1 character set GBK; Alter Table User1 character set UTF8;
Engine
ALTER TABLE table_name ENGINE=INNODB;
Add Drop
Alter TableUser1AddAgeint not NULL default Ten; # Add to last columnAlter TableUser1AddAge1int not NULL default Ten First; # Add to first columnAlter TableUser1AddAge2int not NULL default Ten AfterAgeAlter Table User1 AddAge3int not NULL,AddAge4int not NULL;
Alter Table User1 Drop age; Alter Table User1 Drop Drop Drop drop age4;
Create Table test1 int varchar not null); Create Table test2 int not NULL varchar not null);
Primary key:
Alter Table test2 Add constraintpk_test2_idPrimary Key(ID2); Show columns fromTest2;Alter Table test2 Drop Primary Key;Alter Table test2 Add Primary Key(ID2); Show columns fromTest2;Alter Table test2 Drop Primary Key;
FOREIGN key
Alter Table test2 Add Foreign Key references test1 (ID); # type to the same "Can' t create table 'test.#sql-158e_11' (errno:150)" # type different
Alter Table test2 ModifyId2int not NULL;Alter Table test1 ModifyIdint not NULL;Alter Table test1 Add Foreign Key(ID)ReferencesTest2 (ID2); " Can' t create table 'test.#SQL-158e_11' (errno:150) "
Alter Table test2 Add constraint Primary Key (ID2); # parent table must be primary key altertabletest1addforeignkey References from Test2;
Delete foreign key:
from from Create table test1;# test1_ibfk_1altertabletest2dropforeign key Test1_ibfk_1;
Default
alter table Span style= "color: #87cefa;" >test2 int not null ; alter table test2 set default 15 ; # add default property table test2 alter age drop default ; # delete default Property
Alter Table test2 Add Unique from Test2;
Modify Change
Alter Table test2 ModifyIdsmallint not NULL First;Alter Table test2 ModifyID tinyint not NULL First; # switch * Big integer * to * small integer * Note to prevent data lossAlter Table test2Change PID p_id tinyint;Alter Table test2Change p_id PIDint;
Rename generally cannot be changed, the data may be wrong to change
Table to Test3; Alter Table test3 to Test1; Drop Table test1, test2;
SQL--Alter