数据库—六种约束

最新推荐文章于 2024-05-08 05:00:00 发布

丧尸会跳小芭蕾

最新推荐文章于 2024-05-08 05:00:00 发布

阅读量3.3k

点赞数 4

文章标签：数据库

本文链接：https://blog.csdn.net/qq_26725075/article/details/70217138

版权

以下是我从相关资源网站学习的一些总结

约束保证数据的完整性和一致性
约束分为表级约束和列级约束

如果约束只针对某一个字段来使用，称为列级约束
如果约束针对两个或者两个以上的字段来使用，称为表级约束

数据库中的六种约束：主键约束（Primay Key）、唯一约束（Unique）、检查约束（Check）、默认约束（Default）、外键约束（Foreign Key），非空（NOT NULL）约束

主键约束

这里写图片描述

首先我们来创建一个表

mysql> CREATE TABLE tb1(
    -> id SMALLINT AUTO_INCREMENT,
    -> username VARCHAR(30) NOT NULL
    -> );

这时会出现一个错误

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

现在，我们来分析以下原因
先看看MySQL文档是怎么介绍AUTO_INCREMENT的：
Using AUTO_INCREMENT

然后我们加上主键

mysql> CREATE TABLE tb1(
    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected

再看看表结构

mysql> SHOW COLUMNS FROM tb1;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
2 rows in set

可见主键自动为非空

现在我们插入几条数据

mysql> INSERT tb1(username) VALUES('Tom');
mysql> INSERT tb1(username) VALUES('Scarlett');
mysql> INSERT tb1(username) VALUES('Anna');
mysql> INSERT tb1(username) VALUES('Rose');

在查看一下表

mysql> SELECT * FROM tb1;
+----+----------+
| id | username |
+----+----------+
|  1 | Tom      |
|  2 | Scarlett |
|  3 | Anna     |
|  4 | Rose     |
+----+----------+
4 rows in set

可见id是从1开始自增的，可以保证记录的唯一性
那么能不能不从1开始自增呢答案是可以的
我们设置AUTO_INCREMENT

mysql> ALTER TABLE tb1 AUTO_INCREMENT = 50;

插入两个数据以后，查看表

mysql> SELECT * FROM tb1;
+----+----------+
| id | username |
+----+----------+
|  1 | Tom      |
|  2 | Scarlett |
|  3 | Anna     |
|  4 | Rose     |
| 50 | Jhon     |
| 51 | Dave     |
+----+----------+
6 rows in set

现在我们不设置AUTO_INCREMENT，创建新的表

mysql> CREATE TABLE tb2(
    -> id SMALLINT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL
    -> );

查看数据表的基本结构

mysql> SHOW COLUMNS FROM tb2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | smallint(6) | NO   | PRI | NULL    |       |
| username | varchar(30) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set

插入两条数据，然后查询表

mysql> INSERT tb2 VALUES(5,'Tom');
mysql> INSERT tb2 VALUES(20,'Rose');

mysql> SELECT * FROM tb2;
+----+----------+
| id | username |
+----+----------+
|  5 | Tom      |
| 20 | Rose     |
+----+----------+
2 rows in set

可见，主键的字段是可以赋值的
那么，可不可以是相同的呢？

mysql> INSERT tb2 VALUES(20,'Anmmy');
1062 - Duplicate entry '20' for key 'PRIMARY'

出现错误，主键不允许重复

以上说明了：
AUTO_INCREMENT 必须和主键一起使用，但是主键不一定和AUTO_INCREMENT 一起使用

唯一约束

这里写图片描述
现在创建一个既有主键约束又有唯一约束的数据表

mysql> CREATE TABLE tb3(
    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL UNIQUE KEY,
    -> age TINYINT
    -> );

mysql> SHOW COLUMNS FROM tb3;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | smallint(6) | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | NO   | UNI | NULL    |                |
| age      | tinyint(4)  | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set

接下来插入数据

mysql> INSERT tb3(username,age) VALUES('Rose',18);
Query OK, 1 row affected
mysql> INSERT tb3(username,age) VALUES('Rose',22);
1062 - Duplicate entry 'Rose' for key 'username'

在进行第二次插入的时候出现了错误，证明UNIQUE 保证了唯一性

主键约束和唯一约束最主要的区别是：
主键约束在一张表中只能有一个，但是唯一约束在一张表中可以有多个。

默认约束

当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

创建一个新表

mysql> CREATE TABLE tb4(
    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL UNIQUE KEY,
    -> sex ENUM('1','2','3') DEFAULT '3'
    -> );
Query OK, 0 rows affected
mysql> SHOW COLUMNS FROM tb4;
+----------+-------------------+------+-----+---------+----------------+
| Field    | Type              | Null | Key | Default | Extra          |
+----------+-------------------+------+-----+---------+----------------+
| id       | smallint(6)       | NO   | PRI | NULL    | auto_increment |
| username | varchar(30)       | NO   | UNI | NULL    |                |
| sex      | enum('1','2','3') | YES  |     | 3       |                |
+----------+-------------------+------+-----+---------+----------------+
3 rows in set

在性别中，1表示男，2表示女，3表示保密
在插入数据的时候，如果没有指明性别，则赋予默认值为3保密

mysql> INSERT tb4(username) VALUES('Rose');
Query OK, 1 row affected
mysql> SELECT * FROM tb4;
+----+----------+-----+
| id | username | sex |
+----+----------+-----+
|  1 | Rose     | 3   |
+----+----------+-----+
1 row in set

外键约束

首先外检约束有一些要求：
1.数据表的存储引擎为InnoDB
2.外键列（子表）和参照列（父表）必须有具有相似的数据类型
是数字的话，长度和有无符号位都必须相同
是字符的话，长度可以不相同
3.外键列和参照列必须必须创建索引。如果外键列不存在索引，MySQL自动创建索引

mysql> CREATE TABLE province(
    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    -> pname VARCHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected

mysql> SHOW CREATE TABLE province;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                  |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| province | CREATE TABLE `province` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `pname` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

可以看到ENGINE=InnoDB，满足了条件一

下面看条件二

mysql> CREATE TABLE users(
    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(10) NOT NULL,
    -> pid BIGINT,
    -> FOREIGN KEY(pid) REFERENCES province(id)
    -> );
1215 - Cannot add foreign key constraint

这个错误说明了，外键pid和父表的数据类型不一致(注意，有无符号位也要相同)
父表中的id为SMALLINT ，子表的pid为BIGINT
从而无法创建pid为外键

下面就可以成功

mysql> CREATE TABLE users(
    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT,
    -> FOREIGN KEY(pid) REFERENCES province(id)
    -> );
Query OK, 0 rows affected

下面再看条件三

mysql> SHOW INDEXES FROM province;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| province |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

此时province的id上已经有了索引
下面看看users的索引

mysql> SHOW INDEXES FROM users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| users |          1 | pid      |            1 | pid         | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

除了我们设置为主键的id以外，系统自动的给pid创建了索引

检查约束

CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

mysql> CREATE TABLE tb5(
    -> id INT NOT NULL,
    -> username VARCHAR(10) NOT NULL
    -> CHECK (id > 0)
    -> );
Query OK, 0 rows affected
mysql> INSERT tb5(id,username) VALUES(-1,'Tom');
Query OK, 1 row affected
mysql> SELECT * FROM tb5;
+----+----------+
| id | username |
+----+----------+
| -1 | Tom      |
+----+----------+
1 row in set

check约束在MySQL中不起作用

丧尸会跳小芭蕾

关注

4
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
数据库—六种约束

以下是我从相关资源网站学习的一些总结约束保证数据的完整性和一致性约束分为表级约束和列级约束如果约束只针对某一个字段来使用，称为列级约束如果约束针对两个或者两个以上的字段来使用，称为表级约束数据库中的六种约束：主键约束（Primay Key）、唯一约束（Unique）、检查约束（Check）、默认约束（Default）、外键约束（Foreign Key），非空（NOT NULL）约束主键约束首
复制链接

扫一扫