mysql表的约束相关内容及案例

表的约束

为了防止往数据表中插入错误的数据，在MySQL中，定义了一些维护数据库完整性的规则，即表的约束。约束可以在创建表（create table）的时候规定，也可以在创建表之后规定（通过alter table 语句）常见的表的约束：

上表中列举的约束条件都是针对表中字段进行限制， 从而保证数据表中数据的正确性和唯一性。

一、主键约束

在MySQL中，为了快速查找表中的某条信息，可以通过设置主键来实现。主键约束是通过PRIMARY KEY定义的，它可以唯一标识表中的记录，这就好比身份证可以用来标识人的身份一样。

要求：主键必须唯一，并且非空，可以是单一字段，也可以是多字段，但是只可以有一个主键。

1、添加主键约束

（1）创建表时：

①单字段主键

字段名   数据类型      PRIMARY KEY 

②多字段主键

PRIMARY  KEY（字段名1，字段名2，...字段名n）

（2）创建表后：

（1）alter table 表名 add PRIMARY key(字段名1[,字段名2]);

（2）alter table 表名 modify 字段名 数据类型 PRIMARY key;

（3）alter table 表名 change  原字段名 新字段名 数据类型 PRIMARY key;

说明：一个表最多可以创建一个主键

2、删除主键约束：

alter table 表名 drop PRIMARY key ;

二、非空约束

非空约束指的是字段的值不能为NULL，在MySQL中，非空约束是通过NOT NULL定义的。

1、添加非空约束

（1）创建表时：

字段名   数据类型   NOT  NULL;

（2） 创建表后

alter table 表名 modify 字段名 数据类型 not null;

2、 删除非空约束：

alter table 表名 modify 字段名 数据类型  null;

三、唯一约束

唯一约束用于保存数据表中字段的唯一性，类似于主键，即表中字段值不能重复出现，但是允许为空。唯一约束是通过UNIQUE定义的。

1、添加唯一性约束

（1）创建表时：

字段名   数据类型   UNIQUE;

（2）创建表后

alter table 表名 modify 字段名 数据类型 UNIQUE;

2、删除唯一性约束：

唯一性约束名称查询：show create table 表名;

alter table 表名 drop index <唯一性约束名称>;

四、默认约束

默认约束用于给数据表中的字段指定默认值，即当在表中插入一条新纪录时，如果没有给这个字段赋值，那么，数据库系统会自动为这个字段插入默认值。默认值是通过DEFAULT关键字定义的，其基本语法如下：

1、添加默认值约束

（1）创建表时：

字段名   数据类型   DEFAULT   默认值;

（2）创建表后：

alter table 表名称 modify 字段名 数据类型 default 默认值;

说明：默认值约束一般不在唯一键和主键列上加

2、删除默认值约束：

alter table 表名称 modify 字段名 数据类型 ;

五、AUTO_INCREMENT

作用：某个字段的值自增auto_increment

特点和要求 ：

（1）一个表最多只能有一个自增长列 

（2）当需要产生唯一标识符或顺序值时，可设置自增长 

（3）自增长列约束的列必须是键列（主键列，唯一键列） 

（4）自增约束的列的数据类型必须是整数类型 

1、添加自增约束

（1）创建表时：

字段名   数据类型   primary key/unique auto_increment;

（2）创建表后：

alter table 表名称 modify 字段名 数据类型 auto_increment;

2、删除自增约束：

alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

 多表操作-外键

一、概念解析

1、单表操作：针对一张表的操作。

2、多表操作：需要对两张以上的表进行操作。

3、主键：能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。

4、外键：可以明确声明表示表和表之间关系的字段的参照关系，使数据库帮我们维护这种关系。一张表引用另外一个表中的一列或多列数据，被引用的列应该具有主键约束或者唯一性约束。外键作用是建立和加强两个表数据之间的连接。

注意：一张表中外键可以有多个

5、在实际开发的项目中，一个健壮数据库中的数据一定有很好的参照完整性。

参照完整性是指相关联的两个表之间的约束，具体的说，就是从表中每条记录外键的值必须是主表中存在的，因此，如果在两个表之间建立了关联关系，则对一个关系进行的操作要影响到另一个表中的记录。

2、 参照完整性案例

定义外键之后，如果在后续的操作中由于新增、修改、删除造成了破坏外键约束的状况发生时，数据库会检测这种状况从而阻止这些操作的进行。

如果一个班级表（grade）和一个学生表（student）之间用班级ID建立关联，班级表（grade）是主表，学生表（student）是从表，在学生表添加班级ID（gid）为该表外键。那么，在向从表（student）中输入一条新记录时，系统要检查新记录的班级ID是否在主表（grade）中已存在，如果存在，则允许执行输入操作，否则拒绝输入，这就是参照完整性。

三、主键和外键的区别

定义：

主键--唯一标识一条记录，不能有重复的，不允许为空

外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值

作用：

主键--用来保证数据完整性

外键--用来和其他表建立联系用的

个数：

主键--主键只能有一个

外键--一个表可以有多个外键

3、 为表添加外键约束

（1） 创建表时为表添加外键约束语法格式：

foreign key(外键字段名) references 外表表名(主键字段名)

《案例》：

#建立数据库，具体语句如下：

CREATE DATABASE chapter05;

#进入数据库，具体语句如下：

USE chapter05;

#建表，具体语句如下：

CREATE TABLE grade(

   id int(4) PRIMARY KEY,

   name varchar(36) 

);

CREATE TABLE student(

   sid int(4)  PRIMARY KEY,

   sname varchar(36),

   gid int(4) NOT NULL,

   foreign key(gid) references grade(id)

);

执行成功后，使用SHOW语句来查看一下学生表

#查看表的详细结构

show create table student;

从查询结果可以看出，gid为student表外键，并且gid外键依赖于grade表的id主键，这样两个表通过外键关联起来了。

 

（2）修改表为表添加外键约束的语法格式如下：

alert table 表名 add constraint FK_ID foreign key(外键字段名) references 外表表名(主键字段名)

其中FK_ID为外键的名称，是随意的。

《案例》：

#删除已建立的表，具体语句如下：

DROP TABLE student;

DROP TABLE grade;

#建表，具体语句如下：

CREATE TABLE grade(

   id int(4)  PRIMARY KEY,

   name varchar(36) 

);

CREATE TABLE student(

   sid int(4) NOT NULL PRIMARY KEY,

   sname varchar(36),

   gid int(4) NOT NULL);

#为student表添加外键约束，具体语句如下：

alter table student add constraint FK_ID foreign key(gid) REFERENCES grade (id);

执行成功后，使用SHOW语句来查看一下学生表

#查看表的详细结构

show create table student;

从查询结果可以看出，gid为student表外键，并且gid外键依赖于grade表的id主键，这样两个表通过外键关联起来了。

六、删除外键约束

在实际开发中，根据业务逻辑的需求，需要解除两个表之间的关联关系时，就需要删除外键约束。删除外键约束的语法如下：

alter table 表名 drop foreign key 外键名;

【例】将student表中的外键约束删除

alter table student drop foreign key FK_ID;

#查看表的详细结构

show create table student;

从查询结果可以看出来，student表中的外键约束已经被删除了。