【49-54】约束
2022-02-14 07:50:00 # MySQL

概述

什么是约束

  1. 在创建表的时候可以给表的字段添加相应的约束
  2. 目的保证表中数据的合法性，有效性，完整性

常见的约束

  1. 非空约束 not null
  2. 唯一约束 unique
  3. 主键约束 primary key 约束的字段不能为null也不能重复（PK）
  4. 外键约束 foreign key （FK）
  5. 检查约束 check （Oracle有，MySQL没有）

非空约束 not null

  • 只能在字段数据类型后面加
  • 无法写成表级约束

    sql

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    mysql> drop table if exists t_user;
    Query OK, 0 rows affected (0.02 sec)

    mysql> create table t_user(
        ->     id int,
        ->     username varchar(255) not null,
        ->     password varchar(255)
        -> );
    Query OK, 0 rows affected (0.01 sec)

    mysql> desc t_user;
    +----------+--------------+------+-----+---------+-------+
    | Field    | Type         | Null | Key | Default | Extra |
    +----------+--------------+------+-----+---------+-------+
    | id       | int          | YES  |     | NULL    |       |
    | username | varchar(255) | NO   |     | NULL    |       |
    | password | varchar(255) | YES  |     | NULL    |       |
    +----------+--------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

唯一约束 unique

  • 唯一约束的字段具有唯一性，不能重复。NULL除外。

给一个列添加 unique

  • 列级约束 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> drop table if exists t_user;
    Query OK, 0 rows affected (0.03 sec)

    mysql> create table t_user(
        ->     id int,
        ->     username varchar(255) unique
        -> );
    Query OK, 0 rows affected (0.02 sec)

    mysql> insert into t_user values(1, 'zhangsan');
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_user values(2, 'zhangsan');
    ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 't_user.username'

给多个列添加 unique

  • 联合起来必须唯一
  • 表级约束 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    drop table if exists t_user;
    create table t_user(
        id int,
        usercode varchar(255),
        username varchar(255),
        unique(usercode, username)
    );
    insert into t_user values(1, '111', 'zhangsan');
    insert into t_user values(1, '222', 'zhangsan');
    insert into t_user values(1, '111', 'lisi');
    select * from t_user;

    mysql> drop table if exists t_user;
    Query OK, 0 rows affected (0.01 sec)

    mysql> create table t_user(
        ->     id int,
        ->     usercode varchar(255),
        ->     username varchar(255),
        ->     unique(usercode, username)
        -> );
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into t_user values(1, '111', 'zhangsan');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user values(1, '222', 'zhangsan');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user values(1, '111', 'lisi');
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_user;
    +------+----------+----------+
    | id   | usercode | username |
    +------+----------+----------+
    |    1 | 111      | zhangsan |
    |    1 | 222      | zhangsan |
    |    1 | 111      | lisi     |
    +------+----------+----------+
    3 rows in set (0.00 sec)

主键约束 primary key

  • 约束的字段不能为null也不能重复
  • 表的设计三范式中有要求，第一范式要求任何一张表都要有主键
  • 一张表的主键约束只能有一个

列级约束添加主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
drop table if exists t_user;
create table t_user(
    id int primary key,
    username varchar(255),
    email varchar(255)
);
insert into t_user(id, username, email) values(1,'zs','zs@e.com');
insert into t_user(id, username, email) values(2,'ls','ls@e.com');
insert into t_user(username, email) values('ww','ww@e.com');
insert into t_user(id, username, email) values(1,'zl','zl@e.com');

mysql> drop table if exists t_user;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_user(
    ->     id int primary key,
    ->     username varchar(255),
    ->     email varchar(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_user(id, username, email) values(1,'zs','zs@e.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id, username, email) values(2,'ls','ls@e.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(username, email) values('ww','ww@e.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

mysql> insert into t_user(id, username, email) values(1,'zl','zl@e.com');

ERROR 1062 (23000): Duplicate entry '1' for key 't_user.PRIMARY'

表级约束添加主键

单一主键

1
2
3
4
5
6
drop table if exists t_user;
create table t_user(
    id int,
    username varchar(255),
    primary key(id)
);

复合主键

1
2
3
4
5
6
7
drop table if exists t_user;
create table t_user(
    id int,
    username varchar(255),
    password varchar(255),
    primary key(id, username)
);

主键相关术语

  1. 主键约束
  2. 主键字段
  3. 主键值

主键作用

  1. 是这张表的唯一标识

主键的分类

按照字段数量

  1. 单一主键（推荐常用）
  2. 复合主键（多个字段联合添加，但是违背三范式）

按照主键性质

  1. 自然主键
  2. 业务主键（主键值和系统的业务挂钩，不推荐用…）

MySQL主键值自增（非常重要）

  • 以 1 开始自增
  • Oracle当中也有提供自增，叫：序列（sequence）对象
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    drop table if exists t_user;
    create table t_user(
        id int primary key auto_increment,
        username varchar(255)
    );
    insert into t_user(username) values('a');
    insert into t_user(username) values('b');
    insert into t_user(username) values('c');
    insert into t_user(username) values('d');
    insert into t_user(username) values('e');
    select * from t_user;

    mysql> drop table if exists t_user;
    Query OK, 0 rows affected (0.01 sec)

    mysql> create table t_user(
        ->     id int primary key auto_increment,
        ->     username varchar(255)
        -> );
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into t_user(username) values('a');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user(username) values('b');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user(username) values('c');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user(username) values('d');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user(username) values('e');
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_user;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | a        |
    |  2 | b        |
    |  3 | c        |
    |  4 | d        |
    |  5 | e        |
    +----+----------+
    5 rows in set (0.00 sec)

外键约束 foreign key

相关术语

  1. 外键约束
  2. 外键字段
  3. 外键值

设计表用于维护学生和班级的信息

  1. 删除信息的时候，先删除子表，在删除父表
  2. 添加信息的时候，先添加父表，在添加子表
  3. 创建表的时候，先添加父表，在添加子表
  4. 删除表的时候，先删除子表，在删除父表
  • 以下t_student中classno引用t_class中的cno字段，此是t_student叫做子表，t_class叫做父表
  • 子表的外键值必须在相应父表中对应字段的值的集合中，但是可以为NULL
  • 外键字段引用其他表的某个字段是，被引用表的字段必须有唯一性 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
drop table if exists t_student;
drop table if exists t_class;

create table t_class(
    cno int primary key,
    cname varchar(255)
);

create table t_student(
    sno int primary key,
    sname varchar(255),
    classno int,
    foreign key(classno) references t_class(cno)
);

insert into t_class values(101,'xxx');
insert into t_class values(102,'yyy');

insert into t_student values(101, 'zs1', 101);
insert into t_student values(102, 'zs2', 101);
insert into t_student values(201, 'zs3', 102);
insert into t_student values(202, 'zs4', 102);
insert into t_student values(203, 'zs5', 102);
insert into t_student values(204, 'zs6', 102);
insert into t_student values(205, 'zs7', 102);

select * from t_class;
select * from t_student;

mysql> select * from t_class;
+-----+-------+
| cno | cname |
+-----+-------+
| 101 | xxx   |
| 102 | yyy   |
+-----+-------+
2 rows in set (0.00 sec)

mysql> select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
| 101 | zs1   |     101 |
| 102 | zs2   |     101 |
| 201 | zs3   |     102 |
| 202 | zs4   |     102 |
| 203 | zs5   |     102 |
| 204 | zs6   |     102 |
| 205 | zs7   |     102 |
+-----+-------+---------+
7 rows in set (0.00 sec)

Prev
2022-02-14 07:50:00 # MySQL
Next