4.MySQL表的完整性约束

最新推荐文章于 2023-04-03 23:22:15 发布

咸蛋黄派

最新推荐文章于 2023-04-03 23:22:15 发布

阅读量280

点赞数 2

分类专栏： MYSQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/hanjinjuan/article/details/118526020

版权

MYSQL 专栏收录该内容

24 篇文章 2 订阅

订阅专栏

文章目录

MySQL表的完整性约束

MySQL表的完整性约束

作用：用于保证数据的完整性和一致性

约束条件	说明
PRIMARY KEY (PK)	标识该字段为该表的主键，可以`唯一`的标识记录，不可以为空 UNIQUE + NOT NULL
FOREIGN KEY (FK)	标识该字段为该表的外键，实现表与表之间的关联
NULL	标识是否允许为空，默认为NULL
NOT NULL	标识该字段不能为空，可以修改
UNIQUE KEY (UK)	标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT	标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT	为该字段设置默认值
UNSIGNED	无符号，正数

1、主键

每张表里只能有一个主键，不能为空，而且唯一，主键保证记录的唯一性，主键自动为NOT NULL。
一个 UNIQUE KEY 又是一个NOTNULL的时候，那么它被当做PRIMARY KEY主键。

定义两种方式：

表存在，添加约束
mysql> alter table t7 add primary key (hostname);

创建表并指定约束
mysql> create table t9(hostname char(20),ip char(150),primary key(hostname));

案例：

mysql> insert into t9(hostname,ip) values('qfedu.com', '10.10.10.11');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9(hostname,ip) values('qfedu.com', '10.10.10.12');
ERROR 1062 (23000): Duplicate entry 'qfedu.com' for key 'PRIMARY'

mysql> insert into t9(hostname,ip) values('qfedu', '10.10.10.11');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+-----------+-------------+
| hostname  | ip          |
+-----------+-------------+
| qfedu     | 10.10.10.11 |
| qfedu.com | 10.10.10.11 |
+-----------+-------------+
2 rows in set (0.00 sec)

mysql> insert into t9(hostname,ip) values('qfjy', '10.10.10.12');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t9;
+-----------+-------------+
| hostname  | ip          |
+-----------+-------------+
| qfedu     | 10.10.10.11 |
| qfedu.com | 10.10.10.11 |
| qfjy      | 10.10.10.12 |
+-----------+-------------+
3 rows in set (0.00 sec)

删除主键
mysql> alter table tab_name  drop  primary key;
主键被删除后，此字段仍然不允许为空值

2、auto_increment

自增（自动编号），且必须与主键组合使用默认情况下，起始值为1，每次的增量为1。

当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况：

如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；
如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。

注：每张表只能有一个字段为自增，成了key才可以自动增长

案例：

mysql> CREATE TABLE department3 (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(30),
    comment VARCHAR(50)
    );
    mysql> select * from department3;
Empty set (0.00 sec)

插入值
mysql> insert into department3(dept_name, comment) values('tom','test'), ('jack', 'test2');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from department3;
+---------+-----------+---------+
| dept_id | dept_name | comment |
+---------+-----------+---------+
|       1 | tom       | test    |
|       2 | jack      | test2   |
+---------+-----------+---------+
2 rows in set (0.00 sec)

删除自动增长
mysql> ALTER TABLE department3 CHANGE dept_id  dept_id INT NOT NULL;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc department3;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | NO   | PRI | NULL    |       |
| dept_name | varchar(30) | YES  |     | NULL    |       |
| comment   | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

再次插入数据，报错
mysql> insert into department3(dept_name,comment) values('tom','test1'),('jack','test2');
ERROR 1364 (HY000): Field 'dept_id' doesn't have a default value

3、设置唯一约束 UNIQUE

字段添加唯一约束之后，该字段的值不能重复，也就是说在一列当中不能出现一样的值。

案例：

mysql> create table department2(
    -> dept_id int,
    -> dept_name varchar(30) unique,
    -> comment varchar(50));
Query OK, 0 rows affected (0.01 sec)

