（四）约束及修改数据表

2017-06-15 本文已影响85人黒猫

1、约束概述

约束的目的是为了保证数据的完整性与一致性。

按照约束的范围划分：
列级约束：只对一个数据列建立约束。既可以在列定义时声明，也可以在列定义后声明；
表级约束：对多个数据列建立约束。只能在列定义后声明；

按照约束的功能划分：
非空约束——NOT NULL
主键约束——PRIMARY KEY
唯一约束——UNIQUE KEY
默认约束——DEFAULT
外键约束——FOREIGN KEY

在实际开发中，表级约束很少用到，更多的会使用列级约束；除此之外，在所有的约束中，并不一定每种功能的约束都存在着表级或列级之分。其中，非空约束和默认约束就不存在表级约束，它们只有列级约束；而对于主键约束、唯一约束、外键约束都可以作为表级约束或列级约束。

2、外键约束的要求解析

前四种约束的用法及效果可参见操作数据表，现在来看一下外键约束：外键约束即“FOREIGN KEYp”，可以保证数据的完整性与一致性，实现数据表一对一或一对多的关系，这也是许多数据库被称为“关系型数据库”的根本原因。

创建外键约束需要满足如下要求：

父表(包含参照列)和子表(包含外键列)必须使用相同的存储引擎，且存储引擎只能为InnoDB，而且禁止使用临时表；
外键列和参照列必须具有相似的数据类型，其中：数字的长度或是否有符号位必须相同，而字符的则长度可以不同；
外键列和参照列必须创建索引，如果外键列不存在索引，MySQL将自动创建索引。

创建父表“provinces”，并检查其存储引擎：

创建子表“users”，此表的目的是记录用户的省份信息，在使用外键约束后，只需要父表的“id”就可以获取相应的省份，但是根据要求对于数字的长度必须相同，因此在使用数据类型“BIGINT”后，系统提示错误：

　　此时已将数据类型修改为相同的“SMALLINT”，但系统仍然提示错误，原因是父表中注明“UNSIGNED”，即无符号位，而子表中并没有注明，因此出错：

　　最终完成子表的创建：

输入“SHOW INDEXES FROM tbl_name”可查看该表的索引情况：

　　由于“id”字段使用了主键，而主键会自动创建索引，因此参照列已经具有了索引，再来看外键列：

　　可见具有两个索引，一个是“id”字段，另一个则是外键列“p_id”字段，查看该数据表的结构可以发现：

　　系统已自动为其创建了索引。

注意：
父表——子表所参照的表“provinces”
子表——含有外键“FOREIGN KEY”的表“users”
参照列——父表中“id”字段列
外键列——子表中“p_id”字段列

3、外键约束的参照操作

外键约束的参照操作，简单理解就是在创建外键约束之后，更新父表时，子表是否进行同样的操作，共有如下4种：

CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行；

SET NULL：从父表删除或更新行，并设置子表中的外键列为“NULL”。如果使用该选项，必须保证子表列没有指定“NOT NLUU”；
RESTRICT：拒绝对父表的删除或更新操作；
NO ACTION：标准SQL的关键字，在MySQL中与“RESTRICT”作用相同。

以“CASCADE”为例进行演示，创建子表“users1”：

在创建外键时添加“ON DELETE CASCADE”

　　之后，需要在两张数据表中添加记录，要注意，必须先在父表中添加记录，子表才能有所参照：

在父表中添加三个省份信息，之后向子表中添加用户信息，只需要写入用户名及父表中省份的“id”即可：

　　之所以在添加“John”用户时出错，是因为父表中根本不存在“id”为9的省份，因而报错。查看该数据表的结构可发现：

“id”排列没有序号3是因为，虽然之前输入记录时“‘John’，9”报错没有计入，但是编号已经自动递增一位，因此最终的用户“id”为1、2、4、5。此时删除父表中“id”为3的省份，看子表是否也会发生改变：

可见父表中“id”为3的省份已被删除，而子表中“p_id”为3的用户“Rose”也一同被删除了。

注意：
　　关于外键约束，在实际的开发过程中，其实很少使用物理性的外键约束，更多的会去使用逻辑性的外键约束，因为物理性的外键约束只有InnoDB这种引擎才会支持，然而另一种MyISAM引擎则不支持。换句话说，如果创建一张数据表，其存储引擎为MyISAM，但又想使用这种物理性的外键约束的话，其实是不可能实现的，因此，在实际的项目开发中，不会去定义物理性的外键。所谓的逻辑性外键指的就是在创建数据表之前，已经定义好多张数据表的结构，而不去使用“FOREIGN KEY”这个关键词。

