mysql 表的约束

这篇具有很好参考价值的文章主要介绍了mysql 表的约束。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

mysql 表的约束

NULL/NOT NULL

DEFAULT

comment

zerofill

PRIMARY KRY

删除主键

添加主键

复合主键

AUTO_INCREMENT

UNIQUE KEY

FOREIGN KEY


mysql 表的约束

约束是 mysql 为了保证数据正确的一种手段,而前面在谈数据类型的时候,数据类型其实就是一种约束,但是 mysql 的约束可不仅仅是数据类型,mysql 有各种各样的约束,下面就看一下 mysql 中其他的约束。

NULL/NOT NULL

null / not null 约束是mysql常见的一种约束之一,not null,可以保证用户输入的数据不为空。

创建一张表,里面添加 not null 约束:

mysql> use restraint;
Database changed
mysql> create table t1(
    -> name varchar(12),
    -> age tinyint not null);
Query OK, 0 rows affected (0.01 sec)
​
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(12) | YES  |     | NULL    |       |
| age   | tinyint(4)  | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

这里表已经创建好了,其中 name 并没有设置约束,而age 设置了 not null 约束,通过 desc 查看表结构发现在 Null 那一列发现 name 的 Null 列显示 yes 表示可以为 null ,但是 age 显示 No 表示不能为空。

插入数据测试 not null 约束:

mysql> insert into t1(name, age) values('张三', 27);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t1(age) values(27);
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from t1;
+--------+-----+
| name   | age |
+--------+-----+
| 张三   |  27 |
| NULL   |  27 |
+--------+-----+
2 rows in set (0.00 sec)

上面使用全列插入,还有只插入了 age 发现插入成功,但是只插入了 age 的那一条记录发现name为 NULL。

不插入age 和 插入 age 为NULL 测试:

mysql> insert into t1(name) values(27);
ERROR 1364 (HY000): Field 'age' doesn't have a default value
mysql> insert into t1(name,age) values(27, null);
ERROR 1048 (23000): Column 'age' cannot be null

上面两条记录均插入失败,但是仔细观察发现这两条记录插入的报错信息却是不一样的,没有插入 age 的那条报错显示没有默认的 value 但是插入为 null 的报错信息显示不能为 null。

其实这里发现 not null 约束和 default 约束还有一点点联系,下面看 default 约束会让两者进行对比。

DEFAULT

default 约束就是在填表的时候可以直接忽略掉该列,然后可以让 default 默认填写,但是如果插入了数据,就会按照插入的数据来看。

下面通过查看表创建来看一下 default 和 not null。

创建一张表,里面有 id 为 int 类型且无约束,name varchar(12) 为 not null 还有 age tinyint 为 default 22:

mysql> create table t2(
    -> id int,
    -> name varchar(12) not null,
    -> age tinyint default '22');
Query OK, 0 rows affected (0.00 sec)
​
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(12) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | 22      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

这里创建成功,同时也看到 Null 里面 name 不为空,default 里面 age 默认为 22,和我们设置的一样。

下面在看一下表的创建sql语句:

mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(12) NOT NULL,
  `age` tinyint(4) DEFAULT '22'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql 通过语法分析词法分析以及各种优化等,帮我们记录的该表创建sql语句就是这样的,而 id 里面在我们写的时候并没有添加 default 但是mysql自动帮我们添加 default null。

插入数据:

mysql> insert into t2(id, name, age) values(1, '张三', 10);
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into t2(name, age) values('张三', 10);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t2(name) values('张三');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t2(name, age) values('张三', NULL);
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from t2;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | 张三   |   10 |
| NULL | 张三   |   10 |
| NULL | 张三   |   22 |
| NULL | 张三   | NULL |
+------+--------+------+
4 rows in set (0.00 sec)

这里分别是全列插入,不插入 id ,不插入 id 和 age,不插入 id 且插入 age 为 NULL,上面发现均插入成功,且而没有插入 id 则 id 均为 null,就是像上面的sql 创建语句一样 id 默认为 null,而没有插入 age ,age就是默认 22,还有就是插入 age 为 NULL 也插入成功。

