MySql数据库约束

关系型数据库系统和文件系统的一个不同点是,关系数据库本身能保证存储数据的完整性,不需要应用程序的控制,而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束(constraits)

大家好,又见面了,我是全栈君,今天给大家准备了Idea注册码。

 MySql数据库约束

  《MySQL技术内幕:InnoDB存储引擎》本书从源代码的角度深度解析了InnoDB的体系结构、实现原理、工作机制,并给出了大量实践,本着将书读薄的思想,循序渐进的记录对本书学习的读书笔记。

  关系型数据库系统和文件系统的一个不同点是,关系数据库本身能保证存储数据的完整性,不需要应用程序的控制,而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束(constraits)机制,该机制提供了一条强大而简易的途径来保证数据库中的数据完整性,一般来说,数据完整性有以下三种形式:

(1)实体完整性保证表中有一个主键,在InnoDB存储引擎中,用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性,用户还可以编写一个触发器来保证数据完整性、

(2)域完整性保证数据每列的值满足特定的条件。在InnoDB存储引擎中,域完整性可以通过以下途径来保证:

  a. 选择适合的数据类型确保一个数据值满足条件

  b. 外键(Foreign Key)约束

  c. 编写触发器

  d. 还可以考虑用default约束作为强制域完整性的一个方面

(3)参照完整性保证两张表之间的关系,InnoDB存储引擎提供了以下几种约束:

  a. primary key

  b. unique key

  c. foreign key

  d. default

  e. not null

1. 约束的创建和查找

  约束的创建有以下两种方式:

  (1)表建立时就进行约束定义

  (2)利用alter table命令来进行创建约束

  以下几点需要关注和注意:

  a. 对Unique Key(唯一索引)的约束,用户除了在创建时约定,还可以通过Create Unique Index来创建

  b. 对于主键约束耳音,其默认约束名为PRIMARY,而对于Unique Key约束而言,默认约束名和列名一样,当然也可以人为的指定Unique Key的名字,Foreign Key约束似乎会有一个比较神秘的默认名称

1.1 例1:创建表u,设置一个primary key和unique key

mysql> Create table u(
    -> id int,
    -> id_card varchar(18),
    -> name varchar(20),
    -> primary key(id),
    -> unique key(name)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.56 sec)

mysql> select constraint_name,constraint_type
    -> from
    -> information_schema.table_constraints
    -> where table_schema='test' and table_name='u';
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| PRIMARY         | PRIMARY KEY     |
| name            | UNIQUE          |
+-----------------+-----------------+
2 rows in set (0.01 sec)

  可以看到,约束名如上面所说,主键的约束名为PRIMARY,唯一索引的默认约束名与列名相同。

1.2 例2:alter table创建约束

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

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

1.3 例3:Foreign key的约束

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

mysql> select constraint_name,constraint_type
    -> from
    -> information_schema.table_constraints
    -> where table_schema='test' and table_name='p';
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| PRIMARY         | PRIMARY KEY     |
| p_ibfk_1        | FOREIGN KEY     |
+-----------------+-----------------+
2 rows in set (0.00 sec)

  在上面的例子中,通过information_schema架构下的表table_constraints来查看当前MySql库下所有的约束信息。

2. 对错误数据的约束

  在某些默认设置下,MySql数据库允许非法或不正确的数据的插入或更新,又或者可以在数据库内部将其转化为一个合法的值,如向not null的字段插入一个null值,MySql数据库会将其更改为0再进行插入,因此数据库本身没有对数据的正确性进行约束。

2.1 例1

