SQL--Alter

Source: Internet
Author: User

<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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.