mysql数据库-----表的约束_数据库设计了唯一约束我插入一样的会咋样-CSDN博客

本文详细介绍了MySQL中表的约束，包括默认约束、非空约束、主键约束和唯一约束的使用方法和注意事项。默认约束允许为字段指定默认值，非空约束确保字段值不为NULL，主键约束则结合了唯一性和非空性，用于标识记录的唯一性。此外，还讨论了如何为现有表添加或删除这些约束。

为了防止数据表中插入错误的数据，MySQL定义了一些维护数据库完整性的规则，即表的约束。常见约束分为5种，分别是默认约束、非空约束、主键约束、唯一约束和外键约束。外键约束比较复杂，涉及多表操作，将在后面的章节中讲解，本节主要讲解其余4种约束的使用方法。
1.2.1默认约束
默认约束用于为数据表中的字段指定默认值，即当在表中插入一条新记录时，如果没有给这个字段赋值，那么，数据库系统会自动为这个字段插入默认值。默认值是通过DEFAULT关键字定义的，其基本语法格式如下。
字段名数据类型 DEFAULT默认值;
需要注意的是，BLOB、TEXT数据类型不支持默认约束。下面通过案例演示默认约束的使用及注意事项。
(1)创建my_default表，准备name和age两个字段进行测试，为age添加默认约束，设置默认值为18。

mysql> CREATE TABLE my_default (
    ->   name VARCHAR(10),
    ->   age INT UNSIGNED DEFAULT 18
    -> );

(2)使用DESC查看表结构，结果如下所示。…

mysql> DESC my_default;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | varchar(10)      | YES  |     | NULL    |       |
| age   | int(10) unsigned | YES  |     | 18      |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

(3)插入记录进行测试，具体SQL语句及执行结果如下。
① 在插入记录时省略name和age字段

mysql> INSERT INTO my_default VALUES();
Query OK, 1 row affected (0.00 sec)

② 在插入记录时省略age字段

mysql> INSERT INTO my_default (name) VALUES('a');
Query OK, 1 row affected (0.00 sec)

③ 在age字段中插入NULL值

mysql> INSERT INTO my_default VALUES('b', NULL);
Query OK, 1 row affected (0.00 sec)

④ 在age字段中使用默认值

mysql> INSERT INTO my_default VALUES('c', DEFAULT);
Query OK, 1 row affected (0.00 sec)

⑤ 查询结果

mysql> SELECT * FROM my_default;
+------+------+
| name | age  |
+------+------+
| NULL |   18 |
| a    |   18 |
| b    | NULL |
| c    |   18 |
+------+------+
4 rows in set (0.00 sec)

在上述示例中，由于name和age字段没有设置非空约束，在插入记录时省略了这两个字段的值，则分别使用默认值NULL和18。为age字段设置默认值18后，插入NULL值，则保存结果为NULL，不使用默认值。在为有默认值的字段指定数据时，可以通过DEFAULT关键字直接指定其使用默认值。
(4)为现有的表添加或删除默认约束，具体SQL语句及执行结果如下。
① 删除默认约束

mysql> ALTER TABLE my_default MODIFY age INT UNSIGNED;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

② 添加默认约束

mysql> ALTER TABLE my_default MODIFY age INT UNSIGNED DEFAULT 18;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

通过上述示例可以看出，使用ALTERTABLE修改列属性即可添加或删除默认约束。

1.2.2非空约束
非空约束指的是字段的值不能为NULL，在MySQL中，非空约束是通过NOTNULL定义的，其基本语法格式如下。字段名数据类型NOT NULL;
为了让读者更好地理解，下面通过案例演示非空约束的使用及注意事项。
(1)创建my_ not_ null表，准备n1、n2和n3字段进行测试，为n2和n3设置非空约束，为n3设置默认值为18。

mysql> CREATE TABLE my_not_null (
    ->   n1 INT,
    ->   n2 INT NOT NULL,
    ->   n3 INT NOT NULL DEFAULT 18
-> );

(2)使用DESC查看表结构，结果如下所示。

mysql> DESC my_not_null;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| n1    | int(11) | YES  |     | NULL    |       |
| n2    | int(11) | NO   |     | NULL    |       |
| n3    | int(11) | NO   |     | 18      |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

在上述结果中，Null列的值为NO表示该字段添加了非空约束。需要注意的是，添加了非空约束的n2字段的Default为NULL，表示未给该字段设置默认值，而不能将其理解为默认值为NULL，否则在插入数据时，若n2字段为NULL， MySQL会报“Column ‘n2’ cannot be null"错误提示。另外，在创建数据表时，非空约束与值为NULL的默认约束(DEFULTNULL)不能同时存在，否则数据表在创建时会失败，提示“Invaliddefaultvalue for 'n2"错误。
(3)插入记录进行测试，具体SQL语句及执行结果如下。
① 省略n2字段，插入失败，提示n2没有默认值

