三、MYSQL 约束性条件(列的属性)

最新推荐文章于 2023-03-24 17:19:30 发布

缘聚则合缘灭则散

最新推荐文章于 2023-03-24 17:19:30 发布

阅读量829

点赞数

分类专栏： mysql 文章标签： mysql 约束

原文链接：https://mp.weixin.com

版权

mysql 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

`一、什么是约束性条件`

约束英文：constraint

约束实际上就是表中数据的限制条件,即列的属性

MySQL中创建表的基本语法就是这样的：

CREATE TABLE 表名 (
列名1 列的类型 [列的属性],
列名2 列的类型 [列的属性],
…
列名n 列的类型 [列的属性]
);

`1、约束作用`

表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效

比如name字段中要让其用户名不重复，这就需要添加约束。

`2、约束种类`

非空约束(not null)
默认约束（default）
唯一性约束(unique)
主键约束(primary key) PK
外键约束(foreign key) FK
自增（AUTO_INCREMENT）
注释(comment)

`二、非空约束`

对于某些列，我们要求它们是必填的，也就是不允许存放NULL值，必须给定具体的数据，我们用这样的语法来定义这个列：

列名列的类型 NOT NULL
比如我们把first_table的first_column列定义为NOT NULL。当然，我们在重新定义表之前需要把原来的表删掉：

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE first_table (
-> first_column INT NOT NULL,
-> second_column VARCHAR(100) DEFAULT ‘abc’
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>

这样的话，我们就不能再往这个字段里插入NULL值了，比如这样：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(NULL, ‘aaa’);
ERROR 1048 (23000): Column ‘first_column’ cannot be null
mysql>

可以看到，弹出了错误提示。

一旦对某个列定义了NOT NULL属性，那这个列的默认值就不为NULL了。上边first_column并没有指定默认值，意味着我们在使用INSERT插入行时必须显式的指定这个列的值，而不能省略它，比如这样就会报错的：

mysql> INSERT INTO first_table(second_column) VALUES(‘aaa’);
ERROR 1364 (HY000): Field ‘first_column’ doesn’t have a default value
mysql>

可以看到执行结果提示我们first_column并没有设置默认值，所以在插入数据的时候不能省略掉这个列的值。

`三、默认约束`

DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。
指定方式如下：

列名列的类型 DEFAULT 默认值

示例：
创建first_table表，把first_table的second_column列的默认值指定为’abc’，创建一下这个表：

mysql> CREATE TABLE first_table (
-> first_column INT,
-> second_column VARCHAR(100) DEFAULT ‘abc’
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>

然后插入一条数据后看看默认值是不是起了作用：

mysql> INSERT INTO first_table(first_column) VALUES(1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM first_table;
±-------------±--------------+
| first_column | second_column |
±-------------±--------------+
| 1 | abc |
±-------------±--------------+
1 row in set (0.00 sec)
mysql>

我们的插入语句并没有指定second_column的值，但是可以看到插入结果是按照我们规定的默认值’abc’来设置的。

如果我们不设置默认值，其实就相当于指定的默认值为NULL，比如first_table表并没有设置first_column列的默认值，那它的默认值就是NULL，也就是说上边的表定义语句和下边这个是等价的：

CREATE TABLE first_table (
first_column INT DEFAULT NULL,
second_column VARCHAR(100) DEFAULT ‘abc’
);

`四、唯一性约束`

unique约束的字段，具有唯一性，不可重复，但可以为null（可以有多个）
唯一性约束即唯一索引
被标志了唯一字段的值不允许出现重复，但是有一个特例null不算重复的值。唯一性约束可以通过UNIQUE KEY来实现，Key可以省略。

创建表，保证邮箱地址唯一
列级约束

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique
    -> );
Query OK, 0 rows affected (0.03 sec)

表级约束

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128），
    -> unique(email)
    -> );

如果插入相同email会报错

mysql> insert into t_user(id,name,email) values(1,'xlj','932834897@qq.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user(id,name,email) values(2,'jay','932834897@qq.com');
ERROR 1062 (23000): Duplicate entry '932834897@qq.com' for key 'email'

表级约束可以给多个字段联合约束
联合约束，表示两个或以上的字段同时与另一条记录相等，则报错

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128),
    -> unique(name,email)
    -> );
