mysql建表时候的五种约束

　　一共有五种约束 主键约束、外键约束、唯一约束、非空约束、默认约束：

　　程序实例：

//外键约束,需建立两张表
create table provinces(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL
);

insert into provinces (pname) values ("A");
insert into provinces (pname) values ("B");
insert into provinces (pname) values ("C");
insert into provinces (pname) values ("D");

select * from provinces;

create table user (
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY (pid) references provinces(id)
);

insert into user1(username,pid) values ("tom",1);
insert into user1(username,pid) values ("jack",2);
insert into user1(username,pid) values ("rose",3);

create table user1 (
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY (pid) references provinces(id) on delete cascade
);

show index from provinces\G;  //4,查看索引
show create table user;       //查看建表语句

//修改表结构，增加删除列
alter table user1 add age tinyint unsigned not null default 10;
alter table user1 add column password  VARCHAR(20) not null after username;
alter table user1 drop age, drop password;
//增加、删除约束
alter table user2 add constraint PK_user2_id PRIMARY KEY (id); //主键约束
alter table user2 drop PRIMARY KEY;
alter table user2 add unique (username);//唯一约束
alter table user2 drop unique username;
alter table user2 drop index username;//删除索引

alter table user2 add foreign key (pid)  references provinces (id);//外键约束
alter table user2 drop foreign KEY (pid);//没有删除索引

alter table user2 alter age set default 15;//默认约束
alter table user2 alter age drop default; //删除默认约束
alter table user2 alter username drop unique key;

//修改表列信息
alter table user2 modify id SMALLINT UNSIGNED not null first; //改变顺序第一位
alter table user2 modify id TINYINT UNSIGNED not null ; //修改列数据类型
alter table user2 change pid p_id tinyint unsigned not null ;//修改列名称和数据类型

//修改表名称
alter table user2 rename user_2;
rename table user_2 to user_22;