mysql> INSERT INTO my_not_null VALUES();
ERROR 1364 (HY000): Field 'n2' doesn't have a default value

② 将n2字段设为NULL，插入失败，提示n2字段不能为NULL

mysql> INSERT INTO my_not_null VALUES(NULL, NULL, NULL);
ERROR 1048 (23000): Column 'n2' cannot be null

③ 将n3字段设为NULL，插入失败，提示n3字段不能为NULL

mysql> INSERT INTO my_not_null VALUES(NULL, 20, NULL);
ERROR 1048 (23000): Column 'n3' cannot be null

④ 省略n1和n3字段，插入成功

mysql> INSERT INTO my_not_null (n2) VALUES(20);
Query OK, 1 row affected (0.00 sec)

⑤ 查询结果

mysql> SELECT * FROM my_not_null;
+------+----+----+
| n1   | n2 | n3 |
+------+----+----+
| NULL | 20 | 18 |
+------+----+----+
1 row in set (0.00 sec)

在上述示例中，由于n2字段不能为NULL且没有默认值，在插入时不能插入NULL或省略该字段；n3字段设置了默认值，在插入时可以省略该字段，但不能插入NULL。

提示:为现有的表添加或删除非空约束的方式与默认约束类似，使用ALTER TABLE修改列属性即可。但若目标列中已经保存了NULL值，添加非空约束会失败，提示"Invalid use of NULL value"，只要将NULL值改为其他值即可解决。

1.2.3唯一约束
唯一约束用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。唯一约束是通过UNIQUE定义的，其基本语法格式如下所示。

列级约束

字段名数据类型 UNIQUE;

表级约束

UNIQUE(字段名1, 字段名2, …);

在上述语法格式中，列级约束和表级约束是MySQL中的两种定义约束的方式。列级约束定义在一个列上，只对该列起约束作用；表级约束是独立于列的定义，可以应用在一个表的多个列上。
为了让读者更好地理解，下面通过案例演示唯一约束的使用及注意事项。
(1)创建my_unique_1表和my_unique_2表，分别通过列级约束和表级约束的方式添加唯一约束。具体SQL语句和执行结果如下。

列级约束

mysql> CREATE TABLE my_unique_1 (
    ->   id INT UNSIGNED UNIQUE,
    ->   username VARCHAR(10) UNIQUE
    -> );
Query OK, 0 rows affected (0.01 sec)

表级约束

mysql> CREATE TABLE my_unique_2 (
    ->   id INT UNSIGNED,
    ->   username VARCHAR(10),
    ->   UNIQUE(id),
    ->   UNIQUE(username)
    -> );
Query OK, 0 rows affected (0.01 sec)

接着使用DESC查看my_unique_1表和my_unique_2表的结构，会发现两个表的结构是相同的，如下所示。

+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(10) unsigned | YES  | UNI | NULL    |       |
| username | varchar(10)      | YES  | UNI | NULL    |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

在上述结果中，如果在id和username的Key列看到UNI，说明唯一约束已经添加成功，这两个字段是唯一键。值得一提的是，当表级约束仅建立在一个字段上时，其作用效果与列级约束相同。
(2)为含唯一约束的字段插入记录，具体SQL语句及执行结果如下。
① 插入不重复记录，插入成功

mysql> INSERT INTO my_unique_1 (id) VALUES(1);
Query OK, 1 rows affected (0.01 sec)
mysql> INSERT INTO my_unique_1 (id) VALUES(2);
Query OK, 1 rows affected (0.01 sec)

② 插入重复记录，插入失败

