MySQL列的完整性约束笔记

列的完整性约束

• 主键是表的一个特殊字段，能唯一标识该表中的每条信息。主键和记录的关系，如同身份证和人的关系。主键用来标识每个记录，每个记录的主键值都不同。身份证用来表明人的身份，每个人都具有唯一的身份证号。设置表的主键是指在创建表时设置表的某个字段为该表的主键。
• 主键的主要目的是帮助数据库管理系统以最快的速度查找到表的某一条信息。主键必须满足的条件就是主键必须是唯一的，表中任意两条记录的主键字段的值不能相同，并且是非空值。主键可以是单一的字段，也可以是多个字段的组合。

单字段主键

• 单字段主键的语法规则如下：

CREATE TABLE tablename(
    propName propType PRIMARY KEY, ......
);
或
CREATE TABLE tablename(
    propName propType , ......
PRIMARY KEY(propType）
);

创建表school，设置id字段为PK约束，再查看class表信息，SQL语句如下

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int PRIMARY KEY, name varchar(128), teacher varchar(64)); #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同
mysql> show create table class ;  #同样可以实现查询表class 的定义
mysql> insert into class VALUES(1,'一班','martin');  #插入成功
mysql> insert into class VALUES(1,'二班','rock');    #因主键约束，插入失败

Mysql 支持给主键设置名字：

CREATE TABLE tablename(
    propName propType , ......
CONSTRAINT pk_name PRIMARY KEY(propType）
);

给id字段的PK约束设置一个名字，可以执行SQL语句CONSTRAINT。创建表class:

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int , name varchar(128), teacher varchar(64), CONSTRAINT id_pk PRIMARY KEY (id));         #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同
mysql> show create table class ;  #同样可以实现查询表class 的定义
mysql> insert into class VALUES(1,'一班','martin');  #插入成功
mysql> insert into class VALUES(1,'二班','rock');    #因主键约束，插入失败

多字段主键

• 主键是由多个属性组合而成时，在属性定义完之后统一设置主键。语法规则如下：

CREATE TABLE tablename(  
    propName1 propType , 
    propName2 propType , 
    ......                     
[CONSTRAINT pk_name]PRIMARY KEY(propName1, propName2)
);

多字段主键的设置。

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class3(id int, name varchar(128), teacher varchar(64), CONSTRAINT id_pk PRIMARY KEY(id,name));         #创建表class3,设置联合主键
mysql> desc class3 ;  #查询表class3 的定义, describe class3 效果等同
mysql> insert into class3 VALUES(1,'一班','梁老师');  #插入成功
Query OK, 1 row affected (0.00 sec)

mysql> insert into class3 VALUES(1,'一班','张老师');  #插入成功
ERROR 1062 (23000): Duplicate entry '1-一班' for key 'class3.PRIMARY'
mysql> insert into class3 VALUES(1,'二班','张老师');  #插入成功
Query OK, 1 row affected (0.01 sec)

设置表字段的外键约束（FOREIGN KEY，FK）

• 外键是表的一个特殊字段，外键约束是为了保证多个表（通常为两个表）之间的参照完整性，即构建两个表的字段之间的参照关系。

• 设置外键约束的两个表之间具有父子关系，即子表中某个字段的取值范围由父表决定。

• 例如，表示一个班级和学生关系，即每个班级有多个学生。首先应该有两个表：班级表和学生表，然后学生表有一个表示班级编号的字段no，其依赖于班级表的主键，这样字段no就是学生表的外键，通过该字段班级表和学生表建立了关系。
  一个班级表：
  ID 名称 班主任
  101 六年级一班 马老师
  102 六年级二班 潘老师
  班级表主键
  每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多”：
  ID 姓名 班级ID 性别 年龄
  1 小花 101 M 9
  2 小红 102 F 8
  3 小军 102 M 8
  4 小白 101 F 9
  班级表 为学生表<班级ID >的外键

• 在具体设置FK约束时，设置FK约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为空（NULL）。

• 设置表中某字段的FK约束非常简单，可以在MySQL数据库管理系统中通过SQL语句FOREIGN KEY来实现，

• 其语法形式如下：