Query OK, 0 rows affected (0.01 sec)

插入第一条数据

mysql> insert into t_user(id,name,email) values(1,'xxx','qq.com');
Query OK, 1 row affected (0.05 sec)

插入第二条数据，如果是与联合字段中的一条相同，另一条不相同，也是可以的

mysql> insert into t_user(id,name,email) values(2,'mmm','qq.com');
Query OK, 1 row affected (0.05 sec)

插入第三条数据，如果与联合字段都相同，则报错

mysql> insert into t_user(id,name,email) values(3,'mmm','qq.com');
ERROR 1062 (23000): Duplicate entry 'mmm-qq.com' for key 'name'

表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128),
    -> constraint t_user_email_unique unique(email)
    -> );
Query OK, 0 rows affected (0.06 sec)

constraint是约束关键字，t_user_email_unique自己取的名字

`五、主键约束(primary key)PK`

有时候在我们的表里可以通过某个列或者某些列确定唯一的一条记录，我们就可以把这个列或者这些列称为候选键。比如在学生信息表student_info中，只要我们知道某个学生的学号，就可以确定一个唯一的学生信息，也就是一条记录。当然，我们也可以通过身份证号来确定唯一的一条学生信息记录，所以学号和身份证号都可以作为学生信息表的候选键。在学生成绩表student_score中，我们可以通过学号和科目这两个列的组合来确定唯一的一条成绩记录，所以学号、科目这两个列的组合可以作为学生成绩表的候选键。

一个表可能有多个候选键，我们可以选择一个候选键作为表的主键。从定义中就可以看出，一个表最多只能有一个主键，主键的值不能重复，通过主键可以找到唯一的一条记录。

1、主键涉及术语
主键约束
主键字段
主键值
2、以上三种术语关系
表中的某个字段添加主键约束后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值

3、主键约束与“not null unique”区别
给某个字段添加主键约束之后，该字段不能重复也不能为空，效果和”not null unique”约束相同，但是本质不同。

主键约束除了可以做到”not null unique”之外，还会默认添加”索引——index”

4、一张表应该有主键字段
主键值：是当前行数据的唯一标识、是当前行数据的身份证号
即使表中两行记录相关数据相同，但由于主键值不同，所以也认为是两行不同的记录
5、按主键约束的字段数量分类
无论是单一主键还是复合主键，一张表主键约束只能有一个(约束只能有一个，但可以作用到好几个字段)
如果多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。
复合主键中，如果一个列没有定义为NOT NULL，MySQL就自动把这个列定义为NOT NULL。
在这里插入图片描述

单一主键：给一个字段添加主键约束
复合主键：给多个字段联合添加一个主键约束(只能用表级定义)
单一主键(列级定义)

mysql> create table t_user(
    -> id int(10) primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.07 sec)

单一主键(表级定义)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> constraint t_user_id_pk primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

复合主键(表级定义)

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique,
    -> primary key(id,name)
    -> );

主键和唯一性约束的区别

主键和唯一性约束都能保证某个列或者列组合的唯一性，但是：

一张表中只能定义一个主键，却可以定义多个唯一性约束！
主键列不允许存放NULL值，而普通的唯一性约束列可以存放NULL值！
小贴士：
你可能会问为啥主键列不允许存放NULL值，而普通的唯一性约束列却可以呢？哈哈，这涉及到底层存储的事情，你只需要记住这个规定就好了～

`六、外键约束(foreign key)FK`

什么是外键
若有两个表A、B，如果A表中的某个列或者某些列依赖于B表中的某个列或者某些列，那么就称A表为子表，B表为父表。表A的该列或者列组合称为外键。
外键约束主要用来维护两个表之间数据的一致性。