mysql> INSERT INTO my_unique_1 (id) VALUES(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

③ 查询插入的结果

mysql> SELECT * FROM my_unique_1;
+------+----------+
| id   | username |
+------+----------+
|    1 | NULL     |
|    2 | NULL     |
+------+----------+
2 rows in set (0.00 sec)

从上述结果可以看出，添加唯一约束后，插入重复记录会失败。其中，username字段出现了重复值NULL，这是因为MySQL的唯一约束允许存在多个NULL值。
(3)添加和删除唯一性约束。若为一个现有的表添加或删除唯一约束，无法通过修改字段属性的方式操作，而是按照索引的方式来操作。关于索引的概念和使用会在后面的章节中详细讲解，读者此时只需了解用到的这些操作即可。具体SQL语句及执行结果如下。
① 创建测试表

mysql> CREATE TABLE my_unique_3 (id INT);
Query OK, 0 rows affected (0.01 sec)

② 添加唯一约束

mysql> ALTER TABLE my_unique_3 ADD UNIQUE(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

③ 查看添加结果

mysql> SHOW CREATE TABLE my_unique_3\G
*************************** 1. row ***************************
       Table: my_unique_3
Create Table: CREATE TABLE `my_unique_3` (
  `id` int(11) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

④ 删除唯一约束

mysql> ALTER TABLE my_unique_3 DROP INDEX id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑤ 查看删除结果

mysql> SHOW CREATE TABLE my_unique_3\G
*************************** 1. row ***************************
       Table: my_unique_3
Create Table: CREATE TABLE `my_unique_3` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

在上述操作中，第③步的执行结果中出现了“UNIQUE KEY id(id)”，它是添加唯一约束的完整语法，即UNIQUE(id)的完整形式，如下所示。
UNIQUE KEY 索引名(字段列表)
上述语法表示在添加唯一约束时创建索引，用于加快查询速度。其中，索引名可以自己指定，也可以省略，MySQL会自动使用字段作为索引名。当需要对索引进行删除时，需要指定这个索引名。
(4)创建复合唯一约束。在表级唯一性约束创建时， UNIQUE(的字段列表中，可以添加多个字段，组成复合唯一键，其特点是只有多个字段的值相同时才视为重复记录。具体SQL语句及执行结果如下。
① 创建测试表，添加复合唯一键

mysql> CREATE TABLE my_unique_4 (
    ->   id INT UNSIGNED, username VARCHAR(10),
    ->   UNIQUE(id, username)
    -> );
Query OK, 0 rows affected (0.01 sec)

② 插入不重复记录，插入成功

mysql> INSERT INTO my_unique_4 VALUES(1, '2');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO my_unique_4 VALUES(1, '3');
Query OK, 1 row affected (0.00 sec)

③ 插入重复记录，插入失败

mysql> INSERT INTO my_unique_4 VALUES(1, '2');
ERROR 1062 (23000): Duplicate entry '1-2' for key 'id'

从上述结果可以看出，当同一个字段两次插入的记录相同时，插入成功，只有当两个字段同时发生重复时，插入记录失败。

1.2.4主键约束
在MySQL中，为了快速查找表中的某条信息，可以通过设置主键来实现。主键可以唯一标识表中的记录，类似指纹、身份证用于标识人的身份一样。
主键约束通过PRIMARY KEY定义，它相当于唯一约束和非空约束的组合，要求被约束字段不允许重复，也不允许出现NULL值，每个表最多只允许含有一个主键。
主键约束的创建也分为列级和表级。其基本语法格式如下。.

#列级约束字段名数据类型 PRIMARY KEY
#表级约束 PRIMARY KEY (字段名1，字段名2，…)

在上述语法中，表级约束的字段若只有一个，则为单字段主键与列级约束添加的效果相同；若有多个，则为复合主键。复合主键需要用多个字段来确定一条记录的唯一性，类似于复合唯一键。
为了让读者更好地理解，下面通过案例演示主键约束的使用及注意事项。.
(1)创建my_primary表，为id字段添加主键约束。

mysql> CREATE TABLE my_primary (
    ->   id INT UNSIGNED PRIMARY KEY,
    ->   username VARCHAR(20)
    -> );

(2)使用DESC查看表结构，执行结果如下。

mysql> DESC my_primary;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(10) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

从上述结果可以看出，id字段的Key列为PRI，表示该字段为主键。同时，id字段的Null列为NO，表示该字段不能为NULL。
(3)插入记录进行测试，具体SQL语句及执行结果如下。
① 插入测试记录，插入成功

mysql> INSERT INTO my_primary VALUES(1, 'Tom');
Query OK, 1 row affected (0.00 sec)

② 为主键插入NULL值，插入失败

mysql> INSERT INTO my_primary VALUES(NULL, 'Jack');
ERROR 1048 (23000): Column 'id' cannot be null

③ 为主键插入重复值，插入失败

mysql> INSERT INTO my_primary VALUES(1, 'Alex');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

从上述结果可以看出，添加主键约束后，插入重复值或NULL值会失败。
(4)为一个现有的表添加或删除主键约束，具体SQL语句及执行结果如下。
① 删除主键约束

mysql> ALTER TABLE my_primary DROP PRIMARY KEY;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

② 查看删除结果

mysql> DESC my_primary;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(10) unsigned | NO   |     | NULL    |       |
| username | varchar(20)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

③ 删除id字段的非空约束（根据需要）

mysql> ALTER TABLE my_primary MODIFY id INT UNSIGNED;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

④ 添加主键约束

mysql> ALTER TABLE my_primary ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑤ 查看添加结果

mysql> DESC my_primary;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | int(10) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)      | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

从上述结果可以看出，在删除id字段的主键约束后，该字段的非空约束并没有被同时删除。若需要删除id字段的非空约束，执行第③步删除操作即可。

1.3自动增长
在为数据表设置主键约束后，每次插入记录时，都需要检查主键的值，防止插入的值重复导致插入失败，这会给数据库的使用带来很多麻烦。为此，可以利用MySQL提供的自动增长功能来自动生成主键的值。
自动增长功能通过AUTO_INCREMENT来实现，其基本语法格式如下。

字段名 数据类型 AUTO_INCREMENT

在使用AUTO_INCREMENT时，需要注意以下4点。
(1)一个表中只能有一个自动增长字段，该字段的数据类型是整数类型，且必须定义为键，如UNIQUE KEY PRIMARY KEY。
(2)若为自动增长字段插入NULL、DEFAULT或在插入时省略该字段，则该字段就会使用自动增长值；若插入的是一个具体值，则不会使用自动增长值。
(3)自动增长值从1开始自增，每次加1。若插入的值大于自动增长的值，则下次插入的自动增长值会自动使用最大值加1；若插入的值小于自动增长值，则不会对自动增长值产生影响。
(4)使用DELETE删除记录时，自动增长值不会减小或填补空缺。
为了让读者更好地理解，下面通过案例演示自动增长的使用及注意事项。
(1)创建my_auto表，为id字段添加自动增长。

mysql> CREATE TABLE my_auto (
    ->   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ->   username VARCHAR(20)
    -> );

(2)使用DESC查看表结构，执行结果如下。

mysql> DESC my_auto;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

(3)插入记录进行测试，具体SQL语句及执行结果如下。
① 插入时省略id字段，将会使用自动增长值

mysql> INSERT INTO my_auto (username) VALUES('a');
Query OK, 1 row affected (0.00 sec)

② 为id字段插入NULL，将会使用自动增长值

mysql> INSERT INTO my_auto VALUES(NULL, 'b');
Query OK, 1 row affected (0.00 sec)

③ 为id字段插入具体值6

mysql> INSERT INTO my_auto VALUES(6, 'c');
Query OK, 1 row affected (0.00 sec)

④ 为id字段插入0，使用自动增长值

mysql> INSERT INTO my_auto VALUES(0, 'd');
Query OK, 1 row affected (0.00 sec)

(4)查看my_ auto表中的数据.执行结果如下。

mysql> SELECT * FROM my_auto;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  6 | c        |
|  7 | d        |
+----+----------+
4 rows in set (0.00 sec)

在上述结果中，最后一条记录的id字段在插入时使用了0，MySQL会忽略该值，使用自动增长值(即id最大值6进行加1)，从而得到id的值为7。
(5)使用SHOW CREATE TABLE查看自动增长值，执行结果如下。

mysql> SHOW CREATE TABLE my_auto\G
*************************** 1. row ***************************
       Table: my_auto
Create Table: CREATE TABLE `my_auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

在上述结果中，"AUTO_ INCREMENT=8"用于指定下次插入的自动增长值为8。若在下次插入时指定了大于8的值，此处的8会自动更新为下次插入值加1。
(6)为现有的表修改或删除自动增长，具体SQL语句及执行结果如下。
① 修改自动增长值

mysql> ALTER TABLE my_auto AUTO_INCREMENT = 10;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

② 删除自动增长

mysql> ALTER TABLE my_auto MODIFY id INT UNSIGNED;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

③ 重新为id添加自动增长

mysql> ALTER TABLE my_auto MODIFY id INT UNSIGNED AUTO_INCREMENT;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

需要注意的是，在为字段删除自动增长并重新添加自动增长后，自动增长的初始值会自动设为该列现有的最大值加1。在修改自动增长值时，修改的值若小于该列现有的最大值，则修改不会生效。

提示:
通过“SHOW VARIABLES LIKE 'auto increment%’;"可以查看MySQL中用于维护自动增长的变量分别是auto _increment_increment(默认值1)和auto_increment_offset(默认值为1)，通过更改这两个变量可以改变自动增长的计算方式，读者可以参考官方手册中的详细说明。

超全面的测试IT技术课程，0元立即加入学习！有需要的朋友戳：

腾讯课堂测试技术学习地址

作者：kellyred

出处：https://blog.csdn.net/kellyred

欢迎转载，但未经作者同意请保留此段声明，并在文章页面明显位置给出原文链接。