MySQL建表约束

工业解决方案 2021-09-22

319

--主键约束

它能够唯一确定一张表中的一条记录，也就是我们给某个字段添加主键约束，能够使该字段唯一且不为空。

在创建表的时候：

mysql> create table BOOK (
    -> name varchar(20) primary key,  // primary key即为主键约束，使得name字段唯一且不能为空
    -> owner varchar(20),
    -> death date);
# 联合主键：mysql> create table BOOK (
    -> name varchar(20),
    -> owner varchar(20),
    -> death date),
    -> primary key(name,owner));  // 只要主键name和主键owner加起来和其它数据记录不相同就可以，当然也不能为空。

--自增约束

跟随记录的增加而自增

mysql> create table users(
    -> ID int primary key auto_increment,  # 自增约束和主键结合
    -> name varchar(20));
Query OK, 0 rows affected (0.17 sec)
# 查看表结构
mysql> desc users;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| ID    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 插入数据
mysql> insert into users (name) values('张三');
mysql> insert into users (name) values('李四');
# 查看数据
mysql> select * from users;
+----+------+
| ID | name |
+----+------+
|  2 | 张三 |
|  3 | 李四 |
+----+------+
2 rows in set (0.00 sec)

--唯一约束

约束修饰的字段不可重复

mysql> create table test2(
    -> id int,
    -> name varchar(20) unique
    -> );
Query OK, 0 rows affected (0.21 sec)
# 查看表结构
mysql> desc test2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 插入2条重复数据记录
mysql> insert into test2 values(1,'张三');
Query OK, 1 row affected (0.07 sec)
mysql> insert into test2 values(1,'张三');
ERROR 1062 (23000): Duplicate entry '张三' for key 'name'

--非空约束

约束修饰的字段不能为空NULL

mysql> create table test3(
    -> id int,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.18 sec)
# 查看表结构
mysql> desc test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 插入空字段值
mysql> insert into test3 (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

--默认约束

修饰的字段如果没有传值就会使用默认值

mysql> create table test4(
    -> id int,
    -> name varchar(20),
    -> age int default 25
    -> );
Query OK, 0 rows affected (0.13 sec)
# 查看表结构
mysql> desc test4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL     |       |
| age   | int(11)     | YES  |     | 25       |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 该字段插入空数据
mysql> insert into test4 (id,name) values(1,'张三');
Query OK, 1 row affected (0.10 sec)
# 查看表记录
mysql> select * from test4;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   25 |
+------+------+------+
1 row in set (0.00 sec)

--外键约束

涉及到两个表：父表和子表

# 创建一个班级表
mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.14 sec)
# 创建一个学生表
mysql> create table students(
    -> id int primary key,
    -> name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)
    -> );
Query OK, 0 rows affected (0.21 sec)
# 查看表结构
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 查看表结构
mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 插入classes表数据
mysql> insert into classes values(1,'一班');
mysql> insert into classes values(2,'二班');
mysql> insert into classes values(3,'三班');
mysql> insert into classes values(4,'四班');
Query OK, 1 row affected (0.06 sec)
# 查看classes表数据
mysql> select * from classes;
+----+------+
| id | name |
+----+------+
|  1 | 一班 |
|  2 | 二班 |
|  3 | 三班 |
|  4 | 四班 |
+----+------+
4 rows in set (0.00 sec)
# 插入students表数据
mysql> insert into students values(001,'张三',1);
       insert into students values(002,'李四',2);
       insert into students values(003,'王五',3);
       insert into students values(004,'老六',4);
       Query OK, 4 row affected (0.05 sec)
# 查看表记录
mysql> select * from students;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | 张三 |        1 |
|  2 | 李四 |        2 |
|  3 | 王五 |        3 |
|  4 | 老六 |        4 |
+----+------+----------+
4 rows in set (0.00 sec)
# 插入父表中没有的引用字段值
mysql> insert into students values(005,'张七',5);
ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`text`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

--1.父表classes中没有的数据值，在子表中是不可以被使用的

--2.父表中的记录被子表引用，那么父表的记录将不能删除。

假设在建表的时候忘记创建主键，如何修改？

mysql> create table test1(
    -> ID int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.18 sec)
# 查看表结构
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 修改表结构
mysql> alter table test1 add primary key(id);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 再次查看
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

--假设主键设置错误如何删除？

mysql> alter table test1 drop primary key;
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 再次查看
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

PyCharm专业版工具，感觉还是非常不错的，公众号回复"PyCharm"获取插件下载链接，定时重置试用时间，使工具一直处于试用状态，内附安装教程！！！

mysql

文章转载自工业解决方案，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL建表约束

评论