当前位置:Gxlcms > 数据库问题 > MySQL的约束

MySQL的约束

时间:2021-07-01 10:21:17 帮助过:5人阅读

:当用户向表中插入数据时,指定了该字段的那么就插入该值,否则就插入默认值。

技术分享图片
mysql> create table test(id int(6) primary key auto_increment,name varchar(10) not null default 未知,addr varchar(24) not null default 未知);
Query OK, 0 rows affected (0.07 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(6)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | 未知    |                |
| addr  | varchar(24) | NO   |     | 未知    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into test values(1,科比,湖北罗田);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(name) values(毛线);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values();   
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+--------+--------------+
| id | name   | addr         |
+----+--------+--------------+
|  1 | 科比   | 湖北罗田     |
|  2 | 毛线   | 未知         |
|  3 | 未知   | 未知         |
+----+--------+--------------+
3 rows in set (0.00 sec)
默认值实例

 

2.非空(not null):所谓的非空就是字段的名不能输入为null(大小写无所谓),非空不是说不能没有值,也不是说不能为空格。

技术分享图片
mysql> create table weiyi(id int(4) primary key,name char(10) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> desc weiyi;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | NO   | PRI | NULL    |       |
| name  | char(10) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
name不能为空 技术分享图片
mysql> insert into weiyi(id) values(1);  #指定非空可以不输
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into weiyi(id,name) values(2, );  #可以为空格
Query OK, 1 row affected (0.01 sec)

mysql> insert into weiyi(id,name) values(3,Null);
ERROR 1048 (23000): Column name cannot be null  #不能为空
测试 技术分享图片
mysql> create table weiyi2(id int(4),name char(10));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into weiyi2(id,name) values(1,NULL);
Query OK, 1 row affected (0.05 sec)
不指定非空可以输入NULL

 

3.唯一unique:在这个表格中,设置了unique的这个字段,所有的值只能出现一次。

技术分享图片
mysql> create table test_1(id int(2),name char(10) unique);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_1 values(1,kebi);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_1 values(2,kebi);
ERROR 1062 (23000): Duplicate entry kebi for key name
唯一

 

4.自增(auto_increment)

建立自增属性有两点要求:

1)字段上要有唯一性索引或者主键

技术分享图片
mysql> create table zizeng1(id int(4) auto_increment,name char(10));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
实例

2)该字段必须是数值型

技术分享图片
mysql> create table zizeng1(id char(4) primary key auto_increment,name char(10));
ERROR 1063 (42000): Incorrect column specifier for column id
实例

 自增的表现形式:

在不指定具体的值的时候,设置了自增的字段添加的值会在上一条记录上加一。

技术分享图片
mysql> select * from auto;
+----+------+
| id | name |
+----+------+
|  3 | kebi |
+----+------+
1 row in set (0.00 sec)

mysql> insert into auto(name) values(maoxian);
Query OK, 1 row affected (0.01 sec)

mysql> select * from auto;
+----+---------+
| id | name    |
+----+---------+
|  3 | kebi    |
|  4 | maoxian |
+----+---------+
2 rows in set (0.00 sec)
自动加一

如果添加的值比之前的字段值小,会自动往前面添加。

技术分享图片
mysql> insert into auto values(1,xiaoniao);
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto;
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaoniao |
|  3 | kebi     |
|  4 | maoxian  |
+----+----------+
3 rows in set (0.00 sec)
小值自动往前放

delete删除值,之后自增的值还是会在之前最大的值的基础上增加。

技术分享图片
mysql> insert into auto values(1000,huangyu);
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoniao |
|    3 | kebi     |
|    4 | maoxian  |
| 1000 | huangyu  |
+------+----------+
4 rows in set (0.00 sec)

mysql> delete from auto;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from auto;
Empty set (0.00 sec)    #空的

mysql> insert into auto(name) values(maoxian);
Query OK, 1 row affected (0.01 sec)

mysql> select * from auto;
+------+---------+
| id   | name    |
+------+---------+
| 1001 | maoxian |    #从1001开始
+------+---------+
1 row in set (0.00 sec)
delete对于自增的效果

trancate会清空表,而且也会清空自增的效果。

技术分享图片
mysql> select * from auto;
+------+---------+
| id   | name    |
+------+---------+
| 1001 | maoxian |
| 1002 | zidong  |
| 1003 | zihao   |
+------+---------+
3 rows in set (0.00 sec)

mysql> truncate table auto;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from auto;
Empty set (0.00 sec)

mysql> insert into auto(name) values(zihao);
Query OK, 1 row affected (0.01 sec)

mysql> select * from auto;
+----+-------+
| id | name  |
+----+-------+
|  1 | zihao |
+----+-------+
1 row in set (0.00 sec)
trancate对于自增的效果

 