4、修改数据表——添加／删除列

添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name columan_definition [FIRST | AFTER col_name]
** “FIRST col_name”指所插入的列位于指定列之前；“AFTER col_name”指所插入的列位于指定列之后；如果都省略，则默认在所有列之后插入该列。**

首先查看“users1”的数据结构，之后插入数据列“age”，且不指定其位置，再次查看该表结构可发现，新添加的列“age”确实位于所有列之后，排在末尾：

添加一列“password”，指定其位置在“username”列之后：

再添加一列“truename”，指定其位置在所有列之前：

添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name columan_definition , ……)
添加多列时，列名与定义信息要放在小括号内，且不能指定位置，只能放在已有列的末尾

一次性向数据表“users1”中添加多列：

删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name , ……
可以删除一列，可以删除多列，还可以在删除的同时新增列

删除“username”列：

删除“age”列同时，新增“username”列：

一次性删除多列：

5、修改数据表——添加／删除约束

添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,……)
其中“CONSTRAINT”关键字可以自定义主键名，而“index_type”索引类型后面会讲到。

创建新的数据表“users2”，且不设置主键：

新增一列“id”，且为其添加主键，主键名为“PK_users2_id”：

添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,……)

为“username”添加唯一约束：

添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,……) reference_definition

为“p_id”添加外键约束：

添加默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DEFAULT literal

为新建字段“age”添加默认约束：

删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY

删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
**由于一张数据表可以有多个唯一约束，因此删除时需要添加索引名 **

首先查看“username”字段上唯一约束的索引名：

“Key_name”即索引名

删除的“username”是索引名即唯一约束，而不是字段：

删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
**注意删除时需要添加外键约束名 **

首先查看“p_id”字段上的外键约束名：

系统自动添加的外键约束名“users2_ibfk_1”

删除“p_id”字段上的外键约束：

　　可见已经没有“FOREIGN KEY”等信息：

删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name DROP DEFAULT

删除字段“age”的默认约束：

6、修改数据表——修改列定义／更名操作

修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST|AFTER col_name]

修改字段“id”的数据类型为“TINYINT”并将其挪至数据表起始位置：

注意：
　　由范围较大的数据类型（例如“SMALLINT”）修改成范围较小的数据类型（例如“TINYINT”），可能会造成数据的丢失。

修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name col_definition [FIRST|AFTER col_name]
使用“CHANGE”关键字不仅包含“MODIFY”关键字的功能，还可以修改字段名称。

修改字段“p_id”的名称为“pid”，数据类型为“TINYINT”并将其挪至“age”字段的后面：

修改数据表名称
方法一：
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
方法二：
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name TO new_tbl_name]……

使用方法一将数据表“users2”名称修改为“users3”：

使用方法二将数据表“users3”名称修改为“users2”：

注意：
　　实际上应尽量少的使用数据列及数据表的更名操作，因为当创建了索引或使用过视图、存储过程之后，在表名或列名被引用的情况下，修改其名称可能会导致视图或存储过程等无法正常工作。

7、修改数据表的SQL语句汇总：

添加／删除列
添加单列：ALTER TABLE tbl_name ADD [COLUMN] col_name columan_definition [FIRST | AFTER col_name];
添加多列：ALTER TABLE tbl_name ADD [COLUMN] (col_name columan_definition , ……);
删除列：ALTER TABLE tbl_name DROP [COLUMN] col_name , ……;
添加／删除约束
添加主键约束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,……);
添加唯一约束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,……);
添加外键约束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,……) reference_definition;
添加默认约束：ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DEFAULT literal;
删除主键约束：ALTER TABLE tbl_name DROP PRIMARY KEY;
删除唯一约束：ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
删除外键约束：ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
删除默认约束：ALTER TABLE tbl_name ALTER [COLUMN] col_name DROP DEFAULT;
修改列定义／更名操作
修改列定义：ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST|AFTER col_name];
修改列名称：ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name col_definition [FIRST|AFTER col_name];
修改数据表名称
方法一：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
方法二：RENAME TABLE tbl_name TO new_tbl_name [, tbl_name TO new_tbl_name]……;

版权声明：欢迎转载，欢迎扩散，但转载时请标明作者以及原文出处，谢谢合作！             ↓↓↓