MySQL完整性约束的定义代码实例教程

本篇文章小编给大家分享一下MySQL完整性约束的定义代码实例教程，文章代码介绍的很详细，小编觉得挺不错的，现在分享给大家供大家参考，有需要的小伙伴们可以来看看。

完整性约束的定义

为了保证插入数据的正确性和合法性，给表中字段添加，除了 数据类型约束 以外的 其他约束条件 。

完整性约束的分类

实体完整性：记录之间不能重复。

主键约束(primary key) ：唯一并且不能为空

唯一约束(unique) ：唯一可以为空

主键自增(auto_increment)

域完整性：数据库表的字段，必须符合某种特定的数据类型或约束。

类型约束 ：在创建表的时候，已经给每个字段添加类型了

非空约束 ：not null

默认值 ：default

引用完整性(参照完整性)：一张表中字段的值，需要参考另外一张表中的值。

添加外键约束：foreign key

引用完整性会降低sql的执行效率，有时候能不用就不用。

主键约束(primary key)

主键约束(primary key) ：唯一并且不能为空；

一张表只能有一个主键字段，但是可以有联合主键；

添加主键约束，有以下3种方式：

方式一：在创建表的同时，添加主键约束。
create table student(
    sid int primary key,
    sname varchar(20),
    age int
)charset=utf8;
-- 添加主键约束后，插入相同的sid，会报错。
insert into student(sid,sname,age) values (1,'张三',22);
insert into student(sid,sname,age) values (1,'李四',33);

方式二：在创建表的同时，添加主键约束，但是添加方式不同。
  "只有这种添加主键的方式，可以添加联合主键"
create table student(
    sid int,
    sname varchar(20),
    age int,
    primary key(sid)
)charset=utf8;

方式三：创建完表后，添加主键约束。
create table student(
    sid int,
    sname varchar(20),
    age int
)charset=utf8;                  
alter table student add primary key(sid);
-- constraint表示添加的是一个约束；      
-- pk_sid是给约束起一个名字；

单个主键和联合主键的区别

"单个主键"
    将表中某一个字段设置为主键，用这一个字段，来唯一确定一条记录。
    只要sid唯一不为空，每一行就是一条唯一的记录。
    primary key(sid)
    sid   sname   age
    1     张三    18
    2     张三    18  

"联合主键"
    将表中2个字段(或多个字段)设置为联合主键，用这2个字段(或多个字段)，
    来唯一确定一条记录。
    这2个字段可以分别重复，只要不是同时重复即可。
    primary key(sid，classid)
    sid   classid   sname   age
    1     2         张三    18
    1     3         张三    18  
    2     4         李四    20
    3     4         王五    30
    -- 下面两条记录，联合字段同时重复，表示一条重复记录，插入时报错。
    5     5         李闯    25  
    5     5         王二    24

主键字段的挑选原则

通常选择无意义的字段作为主键字段，比如说表中记录每一行行号的id字段，就是无意义的字段，很适合作为主键

主键字段一般不会对其进行修改(像字段名、字段类型等)

经常变化的字段，有意义的字段，不适合作为主键

特别注意：当一个建表语句中，某个字段只有primary key限制，而没有使用主键自增 auto_increament
需要我们自己给主键字段插入值，否则会出现如下错误。 "ERROR 1364 (HY000): Field 'sid' doesn't have a default value"

但是我们上面说过，主键字段一般是不进行修改的，也就是说不要主动给他值，主键字段应该是让系
统默认赋值。这个在学习过 主键自增 后你就明白了。

记住： primary key 和 auto_increament 最好配合使用。

举例说明：

删除主键约束

alter table student drop primary key;

主键自增(auto_increment)

主键自增的含义和特点

见名知意，这个是用来 帮助主键自动添加值 的一个约束。上面我们说过，主键字段最好不要有任何改动，当然也包括给主键字段插入数值的时候，也不要手动去添加主键，而是让系统自动给主键字段进行赋值。主键自增有如下2个特点：

设置了主键自增后，主键值会从1开始，每次自增1。

以前用过的主键值，当你删除该行记录后，主键值仍然是一个新的值，并不会重复使用以前出现过的主键值。

对第二个特点的说明：

添加唯一约束，有以下2种方式：

方式一：创建表的时候，在添加主键的同时，添加主键自增。 
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int,
   idcard varchar(18) 
)charset=utf8;  
-- primary key和auto_increment配合使用，插入数据的时候，
-- 不需要给主键字段sid赋值了。
insert into student(sname,age) values ("张三",22),("李四",25)；

方式二：创建完表后，给字段同时添加"主键和主键自增"。
create table student(
   sid int,
   sname varchar(20),
   age int,
   idcard varchar(18) 
)charset=utf8;  
-- 以下2种方式，均可以给字段同时添加"主键和主键自增"。
alter table student modify sid int primary key auto_increment;
alter table student change sid sid int primary key auto_increment;

删除主键自增

alter table student modify sid int primary key;

唯一约束(unique)

唯一性约束的含义