只能是表级定义（如以下例子）
CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action
下面我们来更详细的查看上面语法：

CONSTRAINT子句允许您为外键约束定义约束名称。如果省略它，MySQL将自动生成一个名称。
FOREIGN KEY子句指定子表中引用父表中列的列。您可以在FOREIGN KEY子句后放置一个外键名称，或者让MySQL为您创建一个名称。请注意，MySQL会自动创建一个具有foreign_key_name名称的索引。
REFERENCES子句指定父表及其子表中列的引用。在FOREIGN KEY和REFERENCES中指定的子表和父表中的列数必须相同。
ON DELETE子句允许定义当父表中的记录被删除时，子表的记录怎样执行操作。如果省略ON DELETE子句并删除父表中的记录，则MySQL将拒绝删除子表中相关联的数据。此外，MySQL还提供了一些操作，以便您可以使用其他选项，例如ON DELETE CASCADE，当删除父表中的记录时，MySQL可以删除子表中引用父表中记录的记录。如果您不希望删除子表中的相关记录，请改用ON DELETE SET NULL操作。当父表中的记录被删除时，MySQL会将子表中的外键列值设置为NULL，条件是子表中的外键列必须接受NULL值。请注意，如果使用ON DELETE NO ACTION或ON DELETE RESTRICT操作，MySQL将拒绝删除。
ON UPDATE子句允许指定在父表中的行更新时，子表中的行会怎样执行操作。当父表中的行被更新时，可以省略ON UPDATE子句让MySQL拒绝对子表中的行的任何更新。 ON UPDATE CASCADE操作允许您执行交叉表更新，并且当更新父表中的行时，ON UPDATE SET NULL操作会将子表中行中的值重置为NULL值。 ON UPDATE NO ACTION或UPDATE RESTRICT操作拒绝任何更新。

`1、外键涉及到的术语`

外键约束
外键字段
外键值

`2、外键约束、外键字段、外键值之间的关系`

某个字段添加外键约束之后，该字段称为外键字段，外键字段中每个数据都是外键值

`3、按外键约束的字段数量分类`

单一外键：给一个字段添加外键约束
复合外键：给多个字段联合添加一个外键约束

`4、一张表可以有多个外键字段（与主键不同）`

`5、分析场景`

设计数据库表，用来存储学生和班级信息

两种方案
方案一：将学生信息和班级信息存储到一张表

sno	sname	classno	cname
1	jay	100	浙江省第一中学高三1班
2	lucy	100	浙江省第一中学高三1班
3	king	200	浙江省第一中学高三2班

缺点：数据冗余，比如cname字段的数据重复太多

方案二：将学生信息和班级信息分开两张表存储
学生表（添加单一外键）

sno(pk)	sname	classno(fk)
1	jack	100
2	lucy	100
3	king	200

班级表

cno(pk)	cname
100	浙江省第一中学高三1班
200	浙江省第一中学高三2班

为了保证学生表中的classno字段中的数据必须来自于班级表中的cno字段中的数据，有必要给学生表中的classno字段添加外键约束

注意点
外键值可以为null
外键字段去引用一张表的某个字段的时候，被引用的字段必须具有unique约束
有了外键引用之后，表分为父表和子表
班级表：父表
学生表：子表
创建先创建父表删除先删除子表数据插入先插入父表数据
存储学生班级信息

mysql> drop table if exists t_student;
mysql> drop table if exists t_class;

mysql> create table t_class(
    -> cno int(10) primary key,
    -> cname varchar(128) not null unique
    -> );

mysql> create table t_student(
    -> sno int(10) primary key auto_increment,
    -> sname varchar(32) not null,
    -> classno int(3),
    -> foreign key(classno) references t_class(cno)
    -> );

