MySQL - 5.1 约束简化

创建表的时候指定约束
primary key 主键
not null 非空
default '123' 默认
unique 唯一
constraint fk_cid foreign key(lie2) references bookcategory(category_id) 外键
on delete cascade 加在外键设置后面，从父表删除或更新且自动删除或跟新子表中匹配的行
auto_increment 表示自增列
create table player(
    id int auto_increment,
    lie1 int primary key unique,
    lie2 int not null,
    lie3 int default '123',
    CONSTRAINT fk_cid FOREIGN KEY(lie2) REFERENCES bookcategory(category_id) on delete cascade
    primary key(lie1,lie2)  设置联合主键
);

通过修改表指定主键
alter table player modify lie1 int primary key;
alter table player add primary key(lie1);
alter table player add constraint pk_id primary key(lie1);

通过修改表指定唯一约束
alter table player modify lie1 int unique;
alter table player add unique(lie1);
alter table player add constraint uk_bname unique(lie1);

通过修改表指定非空约束
alter table player modify lie2 int not null;

通过修改表指定默认约束
alter table player modify lie3 varchar(10) default 'abc';
alter table player alter column lie3 set default 'abc';

通过修改表添加外键约束
alter table player add foreign key(club_id) references club(id);

删除主键
alter table player drop primary key;

删除唯一约束
alter table player drop index uk_bname
alter table player drop key uk_bname

删除非空约束，修改列时不带not null即可
alter table player modify lie2 varchar(20) not null;

删除默认约束
alter table player modify lie2 varchar(20) default 'abc';
alter table player alter column lie2 drop default;

删除外键约束
alter table player drop foreign key fk_cid;