那么通过上面的测试,现在假设我们将一列字段的约束设置为即有 not null 约束,也有 default 约束会怎么样:

mysql> create table t3(
    -> id int not null,
    -> name varchar(12) default '张三',
    -> age tinyint not null default 20);
Query OK, 0 rows affected (0.01 sec)
​
mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(12) | YES  |     | 张三    |       |
| age   | tinyint(4)  | NO   |     | 20      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
​
mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` varchar(12) DEFAULT '张三',
  `age` tinyint(4) NOT NULL DEFAULT '20'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建完成后,现在我们就是很好奇向 age 里面插入数据,或者直接忽略此字段会怎么样?

mysql> insert into t3(id, name, age) values(1, '田七', 12);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t3(id, name) values(1, '田七');
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from t3;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 田七   |  12 |
|  1 | 田七   |  20 |
+----+--------+-----+
2 rows in set (0.01 sec)

上面的插入均成功了,即使忽略了也可以默认,那么下面我们插入 NULL 呢?

mysql> insert into t3(id, name, age) values(1, '田七', NULL);
ERROR 1048 (23000): Column 'age' cannot be null

这里插入失败了,通过上面的测试,我们发现了 not null 和 default 的区别:

  • default 约束的是用户不插入数据的时候

  • not null 约束的是用户插入数据的时候

comment

comment 其实并不是用来约束数据插入的,而在我认为他就是一种软约束,它约束的是用户看到 comment 的时候就知道该列里面填入的数据是何种类型或者样式。

comment 其实在数据类型的时候就以及用过了,而且该约束还是比较简答的。

mysql> create  table t4(
    -> name varchar(12) comment '用户名',
    -> age tinyint comment '用户年龄');
Query OK, 0 rows affected (0.01 sec)
​
mysql> desc t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(12) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

表创建好后,并不能用 desc 查看到该约束,想要查看该约束还是需要用查看该表创建时的sql语句:

mysql> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `name` varchar(12) DEFAULT NULL COMMENT '用户名',
  `age` tinyint(4) DEFAULT NULL COMMENT '用户年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

zerofill

zerofill 该约束其实时用在整数类型的,之前在创建 int 类型的时候后面总是会带一个括号里面写着 11,其实该约束和那个括号就有关系,而且该约束时显示约束,并不会对用户插入数据等有影响,只是在显示的时候会有不同的样子。

mysql> create table t5(
    -> num int zerofill);
Query OK, 0 rows affected (0.01 sec)
​
mysql> desc t5;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| num   | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

这里看到确实添加了 zero fill 并且默认给我们添加了 unsigned 下面插入数据查看我们就知道了:

mysql> insert into t5 values(1);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t5 values(11);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t5 values(111);
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from t5;
+------------+
| num        |
+------------+
| 0000000001 |
| 0000000011 |
| 0000000111 |
+------------+
3 rows in set (0.00 sec)

这里发现插入后,这些数字前面均加了一下 0 这个就是 zerofill 的显示约束而且 int 后面的那个数字就表示可以显示多少位数字,如果是有符号整型,那么就是 11 位,如果是无符号就是 10位,我们也可以改变该 int 后面的数字来查看:

mysql> alter table t5 modify num int(4) zerofill;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> desc t5;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| num   | int(4) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

修改完成后继续查看插入后的数据:

mysql> select * from t5;
+------+
| num  |
+------+
| 0001 |
| 0011 |
| 0111 |
+------+
3 rows in set (0.00 sec)

现在就变成 最高显示4位的整数了,而 zerofill 就是帮助如果没有超出该显示范围就用 0 来补充。

下面插入一个大于 4 位数的数据:

mysql> select * from t5;
+--------+
| num    |
+--------+
|   0001 |
|   0011 |
|   0111 |
| 111111 |
+--------+
4 rows in set (0.00 sec)

这里发现如果超出的话就是正常显示,也不会发生截断。

PRIMARY KRY

主键约束,在一张表里面只能有一个主键,而且主键不能为空且不能重复,下面看一下如何设置主键:

mysql> create table t6(
    -> id int primary key,
    -> name varchar(12) not null,
    -> age tinyint default 18
    -> );
Query OK, 0 rows affected (0.01 sec)
​
mysql> desc t6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(12) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

设置 id 为主键后, Null 列显示 id 不能为空,且 key 列 显示为 pri 也就是 primary key 主键,默认为空。

插入数据:

mysql> insert into t6(id, name, age) values(1, '天蓬元帅', 28);
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into t6(id, name, age) values(2, '玉皇大帝', 106);
Query OK, 1 row affected (0.01 sec)
​
mysql> select  * from t6;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  1 | 天蓬元帅      |   28 |
|  2 | 玉皇大帝      |  106 |
+----+--------------+------+
2 rows in set (0.00 sec)
​

这里插入 id 分别为 1 和 2 显示插入成功,查询数据同样显示插入成功,下面插入相同的 id 查看是否可以插入:

mysql> insert into t6(id, name, age) values(2, '女儿国王', 18);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

这里显示插入失败了,报错原因就是已经有主键了。

那么如果我们直接省略掉该列看一下:

mysql> insert into t6(name, age) values('东海龙王', 18);
ERROR 1364 (HY000): Field 'id' doesn't have a default value

由于主键也是不能为空,所以也不能插入。

那么如果表已经创建好了,但是还想为其插入主键怎么办?

删除主键

alter table table_name drop primary key;

因为只有一个主键,所以删除可以直接 drop primary key。

mysql> alter table t6 drop primary key;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
​
mysql> desc t6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(12) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

这里看到删除成功了。

删除主键后,我们继续插入相同的 id 看是否能插入:

mysql> insert into t6(id, name, age) values(2, '女儿国王', 18);
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from t6;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  1 | 天蓬元帅      |   28 |
|  2 | 玉皇大帝      |  106 |
|  2 | 女儿国王      |   18 |
+----+--------------+------+
3 rows in set (0.00 sec)

这里插入就直接成功了。

添加主键

alter table table_name add primary key(列名);
mysql> alter table t6 add primary key(id);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

上面我们添加主键失败了,为什么?

因为我们在前面插入了相同的 id 所以为 id 添加主键就会失败,所以我们需要修改 id 不重复就好了:

mysql> update t6 set id=3 where name='女儿国王';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
​
mysql> select * from t6;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  1 | 天蓬元帅      |   28 |
|  2 | 玉皇大帝      |  106 |
|  3 | 女儿国王      |   18 |
+----+--------------+------+
3 rows in set (0.00 sec)

修改成功后,我们继续添加主键:

mysql> alter table t6 add primary key(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> desc t6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(12) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

这里添加主键再次成功,下面我们插入相同的 id 看一下:

mysql> insert into t6 (id, name, age) values(3, '东海龙王', 99);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

这里插入相同数据后就失败了。

复合主键

复合主键就是多列共成主键,我们之前说的是主键只能有一个,但是一个主键既可以由一列构成,也可以由多列构成。

添加复合主键:

mysql> create table t7(
    -> student_id char(5),
    -> course_id  tinyint unsigned,
    -> primary key(student_id, course_id)
    -> );
Query OK, 0 rows affected (0.00 sec)
​
mysql> desc t7;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| student_id | char(5)             | NO   | PRI | NULL    |       |
| course_id  | tinyint(3) unsigned | NO   | PRI | NULL    |       |
+------------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

这里看到设置了复合主键, key 列里面的两列都是 PRI,这里并不是两个都是主键,而是这两个形成一个主键。

看到上面的创建复合主键的方法,我们也就知道在添加一个主键的时候也可以这样写。

下面看一下这个复合主键是怎么样子的:

mysql> insert into t7(student_id, course_id) values('11111', 12);
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t7(student_id, course_id) values('22222', 12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t7;
+------------+-----------+
| student_id | course_id |
+------------+-----------+
| 11111      |        12 |
| 22222      |        12 |
+------------+-----------+
2 rows in set (0.00 sec)

这里看到我们插入 student_id 没有重复,但是 course_id 重复了,但是插入成功,下麦呢看一下 student_id 重复:

mysql> insert into t7(student_id, course_id) values('22222', 13);
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from t7;
+------------+-----------+
| student_id | course_id |
+------------+-----------+
| 11111      |        12 |
| 22222      |        12 |
| 22222      |        13 |
+------------+-----------+
3 rows in set (0.00 sec)

下面我们插入了 student_id 重复,但是 course_id 不重复也插入成功了。

下面让 student_id 和 course_id 都重复:

mysql> insert into t7(student_id, course_id) values('22222', 13);
ERROR 1062 (23000): Duplicate entry '22222-13' for key 'PRIMARY'

这里插入相同的数据,显示报错了,这里看到复合主键将这些数据都连接在一起了,所以对于复合主键来说,如果都重复才算重复,只有其中一个重复,或者是都不重复就不算重复。

AUTO_INCREMENT

自增键,一般都是和主键共同使用,下面还是先直接看一下自增键的使用然后在得出结论。

mysql> create table t8(
    -> id int unsigned primary key auto_increment,
    -> name varchar(12) default '张三');
Query OK, 0 rows affected (0.01 sec)
​
mysql> desc t8;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(12)      | YES  |     | 张三    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

创建好后查看到该表的结构,一般自增都在一张表中只能由一个自增键,下面我们可以在创建一张表看一下是否可以由多个自增键:

mysql> create table t9(
    -> id1 primary key auto_increment,
    -> id2 auto_increment);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'primary key auto_increment,
id2 auto_increment)' at line 2

这里就直接报错了,不能有多个自增键。

下面我们插入数据看一下:

mysql> insert into t8(name) values('张三');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t8(name) values('李四');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t8(name) values('王五');
Query OK, 1 row affected (0.00 sec)
​
mysql> select *from t8;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+
3 rows in set (0.00 sec)
​

这里插入后,我们没有插入 id 但是 id 从1开始依次递增,那么我们主动插入 id:

mysql> insert into t8(id, name) values(1000, '赵六');
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from t8;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
| 1000 | 赵六   |
+------+--------+
4 rows in set (0.00 sec)

这里主动插入后也成功了,那么我们继续忽略 id 列呢:

mysql> insert into t8(name) values('田七');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t8(name) values('王小二');
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from t8;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 张三      |
|    2 | 李四      |
|    3 | 王五      |
| 1000 | 赵六      |
| 1001 | 田七      |
| 1002 | 王小二    |
+------+-----------+
6 rows in set (0.00 sec)

这里看到插入的数据是从上一次的数据增加的,这里我们可以看一下该表的创建 sql 就知道了:

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(12) DEFAULT '张三',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

这里创建 sql 里面底下有一句 auto_increment=1003,而且下一次插入的话确实是从 1003 开始,所以我们知道,我们也可以在创建的时候加这一句:

mysql> create  table t9(
    -> id int primary key auto_increment,
    -> name varchar(12) default '张三'
    -> )auto_increment=100;
Query OK, 0 rows affected (0.00 sec)
​
mysql> show create  table t9\G
*************************** 1. row ***************************
       Table: t9
Create Table: CREATE TABLE `t9` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) DEFAULT '张三',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

这里创建好后,看到自增就是从100开始,下面插入数据查看:

mysql> insert into t9(name) values('张三');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t9(name) values('李四');
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into t9(name) values('王五');
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from t9;
+-----+--------+
| id  | name   |
+-----+--------+
| 100 | 张三   |
| 101 | 李四   |
| 102 | 王五   |
+-----+--------+
3 rows in set (0.00 sec)

UNIQUE KEY

唯一键和主键很容易弄混,有时候不知道该使用主键还是唯一键,唯一键和主键功能上的区别并不大,只是主键不能为空,而唯一键可以为空。

唯一键不能重复,但是唯一键可以为空,但是如果设置唯一键后在设置一个 NOT NULL,那么就和主键的功能上没有区别了。

下面还是直接看一下唯一键,然后说一下唯一键和主键的意义上的区别。

mysql> create table tt1(
    -> id int primary key,
    -> telephone char(11) unique,
    -> name varchar(12));
    Query OK, 0 rows affected (0.00 sec)
​
mysql> desc tt1;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| telephone | char(11)    | YES  | UNI | NULL    |                |
| name      | varchar(12) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

这里设置了一个自增,一个唯一键,下面插入数据:

mysql> insert into tt1(telephone, name) values('12345678901', '贾宝玉');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into tt1(telephone, name) values('12345678902', '林黛玉');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into tt1(telephone, name) values('12345678903', '王熙凤');
Query OK, 1 row affected (0.01 sec)
​
mysql> select * from tt1;
+----+-------------+-----------+
| id | telephone   | name      |
+----+-------------+-----------+
|  1 | 12345678901  | 贾宝玉    |
|  2 | 12345678902  | 林黛玉    |
|  3 | 12345678903  | 王熙凤    |
+----+-------------+-----------+
3 rows in set (0.01 sec)

这里插入数据成功了,那么下面插入telephone 重复数据:

mysql> insert into tt1(telephone, name) values('12345678903', '贾母');
ERROR 1062 (23000): Duplicate entry '12345678903' for key 'telephone'

这里插入重复 telephone 然后失败,这里的唯一键不能重复,那么能不能为空:

mysql> insert into tt1(telephone, name) values(NULL, '贾母');
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from tt1;
+----+-------------+-----------+
| id | telephone   | name      |
+----+-------------+-----------+
|  1 | 12345678901  | 贾宝玉    |
|  2 | 12345678902  | 林黛玉    |
|  3 | 12345678903  | 王熙凤    |
|  5 | NULL         | 贾母      |
+----+-------------+-----------+
4 rows in set (0.00 sec)

这里插入 null 成功,所以唯一键可以为空,但是主键不能为空,那么为telephone添加not null 和主键还有没有区别:

mysql> alter table tt1 modify telephone char(11) not null unique;
ERROR 1138 (22004): Invalid use of NULL value

上面添加not null 失败为什么? 因为我们插入了贾母 telephone 为空,所以失败了,我们将该数据跟新:

mysql> update tt1 set telephone=12345678904 where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

跟新成功后下面重新添加 not null:

mysql> alter table tt1 modify telephone char(11) not null unique;
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1
​
mysql> desc tt1;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| telephone | char(11)    | NO   | UNI | NULL    |                |
| name      | varchar(12) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

这里修改成功了,然后我们在测试一下:

mysql> insert into tt1(telephone, name) values('12345678901', '薛宝钗');
ERROR 1062 (23000): Duplicate entry '12345678901' for key 'telephone'

上面插入重复数据失败,下面插入NULL:

mysql> insert into tt1(telephone, name) values(NULL, '薛宝钗');
ERROR 1048 (23000): Column 'telephone' cannot be null

这里插入 NULL 也失败了,现在到功能上讲和主键是没有区别的,但是这两个的意义却是不同的,其中主键是为了保证每一行记录的唯一性,而唯一键为了让逻辑更通顺,保证其他的值的唯一,但是其实主键还有索引,而唯一键没有。

FOREIGN KEY

外键约束,外键就是其他表的主键,一般从表里面的外键就来自于主表里面的主键,下面还是直接看一下外键。

下面直接创建两张表,其中学生表中存储的是学生 id name 和 class_id (班级号),class 表里面存的是班级 id 和班级名,学生表通过班级 id 和 班级表里面的班级 id 关联起来。

mysql> create table student(
    -> student_id char(5) primary key,
    -> name varchar(12) not null,
    -> class_id char(5));
Query OK, 0 rows affected (0.01 sec)
​
mysql> create table class(
    -> class_id char(5) primary key,
    -> class_name varchar(12));
Query OK, 0 rows affected (0.01 sec)
​
mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| student_id | char(5)     | NO   | PRI | NULL    |       |
| name       | varchar(12) | NO   |     | NULL    |       |
| class_id   | char(5)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
​
mysql> desc class;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_id   | char(5)     | NO   | PRI | NULL    |       |
| class_name | varchar(12) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

上面并没有通过外键关联起来。

下面插入数据:

mysql> insert into class values('22001', '物联网01班');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into class values('22002', '物联网02班');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into class values('22003', '计算机01班');
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into student values('00001', '豹子头', '22001');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student values('00002', '黑旋风', '22001');
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into student values('00003', '及时雨', '22002');
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from class;
+----------+----------------+
| class_id | class_name     |
+----------+----------------+
| 22001    | 物联网01班     |
| 22002    | 物联网02班     |
| 22003    | 计算机01班     |
+----------+----------------+
3 rows in set (0.00 sec)
​
mysql> select * from student;
+------------+-----------+----------+
| student_id | name      | class_id |
+------------+-----------+----------+
| 00001      | 豹子头    | 22001    |
| 00002      | 黑旋风    | 22001    |
| 00003      | 及时雨    | 22002    |
+------------+-----------+----------+
3 rows in set (0.01 sec)

上面就是插入数据,那么我们继续插入数据:

mysql> insert into student values('00004', '智多星', '22006');
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from student;
+------------+-----------+----------+
| student_id | name      | class_id |
+------------+-----------+----------+
| 00001      | 豹子头    | 22001    |
| 00002      | 黑旋风    | 22001    |
| 00003      | 及时雨    | 22002    |
| 00004      | 智多星    | 22006    |
+------------+-----------+----------+
4 rows in set (0.00 sec)

这里插入了一个 22006 但是班级表中没有这个班级,根据我们现实合理吗?显然不合理。

下面我们删除掉一个班级:

mysql> delete from class where class_id='22001';
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from class;
+----------+----------------+
| class_id | class_name     |
+----------+----------------+
| 22002    | 物联网02班     |
| 22003    | 计算机01班     |
+----------+----------------+
2 rows in set (0.00 sec)

这里删除成功了,也是不合理,所以我们需要添加外键约束:

但是在添加之前,我们需要删掉从表(student) 表,然后重新创建

mysql> create table student(
    -> Sid char(5) primary key,
    -> name varchar(12) not null,
    -> id char(5),
    -> foreign key(id) references class(class_id)
    -> );
Query OK, 0 rows affected (0.01 sec)
​
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sid   | char(5)     | NO   | PRI | NULL    |       |
| name  | varchar(12) | NO   |     | NULL    |       |
| id    | char(5)     | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
​

这里创建成功,那么现在重新插入:

mysql> insert into student(Sid, name, id) values('00001', '王熙凤', '22001');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student(Sid, name, id) values('00002', '贾宝玉', '22002');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student(Sid, name, id) values('00003', '贾母', '22003');
Query OK, 1 row affected (0.00 sec)
​
mysql> select * from student;
+-------+-----------+-------+
| Sid   | name      | id    |
+-------+-----------+-------+
| 00001 | 王熙凤    | 22001 |
| 00002 | 贾宝玉    | 22002 |
| 00003 | 贾母      | 22003 |
+-------+-----------+-------+
3 rows in set (0.00 sec)

这里插入成功了,那么下面插入班级表里面没有的班级:

mysql> insert into student(Sid, name, id) values('00004', '赵姨娘', '22004');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`restraint`.`student`, CONSTRAINT `fk_class_id` FOREIGN KEY (`id`) REFERENCES `class` (`class_id`))

这里插入失败了,下面删除掉任意一个班级:

mysql> delete from class where class_id='22001';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`restraint`.`student`, CONSTRAINT `fk_class_id` FOREIGN KEY (`id`) REFERENCES `class` (`class_id`))

上面两条语句都失败了,这就是外键约束的作用。

那么下面说一下我对外键约束的理解:

  • 表与表之间有关系,所以需要联系起来。

  • 但是只有联系没有用,还是需要约束,所以还有外键约束。

虽然外键约束时一个,但是外键约束是由联系和约束组成的。文章来源地址https://www.toymoban.com/news/detail-666143.html

到了这里,关于mysql 表的约束的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • mysql 表的约束

    目录 mysql 表的约束 NULL/NOT NULL DEFAULT comment zerofill PRIMARY KRY 删除主键 添加主键 复合主键 AUTO_INCREMENT UNIQUE KEY FOREIGN KEY 约束是 mysql 为了保证数据正确的一种手段,而前面在谈数据类型的时候,数据类型其实就是一种约束,但是 mysql 的约束可不仅仅是数据类型,mysql 有各种各样

    2024年02月11日
    浏览(38)
  • MySQL(表的约束)

    真正约束字段的是数据类型,但是数据类型约束很单一,需要有一些额外的约束, 更好的保证数据的合法性 ,从业务逻辑角度保证数据的正确性。比如有一个字段是email,要求是唯一的。 表的结构 两个值:null(默认的)和not null(不为空) 数据库默认字段基本都是字段为空,

    2023年04月16日
    浏览(28)
  • 【mysql】—— 表的约束

    目录 序言 (一)空属性 (二)默认值 (三)列描述 (四)zerofill (五)主键 (六)自增长 (七)唯一键 (八)外键 总结 真正约束字段的是数据类型,但是数据类型约束很单一,需要有一些额外的约束,更好的保证数据的合法性,从业务逻辑角度保证数据的正确性。比如

    2024年02月13日
    浏览(29)
  • MySQL:表的约束和基本查询

    表的约束——为了让插入的数据符合预期。 表的约束很多,这里主要介绍如下几个: null/not null,default, comment, zerofill,primary key,auto_increment,unique key 。 两个值:null(默认的)和not null(不为空) 数据库默认字段基本都是字段为空,但是实际开发时,尽可能保证字段不为空,因

    2024年02月13日
    浏览(37)
  • MySQL:数据类型和表的约束

    学习语言的时候首先就是学习数据类型,数据类型起着为变量合理分配空间的作用。数据库同样需要合理使用空间,其次还可以约束SQL语句的正确性,所以同样需要数据类型。 1.0. 数据类型分类 分类 数据类型 解释 数值类型 BIT (M) 位类型。M指定位数,默认为1,范围1-64 BOOL

    2024年02月05日
    浏览(41)
  • 一文学透MySQL表的创建和约束

    ✅作者简介:C/C++领域新星创作者,为C++和java奋斗中 ✨个人社区:微凉秋意社区 🔥系列专栏:MySQL一点通 📃推荐一款模拟面试、刷题神器👉注册免费刷题 🔥前言 上篇博客分享了 MySQL 的下载和环境变量的配置,并简单了创建了数据库和表。这篇博客将分享数据库的创建和

    2023年04月23日
    浏览(39)
  • MySQL--删除表的外键约束,简单易懂。

    删除表的外键约束 我们曾在第一章中讲过外键的作用,以及如何创建一个表的外键。建立了外键我们就建立起了两张表的关联关系,那如果我想删除主表呢?为了确保数据库的正确性,我们必须先解除两个表之间的关联关系,那就是删除外键约束啦!让我们先来看看删除外键

    2024年04月17日
    浏览(43)
  • {MySQL} 数据库约束& 表的关系& 新增&&删除& 修改& 查询

    提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 提示:这里可以添加本文要记录的大概内容: 承接上文,继续讲一下MySQL 提示:以下是本篇文章正文内容,下面案例可供参考 NOT NULL - 指示某列不能存储 NULL 值。 UNIQUE - 保证某列的每行必须有唯一的值

    2024年02月03日
    浏览(59)
  • 【MySQL】MySQL表的约束-空属性/默认值/列属性/zerofill/主键/自增长/唯一键/外键

    表的约束:表中一定要有各种约束,通过约束,让我们未来插入数据库表中的数据是符合预期的。约束的本质是通过计数手段,倒逼程序员,插入正确的数据,反过来,站在mysql的视角,凡是插入进来的数据,都是符合约束的 约束的最终模板:保证数据的完整性和可预期性

    2024年02月01日
    浏览(47)
  • 【lesson8】表的约束(1)

    真正约束字段的是数据类型,但是数据类型约束很单一,需要有一些额外的约束,更好的 保证数据的合法性 ,从业务逻辑角度 保证数据的正确性 。比如有一个字段是email,要求是唯一的。 表中一定要有各种约束,通过约束让我们未来插入数据库中的数据是符合预期的。 约

    2024年02月04日
    浏览(26)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包