mysql> insert into t_class(cno,cname) values(100,'aaaaaaxxxxxx');
mysql> insert into t_class(cno,cname) values(200,'oooooopppppp');
mysql> insert into t_student(sname,classno) values('jack',100);
mysql> insert into t_student(sname,classno) values('lucy',100);
mysql> insert into t_student(sname,classno) values('king',200);

班级表t_class

mysql> select * from t_class;
+-----+--------------+
| cno | cname        |
+-----+--------------+
| 100 | aaaaaaxxxxxx |
| 200 | oooooopppppp |
+-----+--------------+

学生表t_student

mysql> select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | jack  |     100 |
|   2 | lucy  |     100 |
|   3 | king  |     200 |
+-----+-------+---------+

在对学生表t_student插入数据的时候，MySQL都会为我们检查一下插入的班号classno 是否能在班级表t_class中找到，如果找不到则会报错。
上表中找出每个学生的班级名称

mysql> select s.`*`,c.`* `from t_student s join t_class c on s.classno=c.cno;
+-----+-------+---------+-----+--------------+
| sno | sname | classno | cno | cname        |
+-----+-------+---------+-----+--------------+
|   1 | jack  |     100 | 100 | aaaaaaxxxxxx |
|   2 | lucy  |     100 | 100 | aaaaaaxxxxxx |
|   3 | king  |     200 | 200 | oooooopppppp |
+-----+-------+---------+-----+--------------+

`七、自增`

如果一个表中的某个列是数值类型的，包括整数类型和浮点数类型，那么这个列可以设置自增属性。所谓自增，意思是如果我们在插入数据的时候不指定该列的值，那么该列的值就是上一列的值加1后的值，定义语法就是这样：

列名列的类型 AUTO_INCREMENT
比如我们想在first_table里设置一个自增列id，并把这个列设置为主键，来唯一标记一行记录，我们可以这么写：

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE first_table (
-> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> first_column INT,
-> second_column VARCHAR(100) DEFAULT ‘abc’
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>

先把原来的表删掉，然后在新表中增加了一个名为id、数据类型为INT UNSIGNED类型的列，并把它设置为主键而且具有递增属性，那我们插入数据的时候就可以不用管这个列，但是它的值将会递增，看：

mysql> INSERT INTO first_table(first_column, second_column) VALUES(1, ‘aaa’), (2, ‘bbb’), (3, ‘ccc’);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM first_table;
±—±-------------±--------------+
| id | first_column | second_column |
±—±-------------±--------------+
| 1 | 1 | aaa |
| 2 | 2 | bbb |
| 3 | 3 | ccc |
±—±-------------±--------------+
3 rows in set (0.00 sec)
mysql>

可以看到，列id是从1开始递增的。在使用递增属性的时候需要注意这几点：

一个表中最多有一个递增列。
一般只为整数类型的列定义递增属性，浮点数类型基本不用递增属性。
一般具有AUTO_INCREMENT属性的列需要建立索引。主键和具有唯一性约束的列会自动建立索引。
一般递增列都是作为主键的属性，来自动生成唯一标识一个记录的主键值。
因为具有AUTO_INCREMENT属性的列是从1开始递增的，所以最好用UNSIGNED来修饰这个列，可以提升正数的表示范围。

`八、注释comment`

列级注释：
create table tablename(列1 列的类型 [列的属性] comment ‘列1的注释’）;
表级注释：
create table tablename(列1 列的类型 [列的属性] comment ‘列1的注释’）comment [=]‘第一个表’;
注释要写在列定义的最后，或表定义的最后。

注释主要是在查看表结构、表的创建语句时，通过添加的注释信息，有提示注明的作用。

小贴士：
每个列可以有多个约束性条件，声明的顺序无所谓，各个约束性条件之间用空白隔开就好了～
注意，有的约束性条件是冲突的，一个列不能具有两个冲突的约束性条件，比如一个列不能既声明为PRIMARY KEY，又声明为UNIQUE KEY，不能既声明为DEFAULT NULL，又声明为NOT NULL。大家在使用过程中需要注意这一点。