MySQL的那些事儿（二）MySQL建表约束

一、约束是什么？

MySQL主要有六种约束：

1. 主键约束  primary key 用以唯一确定一张表中的记录。
2. 自增约束  auto_increment 使某个字段自动增长，常用于主键id。（只能有一个，且必须作用于某个key上）
3. 唯一约束  unique 保证某个字段唯一，不重复。
4. 非空约束  not null 设置非空。
5. 默认约束  default 设置字段的默认值。
6. 外键约束  foreign key 添加外键，使两张表关联起来。

其中，主键约束包含联合主键，即将多个字段组合起来作为主键，只要所有字段加在一起不重复就可以。例如：

create table test2( 
    id int, name varchar(20), 
    telephone varchar(11), 
    email varchar(20), 
    address varchar(50), 
    active int, 
    //设置id,name,telephone为联合主键 
    primary key(id,name,telephone) 
);

二、如何添加约束？

1、可以在创建表的时候直接添加，例如：

create table ums_user( 
    id int primary key auto_increment, 
    username varchar(20) not null unique COMMENT '登录名', 
    password varchar(50) not null COMMENT '密码', 
    lastlogin datetime not null COMMENT '最后一次登陆时间', 
    active int not null COMMENT '是否有效 1有效 0无效' 
); 
//这里为id添加了主键约束和自增约束，为username添加了非空约束和唯一约束，其他字段添加了非空约束。 //COMMENT是该字段的注释。

2、还可以在建表之后，使用alter关键字或者modify关键字添加或删除约束。例如下表：

create table test1( 
    id int, name varchar(20), 
    telephone varchar(11), 
    email varchar(20), 
    address varchar(50), 
    active int 
);

test1表在创建时未添加任何约束，下面我们为其添加约束。

为id添加主键约束（两种方式如下）：

alter table test1 add primary key(id); 
alter table test1 modify id int primary key;

为有主键约束的字段添加自增约束

alter table test3 modify id int auto_increment;

为没有主键约束的字段添加自增约束（要先添加键约束，否则会报错：ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key【不正确的表定义;只能有一个自动列，并且必须将它定义为键】）

alter table test3 modify id int primary key auto_increment;

删除自增约束（相当于重新定义id字段）

alter table test3 modify id int;

删除主键约束（如果字段是自增主键，要先删除自增约束，再删除主键约束）

alter table test1 drop primary key;

添加唯一约束（相当于重新定义active字段）

alter table test1 modify active int unique;

删除唯一约束（注意，这里使用了drop 和 index）

alter table test1 drop index active;

添加非空约束（相当于重新定义active字段）

alter table test1 modify active int not null;

删除非空约束（相当于重新定义active字段）

alter table test1 modify active int;

添加默认约束（相当于重新定义active字段）

alter table test1 modify active int default 1;

删除默认约束（相当于重新定义active字段）

alter table test1 modify active int;

三、关于外键？

//给product表中的这个cno字段 添加一个购物车表的cid字段作为外键约束 
alter table product add foreign key(cno) references category(cid);

1.主表中没有的数据,在附表中,是不可以使用的.

2.主表中记录的数据现在正在被附表所引用,那么主表中正在被引用的数据不可以被删除

3.若要想删除,先将附表中的数据删除在删除主表数据

 

在真正的开发之中，并不推荐使用外键，为什么呢？（以下内容摘录自网络）

首先我们明确一点，外键约束是一种约束，这个约束的存在，会保证表间数据的关系“始终完整”。因此，外键约束的存在，并非全然没有优点。比如使用外键，可以

• 保证数据的完整性和一致性
• 级联操作方便
• 将数据完整性判断托付给了数据库完成，减少了程序的代码量

然而，鱼和熊掌不可兼得。外键是能够保证数据的完整性，但是会给系统带来很多缺陷。正是因为这些缺陷，才导致我们不推荐使用外键，具体原因如下：

性能问题

假设一张表名为user_tb。那么这张表里有两个外键字段，指向两张表。那么，每次往user_tb表里插入数据，就必须往两个外键对应的表里查询是否有对应数据。如果交由程序控制，这种查询过程就可以控制在我们手里，可以省略一些不必要的查询过程。但是如果由数据库控制，则是必须要去这两张表里判断。

并发问题

在使用外键的情况下，每次修改数据都需要去另外一个表检查数据,需要获取额外的锁。若是在高并发大流量事务场景，使用外键更容易造成死锁。

扩展性问题

这里主要是分为两点：

做平台迁移方便，比如你从Mysql迁移到Oracle，像触发器、外键这种东西，都可以利用框架本身的特性来实现，而不用依赖于数据库本身的特性，做迁移更加方便。

分库分表方便，在水平拆分和分库的情况下，外键是无法生效的。将数据间关系的维护，放入应用程序中，为将来的分库分表省去很多的麻烦。

技术问题

使用外键，其实将应用程序应该执行的判断逻辑转移到了数据库上。那么这意味着一点，数据库的性能开销变大了，那么这就对DBA的要求就更高了。很多中小型公司由于资金问题，并没有聘用专业的DBA，因此他们会选择不用外键，降低数据库的消耗。相反的，如果该约束逻辑在应用程序中，发现应用服务器性能不够，可以加机器，做水平扩展。如果是在数据库服务器上，数据库服务器会成为性能瓶颈，做水平扩展比较困难。

 

以上关于“不推荐使用外键的原因”摘录自网络。原作者：孤独烟 出处： http://rjzheng.cnblogs.com/