SQL--Alter

<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