CREATE TABLE tablename_1(  
    propName1 propType , 
    propName2 propType , 
    ......                     
[CONSTRAINT fk_name]FOREIGN KEY(propName1) REFERENCES table2_name(table2_column)
);

• 其中,tablename_1参数是要设置外键的表名，propName1参数是要设置外键的字段，tablename_2是父表的名称，table2_column是父表中设置主键约束的字段名。

mysql> use school;   #选择数据库school 
mysql> create table class4(id int PRIMARY KEY, name varchar(128), teacher varchar(64)); #创建表class4 
mysql> insert into class4 values(101,'六年级一班','马老师');
mysql> insert into class4 values(102,'六年级二班','潘老师'); 
mysql> desc class ;  #查询表class 的定义, describe class 效果等同
mysql> create table student (id int PRIMARY KEY AUTO_INCREMENT, #主键
                            name varchar(64) NOT NULL, #姓名
                            class_id int,              #外键
                            sex enum('M','F'),         #性别
                            FOREIGN KEY(class_id) REFERENCES class4(id) ); #设置外键
                            #创建表student, class_id 为表class id 字段的外键 
mysql> insert into student(name, class_id, sex) values('小红',102, 'M');   #插入记录，主键自增长
mysql> insert into student(name, sex) values('小军', 'M');   #插入记录，允许外键为空
mysql> select * from student;
+----+------+----------+------+
| id | name | class_id | sex  |
+----+------+----------+------+
|  2 | 小红 |      102 | M    |
|  3 | 小军 |     NULL | M    |
+----+------+----------+------+
2 rows in set (0.00 sec)

设置表字段的非空约束（NOT NULL, NK）

• 设置非空约束目的：
  当数据库表中的某个字段上的内容不希望设置为NULL时，可以使用NK约束进行设置。NK约束在创建数据库表时为某些字段上加上“NOT NULL”约束条件，保证所有记录中的该字段都有值。如果在用户插入的记录中该字段为空值，那么数据库管理系统会报错。
• 其语法形式如下：

CREATE TABLE tablename(
    propName propType NOT NULL , ......
);

• Demo

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int, name varchar(128) NOT NULL, teacher varchar(64));         #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同 
mysql> show create table class ;  #同样可以实现查询表class 的定义

设置表字段唯一约束（UNIQUE，UK）

• 目的：当数据库表中某个字段上的内容不允许重复时，可以使用UK约束进行设置。UK约束在创建数据库时为某些字段加上“UNIQUE”约束条件，保证所有记录中该字段上的值不重复。如果在用户插入的记录中该字段上的值与其他记录中该字段上的值重复，那么数据库管理系统会报错。

CREATE TABLE tablename(
    propName propType UNIQUE, ......
);

• 注意： unique 约束允许受约束的字段出现多个空值 NULL, 不算重复

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));         #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同 
mysql> show create table class ;  #同样可以实现查询表class 的定义 

设置表字段值自动增加（AUTO_INCREMENT）

• 目的：AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当向数据库表中插入新记录时，字段上的值会自动生成唯一的ID。在具体设置AUTO_INCREMENT约束时，一个数据库表中只能有一个字段使用该约束，该字段的数据类型必须是整数类型。由于设置AUTO_INCREMENT约束后的字段会生成唯一的ID，因此该字段也经常会同时设置成PK主键。
• 其语法形式如下：

CREATE TABLE tablename(
    propName propType AUTO_INCREMENT, ......
);

• Demo：

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int PRIMARY KEY AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64));         #创建表class,设置字段id为自增长
mysql> show create table class ;  #查询表class 的定义 

• 注意: mysql8 中,AUTO_INCREMENT 必须设为键(主键、外键和唯一键均可)！ 否则会报错！

设置表字段的默认值（DEFAULT）

• 目的：当为数据库表中插入一条新记录时，如果没有为某个字段赋值，数据库系统就会自动为这个字段插入默认值。为了达到这种效果，可通过SQL语句关键字DEFAULT来设置。
• 其语法形式如下：

CREATE TABLE tablename(
    propName propType DEFAULT defaultvalue, ......
);

• 注意： 如果没有设置，则默认为空