由于一张表中只能由一个主键。但是对于某些字段，像 身份证 这样的字段，肯定也是唯一的，我们不能又将其设置为主键(身份证字段属于有意义的字段)，那么怎么保证它的唯一性呢？这就是接下来要讲述的 唯一约束 。注意：一张表可以有多个唯一约束。

添加唯一约束，有以下3种方式：

方式一：在创建表的同时，添加唯一约束。
create table student(
    sid int primary key key auto_increment,
    sname varchar(20),
   age int,
   idcard varchar(18) unique
)charset=utf8; 


方式二：在创建表的同时，添加唯一约束，但是添加方式不同。
  "只有这种添加主键的方式，可以添加联合主键"
create table student (
    sid int primary key auto_increment,
    sname varchar(20),
    age int,
    idcard varchar(18),
    unique(idcard)
)charset=utf8; 

方式三：创建完表后，添加唯一约束。
create table student (
    sid int primary key auto_increment,
    sname varchar(20),
    age int,
    idcard varchar(18)
)charset=utf8;                      
alter table student add unique(idcard);

-- 设置了唯一约束的字段，当插入相同的值，会报错。
insert into student(sname,age,idcard) values ("李四",18,"123456");
insert into student(sname,age,idcard) values ("王五",22,"123456");

删除唯一约束

删除唯一约束，使用的语句有点不同。
alter table student drop key idcard;

添加非空约束(not null)

添加非空约束

非空约束指的是我们给某个字段设置了非空约束后，当我们给该字段插入空值的时候，会报错。添加非空约束，有以下2种方式：

方式一：创建表的时候，给字段添加非空约束。 
create table student(
   sid int primary key auto_increment,
   sname varchar(20) not null,
   age int,
   idcard varchar(18) 
)charset=utf8;

方式二：创建完表后，给字段添加非空约束。
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int,
   idcard varchar(18) 
)charset=utf8;

-- 有以下两种添加方式。
alter table student modify sname varchar(20) not null;
alter table student change sname sname varchar(20) not null;

字段添加非空约束后具有如下特点

字段设置了非空约束后，你不能插入null值。

字段设置了非空约束后，不但不能插入null值，而且还不能不插入值

"这些细节知识，有时候是我们没有注意到的，因此单独拿出来说明。"
一：字段设置了非空约束后，你不能插入null值。
-- 下面这样插入会报错。
insert into student(sname,age) values (null,22);

二：字段设置了非空约束后，不但不能插入null值，而且还不能不插入值。
-- 下面这样插入也会报错。
insert into student(age,idcard) values (33,"123456789");

举例如下：

添加默认值约束(default)

某个字段当被设置了 默认值 以后，当我们不为该字段指定值的时候，就会显示这个默认值。

添加默认值约束

方式一：创建表的时候，给字段添加默认值。 
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int default 0,
   idcard varchar(18)
)charset=utf8;

方式二：创建完表后，给字段添加默认值。
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int,
   idcard varchar(18)
)charset=utf8;
"不同点，需要特别留意"
-- 使用alter添加默认值的代码有点特别。
alter table student alter age set default 0;

删除默认值

alter table student alter column age drop default;

引用完整性(参照完整性)

什么是引用完整性？

引用完整性指的是，一张表中字段的取值，需要参照另一张表中某个字段的取值。这里涉及到的就是 外键 ： foreign key 。一般情况下，本表中的这个字段设置的是外键 foreign key ，参考表中的那个字段的取值，就需要设置为那个表的主键。

参考下图帮助理解：

特别注意：学生表中的 cid 参考的是班级表中的 cid ，可以看到，这里的字段名是完全一样的。但是，实际上，这两个字段名可以不一样，但是这两个字段的数据类型必须是一样的

构成引用完整性的条件：

一张表（参照表）中的某个字段，参照另一张表（被参照表）中的字段（字段名可以不同）。

两张表中的字段名可以不同，但是字段的数据类型必须一致。

参照表中的字段在该表中是 foreign key 。被参照表中的字段在该表中是 primary key 。

添加外键约束(foreign key)

需要记住一句话：先有被引用的表，然后才可以给当前表添加外键约束，否则，你引用谁去？

添加外键约束，有以下2种方式：

方式一：创建表的时候，给字段添加外键约束。 
create table classroom(
    cid int primary key auto_increment,
    cname varchar(20) not null
)charset=utf8;

create table student(
   sid int primary key auto_increment,
   sname varchar(20) not null,
   age bit(1),
   cid int,
   constraint fk_cid foreign key(cid) references classroom(cid)
)charset=utf8;

方式二：创建完表后，给字段添加外键约束。
create table classroom(
    cid int primary key,
    cname varchar(20) not null
)charset=utf8;

create table student(
    sid int primary key auto_increment,
    sname varchar(20) not null,
    age bit(1),
    cid int
)charset=utf8;

alter table student add constraint fk_cid foreign key(cid)
references classroom(cid);
"注意：constraint后面接的是别名，fk_cid相当于起的别名。"