5.主键(primary key)

主键是表中的特殊字段,这个字段能够唯一标识表中的每一条记录。

一张表最多只能有一个主键,主键需要满足的条件:非空且唯一。

主键常常与外键构成参照完整性的约束,防止出现数据不一致。

主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引,所以主键也是一个特殊的索引。

技术分享图片
mysql> create table auto(id int(4) primary key auto_increment,name varchar(10));
单个字段做主键

还有一点需要提出来的是“联合主键”,多个字段的值只要不都不变,就不会报错。

技术分享图片
mysql> create table test(id int(4),name char(10),primary key(id,name));
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  0 |       |
|  0 | kebi  |
|  0 | zihao |
+----+-------+
联合主键字段值相同合理 技术分享图片
mysql> create table test2(id int(4) primary key,name char(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test2(name) values(maoxian);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into test2(name) values(huangyu);
ERROR 1062 (23000): Duplicate entry 0 for key PRIMARY  #不能满足唯一性
单一主键就不行

 

6.外键 (foreign key)

外键是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简而言之,表的外键就是另一张表的主键,外键将两表联系起来。

创建外键的条件:

(1)存储引擎是innodb,存储引擎不是innodb创建是不会报错,但是数据之间失去了关联。

技术分享图片
mysql> create table mem(id int,name varchar(20),foreign key(id) references class(id)) engine=myisam;
Query OK, 0 rows affected (0.09 sec)
mysql> select * from class;
+----+-------+
| id | name  |
+----+-------+
|  2 | zihao |
+----+-------+
1 row in set (0.00 sec)
mysql> insert into mem values(3,kebi);
Query OK, 1 row affected (0.00 sec)
不会报错,但数据之间没有关联

(2)相关联字段数据类型要一致;

技术分享图片
mysql> create table mem(id int(20),name varchar(20),foreign key(id) references class(id));
Query OK, 0 rows affected (0.10 sec)

mysql> select * from class;
+----+-------+
| id | name  |
+----+-------+
|  2 | zihao |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into class values(3,zihao);
Query OK, 1 row affected (0.00 sec)
字段类型不同,数据不关联

(3)最好在外键列上建索引(目的就是为了减少扫面范围,不创建也可以,只是影响性能)。

 

外键的表现形式:

(1)外键列的值依赖主键列的值,如果主键列中没有,那么外键也不能添加

(2)不能删除主键列中被外键列依赖的值。

技术分享图片
mysql> create table class(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.08 sec)

mysql> create table mem(id int,name varchar(20),foreign key(id) references class(id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc class
    -> ;
+-------+-------------+------+-----+---------+-------+
| 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 mem;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mem中id字段依赖class中的id字段 技术分享图片
mysql> insert into class values(1,kebi),(2,zihao);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into mem values(1,huangyu);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mem values(5,daipeng);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`yueshu`.`mem`, CONSTRAINT `mem_ibfk_1` FOREIGN KEY (`id`) REFERENCES `class` (`id`))
class中没有的值mem中也不能有 技术分享图片
mysql> select * from class;
+----+-------+
| id | name  |
+----+-------+
|  1 | kebi  |
|  2 | zihao |
+----+-------+
2 rows in set (0.00 sec)

mysql> select * from mem;
+------+---------+
| id   | name    |
+------+---------+
|    1 | huangyu |
|    2 | daipeng |
+------+---------+
2 rows in set (0.00 sec)

mysql> delete from class where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`yueshu`.`mem`, CONSTRAINT `mem_ibfk_1` FOREIGN KEY (`id`) REFERENCES `class` (`id`))
mysql> delete from mem where id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> delete from class where id = 1;
Query OK, 1 row affected (0.00 sec)
不要删除外键依赖的主键列值

到这里总是觉得外键没有啥用,反而碍手碍脚。怎样让父表不受子表的约束了?

有两种方法:

(1)删除外键约束

(2)指定级联操作的选项。

on delete cascade:级联删除,父表删除记录,子表随之删除相应的记录。

on update cascade级联更新,父表更改数据,子表也会随之更新,但是父表添加,主表没有反应。

技术分享图片
#创建外连接,同时指定级联删除和级联更新
mysql> create table mem(id int,name char(10),index(id),foreign key(id) references class(id) on delete cascade on update cascade);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from class;
+----+----------+
| id | name     |
+----+----------+
|  2 | zihao    |
|  3 | xiaoniao |
+----+----------+
2 rows in set (0.00 sec)
#级联更新
mysql> update class set id = 30 where name = "xiaoniao";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mem;
+------+----------+
| id   | name     |
+------+----------+
|    2 | xiaocui  |
|   30 | meiliang |

                        
                    

人气教程排行