mysql> desc department2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(30) | YES  | UNI | NULL    |       |
| comment   | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into department2 values(1,'tom','good');
Query OK, 1 row affected (0.00 sec)

重复使用同一名字，提示错误
mysql> insert into department2 values(2,'tom','good');
ERROR 1062 (23000): Duplicate entry 'tom' for key 'dept_name'

mysql> insert into department2 values(2,'jack','good');
Query OK, 1 row affected (0.00 sec)

mysql> insert into department2(dept_id,comment) values(2,'good');
Query OK, 1 row affected (0.00 sec)

mysql> select * from department2;
+---------+-----------+---------+
| dept_id | dept_name | comment |
+---------+-----------+---------+
|       1 | tom       | good    |
|       2 | jack      | good    |
|       2 | NULL      | good    |
+---------+-----------+---------+
3 rows in set (0.00 sec)

4、null与not null

是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 sex enum(‘male’,‘female’) not null default ‘male’
#只能选择male和female，不允许为空，默认是male

案例：

mysql> create table t4(id int(5),name varchar(10),sex enum('male','female') not null default 'male');
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t4(id,name) values(1,'tom');
mysql> select * from t4;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | tom  | male |
+------+------+------+
1 row in set (0.00 sec)
允许为null
mysql> create table t1(id int(5),name varchar(10),age int(5));
Query OK, 0 rows affected (0.00 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(5)      | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(5)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into t1(id,name) values(1,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | tom  | NULL |
+------+------+------+
1 row in set (0.00 sec)

5、默认约束

添加/删除默认约束

语法：

1.设置默认值

alter table 表名 alter 字段名 set default 默认值;

2.删除默认值

alter table 表名 alter 字段名 drop default;

3.插入值

alter table 表名 change 原字段名现字段名字段类型约束;

案例：

1.创建一个表
mysql> create table user(id int not null, name varchar(20), number int, primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| number | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2、设置默认值
mysql> alter table user alter number set default 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| number | int(11)     | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


3、插入值
mysql> alter table user change id id int not null auto_increment;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert  into user(name) values('rock'); 
Query OK, 1 row affected (0.00 sec)

mysql> insert into user(name) values('rock');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------+--------+
| id | name | number |
+----+------+--------+
|  1 | rock |      0 |
|  2 | rock |      0 |
+----+------+--------+
2 rows in set (0.00 sec)

删除默认值
mysql> alter table user alter number drop default;

6、扩展：

指定字符集

修改字符集：在创建表的最后面指定一下： default charset=utf8 （可以指定中文)

* 未指定之前，插入
mysql> insert into t1(id,name) values(1,'石宇飞');
ERROR 1366 (HY000): Incorrect string value: '\xE7\x9F\xB3\xE5\xAE\x87...' for column 'name' at row 1

* 创建表格式指定字符集为utf-8
mysql> create table t6(id int(2),name char(5),age int(4)) default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> desc t6;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(2)  | YES  |     | NULL    |       |
| name  | char(5) | YES  |     | NULL    |       |
| age   | int(4)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t6(id,name) values(1,'石宇飞');
Query OK, 1 row affected (0.00 sec)

change和modify 的区别

1、change可以给字段重命名，modify不可以。

2、当想要更改列的类型而不是名称，change语法仍然要求旧的和新的列名称，即使旧的和新的列名称是一样的。而用modify来改变列的类型，此时不需要重命名。

change
语法：alter table 表名  change 原字段名 现字段名 字段类型  约束 ;

modify
语法：alter table 表名  modify  字段名 字段类型 约束 ;

咸蛋黄派

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
4.MySQL表的完整性约束

MySQL表的完整性约束作用：用于保证数据的完整性和一致性约束条件说明PRIMARY KEY (PK)标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE + NOT NULLFOREIGN KEY (FK)标识该字段为该表的外键，实现表与表之间的关联NULL标识是否允许为空，默认为NULLNOT NULL标识该字段不能为空，可以修改UNIQUE KEY (UK)标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE K
复制链接

扫一扫

专栏目录