Innodb 存储引擎学习笔记 -约束

最新推荐文章于 2022-02-26 15:51:53 发布

转载最新推荐文章于 2022-02-26 15:51:53 发布 · 243 阅读

MySQL 专栏收录该内容

19 篇文章

订阅专栏

博客介绍了数据库表约束的建立方式，一是在建立表时通过information_schema架构下的TABLE_CONSTRAINTS查看约束信息，二是使用alter table xxx add key。还提及了外键约束和ENUM约束，如ENUM可对表中性别类型规定域范围。

约束建立：

1）建立表时建立

mysql> create table u(
    -> id int,
    -> name varchar(30),
    -> id_card char(18),
    -> primary key(id),
    -> unique key(name));
Query OK, 0 rows affected (0.09 sec)

通过information_schema架构下的表 TABLE_CONSTRAINTS 来查看当前数据库下的所有约束信息。

mysql> select constraint_name,constraint_type
    -> from
    -> information_schema.TABLE_CONSTRAINTS
    -> where table_schema = 'constraints'and table_name = 'u';\G;
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| PRIMARY         | PRIMARY KEY     |
| name            | UNIQUE          |
+-----------------+-----------------+

2）alter table xxx add key

mysql> alter table u add unique key(id_card);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select constraint_name,constraint_type
    -> from
    -> information_schema.TABLE_CONSTRAINTS
    -> where table_schema = 'constraints'and table_name = 'u';\G;
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| PRIMARY         | PRIMARY KEY     |
| name            | UNIQUE          |
| id_card         | UNIQUE          |
+-----------------+-----------------+
3 rows in set (0.00 sec)

https://blog.youkuaiyun.com/championhengyi/article/details/78559789

外键约束

创建表p:

mysql> create table p(
    -> id int,
    -> u_id int,
    -> primary key(id),
    -> foreign key(u_id) references p (id));
Query OK, 0 rows affected (0.12 sec)

mysql> select * from
    -> information_schema.TABLE_CONSTRAINTS
    -> WHERE table_schema = 'constraints'and table_name ='p';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | constraints       | PRIMARY         | constraints  | p          | PRIMARY KEY     |
| def                | constraints       | p_ibfk_1        | constraints  | p          | FOREIGN KEY     |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+

ENUM约束

例如：

表上有一个性别类型，规定域的范围只能是male和female，这种情况下用户可以通过ENUM进行约束。

mysql> create table enum_t(
    -> id int,
    -> sex ENUM('male','female'));
Query OK, 0 rows affected (0.17 sec)

mysql> insert into enum_t select 1,'male';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from enum_t;
+------+------+
| id   | sex  |
+------+------+
|    1 | male |
+------+------+
1 row in set (0.00 sec)

mysql> insert into enum_t select 1,'ma';
ERROR 1265 (01000): Data truncated for column 'sex' at row 1