mysql> Create table a(
    -> id int not null,
    -> data date not null
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into a select NULL, '2009-02-20';
Query OK, 1 row affected, 1 warning (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 1
mysql> select * from a;
+----+------------+
| id | data       |
+----+------------+
|  0 | 2009-02-20 |
+----+------------+
1 row in set (0.00 sec)

  通过设置参数set sql_mode=’strict_trans_tables’;对MySql数据库的输入值进行了约束,而且针对不同的错误提示错误内容也不同。

2.1 例2:enum与set约束

mysql> Create table a(
    -> id int,
    -> sex enum('male','female')
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.17 sec)

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

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

3. 触发器与约束

3.1 触发器的认识

  触发器的作用是在执行insert,delete和update命令之前或之后自动调用sql命令或存储过程

3.1.1  触发器的创建

Create
[definer = { user | current_user}]
trigge trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE
on tble_name FOR EACH ROW trigger_stmt

(1)最多可以为一个表建立6个触发器,即分别为insert,update,delete的before和after各定义一个

(2)只有表才支持触发器,视图不支持(临时表也不支持)

(3)如果before触发器失败,则MySQl将不执行请求的操作,此外如果before触发器或语句本身失败,MySql将不执行after触发器(如果有的话)

3.1.2  触发器的删除

  DROP TRIGGER trigger_name;

注:触发器不能更新或覆盖,为了修改一个触发器,必须先删除它,然后再重新创建

3.2 触发器约束

  假设有张用户消费表,每次用户购买一样物品后其金额都是减的,若这时有不坏好意的用户做了一个类似减去一个负值的操作,这样用户的钱没有减少反而不断增加

mysql> Create table usercash(
    -> userid int not null,
    -> cash int unsigned not null
    -> ,
    -> Primary key(userid))
    -> engine=InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into usercash select 1,1000;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update usercash set cash=cash-(-20) where userid = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from usercash;
+--------+------+
| userid | cash |
+--------+------+
|      1 | 1020 |
+--------+------+
1 row in set (0.00 sec)

  上面运行的SQL语句对数据库来说没有任何的问题,都可以正常运行,不会报错。但从业务逻辑上来说,这是绝对错误的。下面采用触发器来约束这个逻辑行为:

mysql> Create table usercash_error_log(
    -> userid int not null,
    -> old_cash int unsigned not null,
    -> new_cash int unsigned not null,
    -> user varchar(30),
    -> time datetime,
    -> primary key(userid)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.23 sec)

mysql> delimiter //
mysql> Create trigger tgr_usercash_update before update on usercash
    -> for each row
    -> begin
    -> if new.cash -old.cash>0 then
    -> insert into usercash_error_log select old.userid, old.cash,new.cash,US
),NOW();
    -> set new.cash=old.cash;
    -> end if;
    -> end//
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> delete from usercash;
Query OK, 1 row affected (0.05 sec)

mysql> insert into usercash select 1,1000;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update usercash set cash=cash-(-20) where userid=1;
Query OK, 0 rows affected (0.11 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from usercash;
+--------+------+
| userid | cash |
+--------+------+
|      1 | 1000 |
+--------+------+
1 row in set (0.00 sec)

mysql> select * from usercash_error_log;
+--------+----------+----------+----------------+---------------------+
| userid | old_cash | new_cash | user           | time                |
+--------+----------+----------+----------------+---------------------+
|      1 |     1000 |     1020 | root@localhost | 2018-06-01 15:09:51 |
+--------+----------+----------+----------------+---------------------+
1 row in set (0.00 sec)

  可以看出这次对于异常的数据更新通过触发器将其保存到了usercash_error_log。此外该触发器还记录了操作该SQL语句的用户和时间。 

4. 外键约束

外键用来保证参照完整性,MySQL数据库的MyIsAM存储引擎本身并不支持外键,对于外键的定义只是起到一个注释的作用,而InonoDB存储引擎则完整支持外键约束。

一般来说,称被引用的表为父表,引用的表称为子表,外键定义时的on delete和on update表示在对父表进行delete和updata操作时,对子表所做的操作。可定义的子表操作有:

(1)CASCADE

   表示当父表发生delete和update操作时,对相应的子表中的数据页进行delete和update操作

(2)SET FULL

  表示当父表发生delete和update操作时,相应的子表中的数据被更新为NULL值,但是子表中对应的列必须允许为NULL值

(3)NO ACTION

  表示父表发生delete或update操作时,抛出错误,不允许这类操作发生

(4)RESTRICT

  表示父表发生delete或update操作时,抛出错误,不允许这类操作发生,如果定义外键时没有指定on delete或on update,RESTRICT就是默认的外键设置

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/120124.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号