有公司如何制作网站,建设电影网站论文,html5做登录网站的代码,用wordpress 安装自己喜欢的主题 主题图片显示不对约束
1 数据完整性 关系型数据库系统和文件系统的一个不同点是#xff0c;关系数据库本身能保证存储数据的完整性#xff0c;不需要应用程序的控制#xff0c;而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供约束(constraint)机制#xff0c;该机制…
约束
1 数据完整性 关系型数据库系统和文件系统的一个不同点是关系数据库本身能保证存储数据的完整性不需要应用程序的控制而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供约束(constraint)机制该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。一般来说数据完整性有以下三种形式:
**1.实体完整性**保证表中有一个主键。在InnoDB存储引擎表中用户可以通过定义Primary Key和Unique Key约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。**2.域完整性**保证数据每列的值满足特定的条件。在InnoDB存储引擎表中域完整性可以通过以下几种途径保证。 选择合适的数据类型确保一个数据值满足特定条件外键(Foreign Key)约束编写触发器还可以考虑用default约束作为强制域完整性的一个方面 **3.参照完整性**保证两张表之间的关系。InnoDB存储引擎支持外键因此允许用户定义外键以强制参照完整性也可以通过编写触发器以强制执行。
对于InnoDB引擎本身提供了以下几种约束
Primary KeyUnique KeyForegin KeyDefaultNOT NULL
2 约束的创建和查找
约束的创建可以采用以下两种方式
表建立时就进行约束定义利用ALTER TABLE命令进行创建约束 对于Unique Key(唯一索引)的约束用户还可以通过命令create unique index来建立。
Primary Key
## 1.创建表id是Primary Key
mysql create table constraint_test(- id int,- primary key (id)- ) engineinnodb;
Query OK, 0 rows affected (0.05 sec)## 2.查询元数据信息可以发现
## Primary Key的约束名是Primary
mysql select constraint_name,constraint_type- from information_schema.table_constraints- where table_schemazxy and table_nameconstraint_test\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEYUnique Key 方式一alter table...add unique key... 添加 # 1.添加字段name
mysql alter table constraint_test add column name varchar(25);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 2.对name添加unique key并指定约束名为uk_name
mysql alter table constraint_test add unique key uk_name (name);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 3.查询元数据
mysql select constraint_name,constraint_type- from information_schema.table_constraints- where table_schemazxy and table_nameconstraint_test\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: uk_name
constraint_type: UNIQUE 方式二create unique index ...添加 mysql alter table constraint_test add column sex varchar(25) ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql create unique index un_sex on constraint_test(sex);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql select constraint_name,constraint_type- from information_schema.table_constraints- where table_schemazxy and table_nameconstraint_test\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: uk_name
constraint_type: UNIQUE
*************************** 3. row ***************************
constraint_name: un_sex
constraint_type: UNIQUE
3 rows in set (0.00 sec)Foregin Key
## 1.创建外键表constraint_foreign
# id为primary key
# test_id为constraint_test的外键
mysql create table constraint_foreign(- id int ,- test_id int ,- name varchar(25) ,- primary key (id),- foreign key (test_id) references constraint_test(id)- );# 2.查看元数据信息
mysql select constraint_name,constraint_type- from information_schema.table_constraints- where table_schemazxy and table_nameconstraint_foreign\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: constraint_foreign_ibfk_1
constraint_type: FOREIGN KEY
2 rows in set (0.00 sec)# 3.用户还可以通过information_constraints查看表的外键属性
mysql select *- from information_schema.referential_constraints- where constraint_schemazxy and table_name constraint_foreign\G;
*************************** 1. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: zxyCONSTRAINT_NAME: constraint_foreign_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: defUNIQUE_CONSTRAINT_SCHEMA: zxyUNIQUE_CONSTRAINT_NAME: PRIMARYMATCH_OPTION: NONEUPDATE_RULE: RESTRICTDELETE_RULE: RESTRICTTABLE_NAME: constraint_foreignREFERENCED_TABLE_NAME: constraint_test
1 row in set (0.00 sec)3 约束和索引的区别 我们可以看到在使用Primary Key和Unique Key的时候就是正常创建索引的方法。那么约束和索引有什么区别呢 用户创建一个唯一索引就是创建了一个唯一的约束。但是约束和索引的概念有所不同约束更像是一个逻辑的概念用户保证数据的完整性而索引是一个数据结构既有逻辑上的概念在数据库中还代表着物理存储的方式。
4 对错误数据的约束 在某些默认设置下MySQL数据库允许非法的或不正确的数据的插入或更新又或者可以在数据库内部将其转化为一个合法的值。比如向NOT NULL的字段插入一个NULL值MySQL数据库会将其更改为0再进行插入因此数据库本身没有对数据的正确性进行约束。 但是在插入非法的数据或不正确的数据的时候会根据数据库的sql_mode来判断是报错还是警告。
# 1.查看sql_mode类型
mysql select sql_mode\G;
*************************** 1. row ***************************
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)# 2.查看当前会话的sql_mode类型
mysql select session.sql_mode\G;
*************************** 1. row ***************************
session.sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)# 3.查看全局的sql_mode类型
mysql select global.sql_mode\G;
*************************** 1. row ***************************
global.sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)此时设置有sql_mode因此插入非法或不正确的数据会直接报错
mysql create table constraint_null (- id int not null,- date date not null- );
Query OK, 0 rows affected (0.04 sec)mysql insert into constraint_null select null,2023-02-30;
ERROR 1048 (23000): Column id cannot be null 设置sql_mode为空此时向NOT NULL的列插入了一个NULL值同时向date列插入一个不合法的日期。这时候就没有报错信息只是显示了警告(warning)。通过指令show warnings可以查看警告的信息。
mysql set session sql_mode ;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql insert into constraint_null select null,2023-02-30;
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 2mysql show warnings\G;
*************************** 1. row ***************************Level: WarningCode: 1048
Message: Column id cannot be null
*************************** 2. row ***************************Level: WarningCode: 1264
Message: Out of range value for column date at row 1
2 rows in set (0.00 sec)mysql select * from constraint_null;
----------------
| id | date |
----------------
| 0 | 0000-00-00 |
----------------
1 row in set (0.00 sec)5 ENUM和SET约束 MySQL数据库不支持传统的CHECK约束但是通过ENUM和SET类型可以解决部分这样的约束需求。例如表上有一个性别类型规定域的范围只能是male或female在这种情况下用户可以通过ENUM类型来进行约束。
mysql create table enum_test (- id int ,- sex enum(male,female)- );
Query OK, 0 rows affected (0.02 sec)mysql insert into enum_test values(1,male),(2,female);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql insert into enum_test values(1,test);
ERROR 1265 (01000): Data truncated for column sex at row 1如上所示对非法的输入值进行了约束但是只限于对离散数值的约束对于传统check约束支持的连续值的范围或更复杂的约束ENUM和SET类型还是无能为力这时用户需要通过触发器来实现对于值域的约束。
6 触发器与约束 触发器的作用是在执行INSERT、UPDATE、DELETE命令前后自动调用SQL命令或存储过程。 触发器的创建命令是CREATE TRIGGER只有具备Super权限的MySQL数据库用户才可以执行这条命令。
CREATE
[DEFINER {user | current_user}]
TRIGGER trigger_name DEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name FOR EACH ROW 最多可以为一个表建立6个触发器即分别是INSERT、UPDATE、DELETE的BEFORE和AFTER各定义一个。BEFORE和AFTER代表触发器发生的时间表示在每行操作的之前还是之后发生。MySQL只支持行级触发器for each row,不支持语句级触发器for each statement。 通过触发器用户可以实现MySQL数据库本身并不支持的一些特性比如对于传统check约束的支持物化视图、高级复制、审计等特性。这里先关注触发器对于约束的支持。
案例 加入有张用户消费表每次用户购买一样物品后其金额都是减的若这时残生了负值的操作这样用户的金额不减反增
mysql create table usercash(- userid int not null,- cash int unsigned not null- );
Query OK, 0 rows affected (0.02 sec)mysql insert into usercash values(1,1000);
Query OK, 1 row affected (0.01 sec)mysql update usercash- set cashcash-(-20)- where userid1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from usercash;
--------------
| userid | cash |
--------------
| 1 | 1020 |
--------------
1 row in set (0.00 sec)如上所示SQL语句对于数据库来说是没有任何问题的可以正常的运行不会报错。但是从业务逻辑上讲这是绝对错误的。所以可以通过触发器来约束这个行为
# 1.创建存储错误业务数据表
mysql create table usercash_err_log (- userid int not null,- old_cash int unsigned not null,- new_cash int unsigned not null,- user varchar(30),- time datetime- );
Query OK, 0 rows affected (0.02 sec)# 2.创建触发器
mysql delimiter
mysql create trigger trigger_user_cash_updatebefore before update- on usercash- for each row- begin- if new.cash - old.cash 0 then- insert into usercash_err_log- select old.userid,old.cash,new.cash,user(),now();- set new.cash old.cash;- end if;- end;-
Query OK, 0 rows affected (0.01 sec)
mysql delimiter ;# 3.业务数据负数情况
mysql update usercash- set cash cash - (-20)- where userid 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0# 4.查看错误业务数据是否影响业务
mysql select * from usercash;
--------------
| userid | cash |
--------------
| 1 | 1020 |
--------------
1 row in set (0.00 sec)# 5.查看错误日志表
mysql select * from usercash_err_log;
-----------------------------------------------------------------
| userid | old_cash | new_cash | user | time |
-----------------------------------------------------------------
| 1 | 1020 | 1040 | rootlocalhost | 2023-03-09 16:44:36 |
-----------------------------------------------------------------
1 row in set (0.00 sec)可以看到对于异常的数据更新通过触发器将其保存到usercash_err_log。此外该触发器还记录了操作该SQL语句的用户及时间。通过上述例子可以发现创建触发器也是实现约束的一种手段和方法。
7 外键约束 外键用来保证参照完整性MySQL数据库的MyISAM存储引擎本身并不支持外键对于外键的定义只是起到一个注释的作用。而InnoDB存储引擎则完整支持外键约束。
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...)
REFERENCES tb1_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
referece_optio:
RESTRICT | CASCADE |SET NULL|NO ACTION用户可以在执行CREATE TABLE时就添加外键也可以在表创建后ALTER TABLE命令来添加。一个简单的外键的创建示例如下
mysql create table parent(- id int not null,- primary key (id)- ) engineinnodb;
Query OK, 0 rows affected (0.02 sec)mysql create table child(- id int ,- parent_id int,- foreign key (parent_id) references parent(id)- ) engineinnodb;
Query OK, 0 rows affected (0.02 sec)一般来说称被引用的表为父表引用的表为子表。外键定义时的ON DELETE和ON UPDATE表示在对父表进行DELETE和UPDATE操作时对子表所做的操作可定义的子表操作有 CASCADE CASCADE表示当父表发生DELETE或UPDATE操作时对相应的子表中数据进行DELETE或UPDATE操作。 SET NULL SET NULL表示当父表发生DELETE或UPDATE操作时相应的子表中的数据被更新为NULL值。 NO ACTION NO ACTION表示当父表发生DELETE或UPDATE操作时抛出错误不允许这类操作发生 RESTRICT RESTRICT表示当父表发生DELETE或UPDATE操作时抛出错误不允许这类操作发生。 如果定义外键没有指定ON DELETE或ON UPDATERESTRICT就是默认的外键设置。 在其他数据库中如Oracle数据库有一种称为延时检查(deferred check)的外键约束即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查(immediate check)因此从上面的定义可以看出在MySQL数据库中NO ACTION和RESTRICT功能都是相同的。 在Oracle数据库中对于建立外键的列一定不要忘记给这个列加上一个索引。而InnoDB存储引擎在外键建立时会自动对该列加一个索引这和SQL Server数据库做法一样。因此可以很好的避免外键列上无索引而导致死锁的问题。例如上述的例子表child创建时只定义了外键并没有手动指定parent_id列为索引但是通过命令show create table可以发现InnoDB引擎自动为外键约束的列parent_id添加了索引
mysql show create table child\G;
*************************** 1. row ***************************Table: child
Create Table: CREATE TABLE child (id int(11) DEFAULT NULL,parent_id int(11) DEFAULT NULL,KEY parent_id (parent_id),CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id)
) ENGINEInnoDB DEFAULT CHARSETutf8
1 row in set (0.00 sec)对于参照完整性约束外键能起到一个非常好的作用。但是对数据的导入操作时外键往往导致在外键约束的检查上花费大量时间。因为MySQL数据库的外键是即时检查的所以对导入的每一行都会进行外键检查。但是在导入过程中忽视外键的检查如 mysql set foreign_key_checks 0;
Query OK, 0 rows affected (0.00 sec)
...
加载数据
...
mysql set foreign_key_checks 1;
Query OK, 0 rows affected (0.00 sec)
